13.2.2 Reducing the SQL statement execution time

Check the global buffer 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_page_hit_rate (global buffer page hit rate)

Reassess the global buffer allocation method.

If the DBbuff_page_hit_rate value is 50 or smaller, consider the following three actions:

• Increase the number of pages in global buffers.

  The number of pages in the global buffers allocated to DB areas might be too small. If so, increase the value specified for the -p option of the adbbuff operand.

• Allocate a dedicated global buffer.

  If a single global buffer is allocated to multiple DB areas, use the adbbuff operand to allocate a dedicated global buffer to each DB area.

• Allocate a table scan buffer.

  If there is an SQL statement that performs table scans, specify the -v option in the adbbuff operand to allocate a table scan buffer.

Terminate the HADB server.

Terminate the HADB server by executing the adbstop command.

Change the server definition.

Change the specification of the adbbuff operand in the server definition based on the policies determined in step 2.

Start the HADB server.

Execute the adbstart command to start the HADB server.

Check the global buffer hit rate.

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

• DBbuff_page_hit_rate (global buffer page hit rate)

Reassess the global buffer allocation method.

If the DBbuff_page_hit_rate value, which you checked in step 1, is 50 or smaller, consider taking one or more of the following three actions, as appropriate:

• Increase the number of pages in global buffers.

  There might be too few pages in the global buffers allocated to DB areas. If so, increase the value specified for the -p option of the adbbuff operand.

• Allocate a dedicated global buffer.

  If a single global buffer is allocated to multiple DB areas, check DBbuff_page_request_cnt (number of global buffer page requests) in the SQL statement statistical information. If the number of page requests is large for any DB area, use the adbbuff operand to allocate a dedicated global buffer to that DB area.

• Allocate a table scan buffer.

  If there is an SQL statement that performs table scans, specify the -v option in the adbbuff operand to allocate a table scan buffer.

Terminate the HADB server.

Terminate the HADB server by executing the adbstop command.

Change the server definition.

Change the specification of the adbbuff operand in the server definition based on the policies determined in step 2.

Start the HADB server.

Execute the adbstart command to start the HADB server.