Verifying that optimization guidelines have been used

The optimizer makes every attempt to adhere to the optimization guidelines that are specified in an optimization profile; however, the optimizer can reject invalid or inapplicable guidelines.

Before you begin

Explain tables must exist before you can use the explain facility. The data definition language (DDL) for creating the explain tables is contained in EXPLAIN.DDL, which can be found in the misc subdirectory of the sqllib directory.

Procedure

To verify that a valid optimization guideline has been used:

  1. Issue the EXPLAIN statement against the statement to which the guidelines apply.
    If an optimization guideline was in effect for the statement using an optimization profile, the optimization profile name appears as a RETURN operator argument in the EXPLAIN_ARGUMENT table. And if the optimization guideline contained an SQL embedded optimization guideline or statement profile that matched the current statement, the name of the statement profile appears as a RETURN operator argument. The types of these two new argument values are OPT_PROF and STMTPROF.
  2. Examine the results of the explained statement.
    The following query against the explain tables can be modified to return the optimization profile name and the statement profile name for your particular combination of EXPLAIN_REQUESTER, EXPLAIN_TIME, SOURCE_NAME, SOURCE_VERSION, and QUERYNO:
       SELECT VARCHAR(B.ARGUMENT_TYPE,   9) as TYPE, 
        VARCHAR(B.ARGUMENT_VALUE, 24) as VALUE 
 
   FROM   EXPLAIN_STATEMENT A, EXPLAIN_ARGUMENT B 
 
   WHERE  A.EXPLAIN_REQUESTER = 'SIMMEN' 
     AND  A.EXPLAIN_TIME      = '2003-09-08-16.01.04.108161' 
     AND  A.SOURCE_NAME       = 'SQLC2E03' 
     AND  A.SOURCE_VERSION    = '' 
     AND  A.QUERYNO           = 1 
 
     AND  A.EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER 
     AND  A.EXPLAIN_TIME      = B.EXPLAIN_TIME 
     AND  A.SOURCE_NAME       = B.SOURCE_NAME 
     AND  A.SOURCE_SCHEMA     = B.SOURCE_SCHEMA 
     AND  A.SOURCE_VERSION    = B.SOURCE_VERSION 
     AND  A.EXPLAIN_LEVEL     = B.EXPLAIN_LEVEL 
     AND  A.STMTNO            = B.STMTNO 
     AND  A.SECTNO            = B.SECTNO 
 
     AND  A.EXPLAIN_LEVEL     = 'P' 
 
     AND  (B.ARGUMENT_TYPE  = 'OPT_PROF' OR ARGUMENT_TYPE = 'STMTPROF') 
     AND  B.OPERATOR_ID = 1
    If the optimization guideline is active and the explained statement matches the statement that is contained in the STMTKEY element of the optimization guideline, a query that is similar to the previous example produces output that is similar to the following output. The value of the STMTPROF argument is the same as the ID attribute in the STMTPROFILE element.
       TYPE      VALUE
   --------- --------------------------
   OPT_PROF  NEWTON.PROFILE1 
   STMTPROF  Guidelines for SAMP Q9