13.4.3 Reducing memory usage by re-evaluating buffers for local work tables

This subsection explains how to re-evaluate the number of pages in the buffers for local work tables by checking the access path statistical information that is output as SQL trace information.

You might be able to reduce memory usage by re-evaluating the number of pages in the buffers for local work tables.

Procedure

Check the access path statistical information.

Check the access path statistical information that is output as SQL trace information. In the data access information that is output as access path statistical information, check the following output items:
- Data_dbbuff_wrktbl_clt_write_cnt (number of write operations to files from buffers for local work tables)
- Data_wrktbl_page_use_max (number of pages in the largest work table that was allocated)
After you have checked the output items, go to step 2.
Check the values that have been specified for operands.

Check the values that have specified for the following operands:
- adb_sql_exe_max_rthd_num
- adb_dbbuff_wrktbl_clt_blk_num
After you have checked the operand values, go to step 3.
Determine whether the buffers for local work tables need to be re-evaluated.

Based on the checked values, determine whether the condition shown below is satisfied.

Condition (determining whether the buffers for local work tables need to be re-evaluated)

If the preceding formula is satisfied, the number of allocated pages of buffers for local work tables might be greater than the number of pages that are actually needed. If this case applies, go to step 4.
Change the client definition.

Change the value of the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition for the relevant connection to the value obtained from the following formula.

Formula (adb_dbbuff_wrktbl_clt_blk_num operand value)

By changing the value of the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition for the relevant connection to the value determined from the preceding formula, you can reduce the memory usage without affecting processing performance.

When you change the client definition, follow the procedure explained in Notes about changing a client definition in the HADB Application Development Guide.

Note: For details about the adb_dbbuff_wrktbl_clt_blk_num operand in the client definition, see Operands related to performance in the HADB Application Development Guide.

To Page Top