13.2.5 Reducing the execution time of SQL statements that perform table scans

Check the number of times a buffer shortage occurred in the table scan buffer, and the table scan buffer's page hit rate.

Check the value of the following item in the global buffer statistical information that is output when the adbstat command is executed:

• DBbuff_tblscan_failed_cnt (number of times a buffer shortage occurred in the table scan buffer)

• DBbuff_tblscan_hit_rate (page hit rate in the table scan buffer)

If the value that is output to DBbuff_tblscan_failed_cnt is at least 1 and the value that is output to DBbuff_tblscan_hit_rate is low, a page shortage has occurred in the table scan buffer. Consequently, data was read into the global buffer during the table scan. If this case applies, go to step 2.

Terminate the HADB server.

Terminate the HADB server by executing the adbstop command.

Change the server definition.

To the value specified for the -v option of the adbbuff operand in the server definition, add the value specified for the adb_sys_rthd_num operand in the server definition.

Start the HADB server.

Execute the adbstart command to start the HADB server.

Re-execute the SQL statement that performs a table scan.

After re-executing the SQL statement that performs a table scan, perform step 1 to check whether the value output to DBbuff_tblscan_failed_cnt has decreased. Also, check whether the value output to DBbuff_tblscan_hit_rate has increased.

Repeat steps 1 to 5.

If you make adjustments by repeating steps 1 to 5 until the value that is output to DBbuff_tblscan_failed_cnt approaches 0, the value that is output to DBbuff_tblscan_hit_rate increases, improving the page hit rate in the table scan buffer. As a result, you can sometimes reduce the execution time of an SQL statement that performs table scans.

Check the number of times a buffer shortage occurred in the table scan buffer.

Check the values of the following items in the SQL statement statistical information that was output when the adbstat command was executed:

• DBbuff_tblscan_failed_cnt (number of times a buffer shortage occurred in the table scan buffer)

• DBbuff_tblscan_insufficient_buff_num (number of table scan buffer sectors in which a shortage occurred)

If the value that was output to DBbuff_tblscan_failed_cnt is at least 1, a table scan buffer shortage has occurred. Consequently, data was read into the global buffer during the table scan. If this case applies, go to step 2.

Terminate the HADB server.

Terminate the HADB server by executing the adbstop command.

Change the server definition.

To the value specified for the -v option of the adbbuff operand in the server definition, add the value that was output to DBbuff_tblscan_insufficient_buff_num.

Start the HADB server.

Execute the adbstart command to start the HADB server.

Re-execute the SQL statement that performs a table scan.

By making these adjustments, you can sometimes reduce the execution time of an SQL statement that performs table scans.