13.5 Tuning to shorten SQL statement execution time by re-examining the hash table area size

This section describes how to shorten SQL statement execution time by re-examining the hash table area size specified for the following operands:

• adb_sql_exe_hashtbl_area_size operand in the server definition

• adb_sql_exe_hashtbl_area_size operand in the client definition

Note that you can also shorten SQL statement execution time by specifying an SQL statement in the adbexport command. If you use this method, replace the adb_sql_exe_hashtbl_area_size operand that appears in this section with the adb_export_hashtbl_area_size export option.

When executing an SQL statement for which the following processes (which use the hash table area) are applied, you can possibly shorten SQL statement execution time by increasing the hash table area size.

• Hash join as a table joining method

• Global hash grouping as a grouping method

• Hash execution as a method for processing subqueries

• Hash execution as a method for processing SELECT DISTINCT

• Hash execution as a method for processing the set operation

Procedure:

1. Check whether there was a process in which the hash table area size became insufficient.

   Check the access path statistical information output to the SQL trace information. In the information related to hash table areas that is output to the access path statistical information, check whether there is a process for which the value for the following output item is Y:

   • Hashtbl_area_shortage (whether the hash table area size became insufficient)

   If there is a process for which the value for the preceding item is Y, go to step 2.

2. Check whether the KFAA51130-W message was output.

   In step 1, if there was a process for which the value for the relevant item is Y, compare the following two contents:

   • Content of message_log_info output to the SQL statement execution information for the relevant processing

   • Content of the server message log file

   After comparing the preceding two contents, check whether the KFAA51130-W message was output to the server message log file. If the KFAA51130-W message was output, go to step 3.

3. Re-examine the value specified for the adb_sql_exe_hashtbl_area_size operand.

   In step 2, if the KFAA51130-W message was output, the data that could not be processed in the hash table area might have been stored in the work table multiple times and processed. Therefore, the SQL statement execution time might be longer.

   In this case, increase the value specified for the adb_sql_exe_hashtbl_area_size operand in the server definition and client definition. The execution time of an SQL statement to which a process that uses the hash table area is applied might be shortened.

   Important

   • Check the values specified for both the adb_sql_exe_hashtbl_area_size operand in the client definition and the adb_sql_exe_hashtbl_area_size operand in the server definition. If the value specified in the client definition exceeds the value specified in the server definition as a result of increasing the value in the client definition only, the value specified in the server definition is applied. The new value specified in the client definition is not applied. If this case applies, increase the values of the adb_sql_exe_hashtbl_area_size operands in both the server definition and client definition.

   • If you have changed the value specified for the adb_sql_exe_hashtbl_area_size operand, always re-estimate the HADB server's memory requirement. For details about how to re-estimate the memory requirements of the HADB server, see the explanation of the variable HASHTBL_PROC_SIZE in (c) Determining the variable RTHD_EXESQLSZ under (2) Determining the real thread private memory requirement (during normal operation) in 6.3.4 Determining the memory requirement during normal operation.

4. Change the server definition and client definition.

   Increase the value specified for the adb_sql_exe_hashtbl_area_size operand in the server definition and client definition.

   • When you change the server definition:

     Use the adbstop command to terminate the HADB server temporarily. After that, change the server definition, and then use the adbstart command to start the HADB server.

   • When you change the client definition:

     Change the client definition by following the procedure explained in Notes about changing a client definition in the HADB Application Development Guide.

5. Re-execute the SQL statement.

   Re-execute the SQL statement, and check whether the execution time of the SQL statement is shortened.

Note

• For details about the information related to hash table areas that is output as access path statistical information, see (e) Information related to hash table areas in (2) Items that are output as access path statistical information under 10.11.3 Examples of output of and output items for access path statistical information.

• For details about the adb_sql_exe_hashtbl_area_size operand in the server definition, see the description of the adb_sql_exe_hashtbl_area_size operand in 7.2.2 Operands related to performance (set format).

• For details about the adb_sql_exe_hashtbl_area_size operand in the client definition, see Operands related to performance in the HADB Application Development Guide.

To Page Top