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.

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