13.6 Tuning to shorten SQL statement execution time by re-examining the hash group area size
This section explains how to shorten SQL statement execution time by re-examining the hash group area size specified in the following operands:
-
adb_sql_exe_hashgrp_area_size operand in the server definition
-
adb_sql_exe_hashgrp_area_size operand in the client definition
If you execute SQL statements to which local hash grouping is applied, you might be able to shorten the SQL statement execution time by increasing the hash group area size.
Procedure
-
Check if a shortage of hash grouping area occurred during any processing.
Check the access path statistical information that has been output as SQL trace information. In the information related to hash grouping areas that was output to access path statistical information, check if Y is displayed for the following output item:
-
Hashgrp_area_shortage (whether a space shortage occurred in the hash grouping area)
If local hash grouping processing was performed and Y is displayed, go to step 2.
-
-
Re-evaluate the value of the adb_sql_exe_hashgrp_area_size operand.
If step 1 revealed that there was a local hash grouping process that resulted in a shortage of hash grouping area, data that could not be processed in the hash grouping area might have been stored in a work table for processing. This might have caused an increase in the SQL statement execution time.
In this case, check the following output item in the information related to hash grouping areas that was output as access path statistical information for all the local hash grouping processes whose Hashgrp_area_shortage is Y:
-
Hashgrp_area_sufficient_size (sufficient size (in kilobytes) of hash grouping area that will not result in a shortage of space)
Then, in the server definition or client definition, set the adb_sql_exe_hashgrp_area_size operand to a value that is equal to or larger than the largest one of the values that have been output. This might shorten SQL statement execution time to which local hash grouping is applied.
- Important
-
If you have changed the value of the adb_sql_exe_hashgrp_area_size operands, always re-estimate the HADB server's memory requirement. For details about estimation of the HADB server's memory requirement, see 6.3 Estimating the HADB server's memory requirement.
-
-
Change the server definition or client definition.
Increase the value specified for the adb_sql_exe_hashgrp_area_size operand in the server definition or 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.
-
-
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 grouping areas that is output as access path statistical information, see (d) Information related to hash grouping 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_hashgrp_area_size operand in the server definition, see the description of the adb_sql_exe_hashgrp_area_size operand in 7.2.2 Operands related to performance (set format).
-
For details about the adb_sql_exe_hashgrp_area_size operand in the client definition, see Operands related to performance in the HADB Application Development Guide.
-