Creating statement-level optimization guidelines

The statement-level optimization guidelines section of the statement profile is made up of one or more access or join requests, which specify methods for accessing or joining tables in the statement.

Before you begin

Exhaust all other tuning options. For example:

Ensure that the data distribution statistics have been recently updated by the RUNSTATS utility.
Ensure that the data server is running with the proper optimization class setting for the workload.
Ensure that the optimizer has the appropriate indexes to access tables that are referenced in the query.

Procedure

To create statement-level optimization guidelines:

Create the optimization profile in which you want to insert the statement-level guidelines.
Run the explain facility against the statement to determine whether optimization guidelines would be helpful. Proceed if that appears to be the case.

Obtain the original statement by running a query that is similar to the following:

   select statement_text
     from explain_statement
     where explain_level = '0' and
       explain_requester = 'SIMMEN' and
       explain_time      = '2003-09-08-16.01.04.108161' and
       source_name       = 'SQLC2E03' and
       source_version    = '' and
       queryno           = 1

Edit the optimization profile and create a statement profile, inserting the statement text into the statement key.

For example:

   <STMTPROFILE ID="Guidelines for SAMP Q9">
      <STMTKEY SCHEMA="SAMP"><![CDATA[SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE,
        S.S_COMMENT
        FROM PARTS P, SUPPLIERS S, PARTSUPP PS
        WHERE P_PARTKEY = PS.PS_PARTKEY AND S.S_SUPPKEY = PS.PS_SUPPKEY
        AND P.P_SIZE  = 39 AND P.P_TYPE  = 'BRASS' AND S.S_NATION
        = 'MOROCCO' AND
        PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST)
        FROM PARTSUPP PS1, SUPPLIERS S1
        WHERE P.P_PARTKEY  = PS1.PS_PARTKEY AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
        AND S1.S_NATION  = S.S_NATION)]]>
      </STMTKEY>
  </STMTPROFILE>

Insert statement-level optimization guidelines after the statement key. Use exposed names to identify the objects that are referenced in access and join requests.
The following is an example of a join request:
```
   <OPTGUIDELINES>
     <HSJOIN>
       <TBSCAN TABLE='PS1'/>
       <IXSCAN TABLE='S1'
         INDEX='I1'/>
     </HSJOIN>
   </OPTGUIDELINES>
```
Validate the file and save it.

Results

If expected results are not achieved, make changes to the guidelines or create additional guidelines, and update the optimization profile, as appropriate.