Hitachi

Hitachi Advanced Database Setup and Operation Guide

13.8 Tuning to shorten SQL statement execution time by re-examining the hash filter area size

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

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_hashflt_area_size operand that appears in this section with the adb_export_hashflt_area_size export option.

You might be able to shorten SQL statement execution time by increasing the size of the hash filter area if the following processes to which hash filters are applied are executed:

Read the description in both of the following sections: ■ Checking whether hash filters are applied and ■ Checking whether hash filters are working effectively.

■ Checking whether hash filters are applied

Procedure

  1. Check whether there are processes to which no hash filter is applied.

    In the output results of SQL trace information, check whether there are processes that satisfy the following conditions:

    • In the client-definition information, the value of adb_sql_exe_hashflt_area_size is 1 or larger.

    • In the SQL statement statistical information, the value of Hashflt_disabled (whether hash filters are disabled) is Y.

    If a process satisfies all of the preceding conditions, no hash filter is applied to the process. Proceed to step 2. If there is no process that satisfies all of the preceding conditions, read the description in ■ Checking whether hash filters are working effectively.

  2. Check the total number of invalidated hash filters.

    If there are processes that satisfy the conditions shown in step 1, check the value of the following item in the output results of SQL trace information:

    • Hashtbl_filter_disabled_num in the information related to hash table areas that is output as access path statistical information (total number of invalidated hash filters)

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

    If the value of Hashtbl_filter_disabled_num that you checked in step 2 is 0, hash filters might not be applied because the hash filter area is too small. Therefore, the SQL statement execution time might be longer.

    In this case, increase the values specified for the adb_sql_exe_hashflt_area_size operands in the server definition and client definition. This might cause hash filters to be applied, resulting in faster execution of the SQL statement. For the guideline to determine the value to be specified for the adb_sql_exe_hashflt_area_size operand, see the following sections in the HADB Application Development Guide:

    • Conditions where a hash filter is applied in About hash join

    • Hash execution in Methods for processing subqueries that do not contain an external reference column

    • Hash execution in Methods for processing subqueries that contain an external reference column

    Important

    • Check the values specified for both the adb_sql_exe_hashflt_area_size operand in the client definition and the adb_sql_exe_hashflt_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_hashflt_area_size operands in both the server definition and client definition.

    • If you change the value specified for the adb_sql_exe_hashflt_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 HASHFLT_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.

    Even if the value of Hashtbl_filter_disabled_num that you checked in step 2 is 1 or larger, increasing the value of the adb_sql_exe_hashflt_area_size operand in the server definition or client definition might result in faster SQL statement execution.

  4. Change the server definition and client definition.

    Increase the values specified for the adb_sql_exe_hashflt_area_size operands 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. If hash filters are applied, SQL statement execution time might be shortened.

■ Checking whether hash filters are working effectively

Procedure

  1. Check whether hash filters are working effectively.

    Check the values of the following two items in the information related to hash table areas that is output as access path statistical information in the SQL trace information:

    • Hashtbl_sum_filter_check_cnt (total number of times a hash value was checked by using hash filters)

    • Hashtbl_sum_filtering_cnt (total number of times a hash value was excluded by hash filters)

    If the value of Hashtbl_sum_filtering_cnt is much smaller than the value of Hashtbl_sum_filter_check_cnt, the effectiveness of applying hash filters is diminished. Therefore, SQL statement execution time might be longer as a result of applying hash filters.

  2. Re-examine the value specified for the adb_sql_exe_hashflt_area_size operand.

    If the condition in step 1 is met, consider applying no hash filters for the processing of the SQL statement by specifying 0 for the adb_sql_exe_hashflt_area_size operand in the client definition.

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

Note

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.