Item | pd_work_buff_mode=each is specified | pd_work_buff_mode=pool is specified |
---|
Advantage | A large work table buffer size reduces the number of I/O operations associated with data manipulation, which means that the execution time of SQLs that use work tables is also reduced. However, because each server's process private memory is used, specification of this option should take into account the overall size of the system memory (real memory and virtual memory). If pd_work_buff_mode = each is specified, the memory size to be allocated is value of pd_work_buff_size required number of work tables. Therefore, specifying an unnecessarily large value may cause a virtual memory shortage for other processes. |
Application criterion | pd_work_buff_mode = pool should be specified when a large volume of data is to be retrieved and when manipulations such as join, ORDER BY, and GROUP BY are to be performed. |
Specification guidelines | - Specify the size of the buffer to be allocated for one work table.
- If a value greater than the work table memory capacity is specified for the work table buffer size, input/output to the work table file during work table creation is eliminated, thus reducing the time necessary for work tables. The following formula can be used to determine the work table memory capacity:
Work table memory capacity = Applicable work table size* 2
| - Specify the size of the buffer pool to be allocated for the entire server process.
- Specify a value between 4352 and 5120 when a large volume of data is to be retrieved or when manipulations such as join, ORDER BY, and GROUP BY are to be performed. Specifying such a value increases the unit of sorting input/output, thus reducing the sort time.
- If a large value is specified for the work table buffer size, with the total work table memory capacity for each SQL statement as the upper limit, input/output operations on the work table file during work table creation is eliminated, thus reducing the time necessary for work tables. The following formula can be used to determine the total work table memory capacity for each SQL statement:
Total work table memory capacity per SQL statement = a b + c d
|
Notes | When multiple users execute processes concurrently or when an SQL statement that uses multiple work tables is executed, the specified size buffer is allocated for each work table. Consequently, specifying a large value may result in a memory shortage. | If the value specified for the work table buffer size is smaller than the number of work tables to be used by each SQL statement, the processing time may become longer than when each is specified. Specifically, specify a value that is at least equal to maximum number of work tables for each SQL statement 128. The following formula can be used to determine the maximum number of work tables for each SQL statement: Maximum number of work tables for each SQL statement = b + d |
Operand rule | Specify a multiple of 128. If the value is not a multiple of 128, it is rounded up to the next multiple of 128. | - Specify a multiple of 128. If the value is not a multiple of 128, it is rounded up to the next multiple of 128.
- Specify at least 384. If a value that is smaller than 384 is specified, it is rounded up to 384.
|