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 SQL statements that use work tables is also reduced. However, because each server's process private memory is used, you must also take into account the overall size of the system memory (real memory and virtual memory) when you specify this option. 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. A process private memory that has been allocated will remain allocated to that process at least in the virtual memory (will not be released until that process disappears) even after release is requested by HiRDB. Therefore, specifying an unnecessarily large value might cause a virtual memory shortage for other processes. |
Application criterion | Specify pd_work_buff_mode = pool 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, a buffer of the specified size is allocated for each work table. Consequently, specifying a large value might result in a memory shortage.
- If the specified buffer size is too large to be allocated in the system, the server can still start up, but the allocation of process private memory fails when work tables are created, resulting in an SQL error.
| - 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 might 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
- If the specified buffer size is too large to be allocated in the system, the allocation of process private memory fails and the server cannot start up.
|
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.
|
Default value | If this operand is omitted, the value specified for the same operand in the server common definition takes effect. If the same operand is also omitted from the server common definition, 128 is assumed. | If this operand is omitted, the value specified for the same operand in the server common definition takes effect. If the same operand is also omitted from the server common definition, the following value is assumed:
- 32-bit mode: 384
- 64-bit mode: 5120
|