13.9 Tuning to shorten SQL statement execution time by re-examining the table-definition pool size

This section describes how to shorten SQL statement execution time by re-examining the table-definition pool size specified for the following operand:

• adb_sql_tbldef_cache_size operand in the server definition

You might be able to shorten SQL statement execution time by increasing the table-definition pool size. The following shows the procedure for changing the size of the table-definition pool after checking whether the size is appropriate.

Procedure

1. Execute the adbstat command to obtain the statistical information of the HADB server.

   Information about the table-definition pool is output as the statistical information of the HADB server. Check the following items:

   • Tbldef_req_cnt (number of times acquisition of table-definition information was requested)

     The number of times acquisition of table-definition information was requested is output.

   • Tbldef_access_cnt (number of times table-definition information was obtained from the dictionary table)

     Because the relevant table-definition information cannot be obtained from the table-definition pool, the number of times table-definition information was obtained from the dictionary table is output.

   • Tbldef_cache_access_cnt (number of times table-definition information was obtained from the table-definition pool)

     The number of times the relevant table-definition information was obtained from the table-definition pool is output.

   • Tbldef_cache_register_cnt (number of times table-definition information was registered in the table-definition pool)

     The number of times table-definition information was registered in the table-definition pool is output.

   • Tbldef_cache_sweep_cnt (number of times table-definition information was swept out of the table-definition pool)

     The number of times table-definition information was swept out of the table-definition pool is output.

2. Check the hit rate of table-definition information.

   Use the following formula to obtain the hit rate of table-definition information. If the calculated hit rate is 80% or less, increase the value specified for the adb_sql_tbldef_cache_size operand in the server definition.

   Hit rate of table-definition information (%) = (A ÷ B) × 100

   A: Tbldef_cache_access_cnt (number of times table-definition information was obtained from the table-definition pool)

   B: Tbldef_req_cnt (number of times acquisition of table-definition information was requested)

3. Check the value of Tbldef_cache_sweep_cnt (number of times table-definition information was swept out of the table-definition pool).

   If the number of times table-definition information was swept out of the table-definition pool is large, increase the value specified for the adb_sql_tbldef_cache_size operand in the server definition.

4. Terminate the HADB server.

   Terminate the HADB server by executing the adbstop command.

5. Change the server definition.

   Increase the value specified for the adb_sql_tbldef_cache_size operand in the server definition.

6. Start the HADB server.

   Execute the adbstart command to start the HADB server.

Note

For details about the table-definition pool and the adb_sql_tbldef_cache_size operand in the server definition, see the description of the adb_sql_tbldef_cache_size operand in 7.2.2 Operands related to performance (set format).

To Page Top