10.11.8 Using SQL trace information to tune SQL statements

Prepare the information that will be used for tuning.

Prepare the SQL trace information so that you can view the information.

Identify an SQL statement that took too long to execute.

Check the execution durations of the SQL statements that are displayed under exe_time in the SQL statement execution information that is output as SQL trace information. Identify an SQL statement that took too long to execute.

Make a note of the values displayed in the SQL statement execution information.

Make a note of the following values in the SQL statement execution information that was identified in step 2:

• con_id (connection ID)

• con_num (connection sequence number)

• stmt_hdl (statement handle allocated to the SQL statement)

• sql_serial_num (SQL statement sequence number)

Check the basic information for the SQL statements.

Locate the set of SQL statement basic information that satisfies the following condition:

• The values of con_id, con_num, stmt_hdl, and sql_serial_num are the same as those obtained in step 3.

For details about how to locate the target SQL statement basic information from multiple sets of SQL statement basic information, see 10.11.4 Examples of output of SQL trace information and how to interpret the information.

Check the executed SQL statement and access path information and the dynamic parameter information.

Check the executed SQL statement and access path information and the dynamic parameter information that were output immediately after the SQL statement basic information identified in step 4.

Check the statistical information.

See the following statistical information that was output immediately after the SQL statement execution information, identified in step 2, and use it to tune the SQL statement:

• SQL statement statistical information

• Access path statistical information

Prepare the information that will be used for tuning.

Prepare the SQL trace information so that you can view the information.

Identify an SQL statement that took too long to execute.

Check the execution durations of the SQL statements that are displayed under exe_time in the SQL statement execution information that is output as SQL trace information. Identify an SQL statement that took too long to execute.

Make a note of the values displayed in the SQL statement execution information.

Make a note of the following values in the SQL statement execution information that was identified in step 2:

• con_id (connection ID)

• con_num (connection sequence number)

• stmt_hdl (statement handle allocated to the SQL statement)

• sql_serial_num (SQL statement sequence number)

Check the SQL statement execution information.

Locate the SQL statement execution information that satisfies the following conditions:

• The values of con_id, con_num, stmt_hdl, and sql_serial_num are the same as those obtained in step 3.

• If a retrieval SQL statement was executed, the call type is PREP or OPEN.

• If an update SQL statement was executed, the call type is PREP, EXEC, or EXDI.

Check the executed SQL statement and access path information and the dynamic parameter information.

Check the following information that is output immediately after the SQL statement execution information identified in step 4:

• If the call type is PREP or EXDI, check the executed SQL statement and access path information.

• If the call type is OPEN or EXEC, check the dynamic parameter information.

Check the SQL statement execution information.

Locate the SQL statement execution information that satisfies both of the following conditions:

• The values of con_id, con_num, stmt_hdl, and sql_serial_num are the same as those obtained in step 3.

• The call type is CLOS, EXEC, or EXDI.

Check the statistical information.

See the following statistical information that has been output immediately after the SQL statement execution information, identified in step 6, and use it to tune the SQL statement:

• SQL statement statistical information

• Access path statistical information