Runtime Optimization Hints

In writing about changes to SQL optimization hints, I neglected to note that there are actually two types of statement-level hints. I only covered access-level optimization hints last week. The other type of statement level optimization hint is to set the runtime options for a given SQL statement text.

The setup for runtime options hints is similar to that of optimization access path hints. Insert a row into DSN_USERQUERY_TABLE with a unique QUERYNO. In doing this, make sure that no row is found in the user PLAN_TABLE for the given statement, because if there's a row in the PLAN_TABLE when the BIND QUERY LOOKUP(NO) runs, the SYSIBM.SYSQUERYOPTS table won't contain a row. That said, if you do make this mistake, it's a simple fix. Just delete the rows from the user PLAN_TABLE and rerun the BIND QUERY command to populate SYSIBM.SYSQUERYOPTS.

Hints are an either-or proposition. It's not possible for one hint to be both an optimization access path hint and a optimization options hint. Remember: If you're creating an options hint, you must not have rows in the PLAN_TABLE for the given statement. I always delete the rows in DSN_USERQUERY_TABLE and insert new rows with the settings I want to be populated into the access path repository.

The runtime options that can be set using DSN_USERQUERY_TABLE are:

REOPT:    Set bind options that is in effect for the plan
(A:AUTO, 1:ONCE, N:NONE, Y:ALWAYS)
STARJOIN:    Whether Starjoin is Enabled
 (Y:Enabled, N:Disabled, blank:Starjoin not specified)
MAX_PAR_DEGREE:    The maximum parallel degree. 
Valid values between 0 and 254, -1:Not Specified
DEF_CURR_DEGREE:    Whether query parallelism is enabled
            (ONE: Disabled, ANY:Enabled, blank: Disabled
SJTABLES:        The number of tables specified in a query to qualify for star joins processing.
-1: Not Specified
GROUP_MEMBER:    The group member name to which the parameters are to be applied. This column is
 blank if no group member name is specified.

Sample Insert into user DSN_USERQUERY_TABLE to define a system-level option hint for a statement to be executed with REOPT(ALWAYS). The HINT_SCOPE is set to 0 and REOPT is set to ‘Y’:

INSERT INTO DSN_USERQUERY_TABLE          
( QUERYNO, SCHEMA, HINT_SCOPE            
 , QUERY_TEXT, USERFILTER, OTHER_OPTIONS 
 , COLLECTION, PACKAGE, VERSION          
 , REOPT, STARJOIN, MAX_PAR_DEGREE       
 , DEF_CURR_DEGREE, SJTABLES, OTHER_PARMS
)                                        
SELECT 1                                 
     , '' , 0                            
     , PS.STATEMENT, '',''               
     , '', '', ''                        
     , 'Y','', -1                        
     , '', -1, ''                        
FROM SYSIBM.SYSPACKSTMT PS               
WHERE PS.COLLID = 'COLTR05_COLLID'       
  AND PS.NAME = 'ROWSET'                 
  AND PS.VERSION = 'MYVERSION_1'         
  AND PS.STMTNO = 264;

SQL Optimization Hints: What's New

Last week I gave a quick introduction of DB2 SQL optimization hints and noted that DB2 10 brings some key enhancements in this area. Now I'll get into the specifics of what's new.

Prior to DB2 10, the QUERYNO clause was used to associate a query to the related hints. However, because programming changes could potentially alter QUERYNO, errors often occurred. To deal with this problem, programmers began using QUERYNO on the SQL statement. Assigning a fixed value eliminated the possibility of numbers changing due to coding changes. This work-around is effective provided you own the code, but in the case of vendor products, it's generally not an option.

But in the latest release, DB2 addresses the QUERYNO issue by providing statement-level optimization hints -- aka instance-based statement hints. With this method, hints are enforced based on the statement text for the entire DB2 subsystem. This is why they're also referred to as "instance-based" hints. Statement-level hints are stored in the DB2 catalog in a set of tables known as the access path repository.

Use BIND QUERY  LOOKUP(NO) to populate the access path repository. This command reads the statement text, default schema and a set of bind options from every row of DSN_USERQUERY_TABLE, as well as system-level access path hint information from correlated PLAN_TABLE rows. BIND QUERY then inserts the pertinent data into the SYSIBM.SYSQUERY, SYSIBM.SYSQUERYPLAN and SYSIBM.SYSQUERYOPTS catalog tables.

When using statement-level hints, follow these steps:

1)     BIND the package with EXPLAIN(YES) to populate a user PLAN_TABLE with system-level access path hints.

2)     Populate user table DSN_USERQUERY_TABLE with the query text. Use install member SDSNSAMP(DSNTESH) to create the access path repository tables. The best way to populate this table with the statement is to use an INSERT with a select against the SYSIBM.SYSPACKSTMT catalog table.

In this sample, insert statement 264 is used for package name rowset found in collection coltr05_collid.

INSERT INTO DSN_USERQUERY_TABLE          

( QUERYNO, SCHEMA, HINT_SCOPE, QUERY_TEXT, USERFILTER, OTHER_OPTIONS 

 , COLLECTION, PACKAGE, VERSION , REOPT, STARJOIN, MAX_PAR_DEGREE       

 , DEF_CURR_DEGREE, SJTABLES, OTHER_PARMS

)                                        

SELECT PS.STMTNO                         

     , 'COLTR05' , 1                     

     , PS.STATEMENT, '',''               

     , PS.COLLID, PS.NAME, PS.VERSION    

     , '', '', -1                        

     , '', -1, ''                        

FROM SYSIBM.SYSPACKSTMT PS               

WHERE PS.COLLID = 'COLTR05_COLLID'       

  AND PS.NAME = 'ROWSET'                 

  AND PS.VERSION = 'MYVERSION_1'         

  AND PS.STMTNO = 264;

 

3)    Run a new BIND QUERY LOOKUP(NO) command. This will insert hints into the DSN_USERQUERY_TABLE repository based on the statements in the PLAN_TABLE. 

4)     Use BIND QUERY LOOKUP(YES) to verify you have a row in the access repository.  Insert the SQL statement into the DSN_USERQUERY_TABLE and after  executing BIND QUERY LOOKUP(YES) the QUERYID column will be set.

5)     The FREE_QUERY command is used to remove the hint from the access path repository.

As with optimization hints in general, once a statement-level optimization hint is created, DB2 validates and tries to enforce the hints. Hints for static SQL statements are validated and applied when you rebind the package that contains the statements. Hints for dynamic SQL statements are validated and enforced when the statements are prepared.

Once the access-path repository is populated, you should remove the statement from DSN_USERQUERY_TABLE to avoid replacing hints on subsequent BIND QUERY commands. 

Hints are created or replaced for every row in DSN_USERQUERY_TABLE when BIND_QUERY is issued. Consequently, changes to PLAN_TABLE data might result in the replacement of existing hints of either type if old rows remain in the DSN_USERQUERY_TABLE and BIND_QUERY is issued.

 

*updated 7/20/11

 

 

Using DB2 SQL Optimization Hints

Have you considered using DB2 optimization hints? The feature debuted on DB2 for z/OS Version 6 with the introduction of OPTHINT, HINT_USED and PRIMARY_ACCESSTYPE columns on the PLAN_TABLE. 

Hints provide:

• A temporary fix to resolve an immediate crisis
• Access path regression from previously good path
• Lock in access path to stabilize desired access path

However, along with those benefits, using hints has these drawbacks:

• Locked in access path will not change to take advantage of new improved access paths
• Error prone due to changes in QUERYNO during program development
• Complicated to understand and figure out what values should be used when defining the hint.

Before you can start using optimization hints, your DB2 environment must be set up to accomodate hints. First, during the DB2 install, set OPTIMIZATION HINTS on installation panel DSNTIP8 to YES. I also strongly suggest upgrading to the most current PLAN_TABLE definition. Under the default, the PLAN_TABLE used for hints must contain at least the 49 columns defined in DB2 Version 6. By upgrading, you can benefit from all the extra information DB2 can provide. DB2 10 install job hlq.SDSNSAMP(DSNTESC) creates the PLAN_TABLE with 65 columns.

For static SQL, I take these the steps:

1. BIND package with EXPLAIN (YES) to populate the plan_table.
2. Update the rows in the PLAN_TABLE with OPTHINT name (e.g., "goodpath").
3. Set up the BIND package to use OPTHINT('goodpath').
4. Query the PLAN_TABLE to see if the hint was used. Also note the messages in the BIND job, which tell you how many hints have been applied.

DB2 10 brings some useful enhancements for managing hints. "Instance-based" optimization hints help you avoid errors due to changing QUERYNO. I'll go into details about this next week.

Has your organization taken advantage of hints? What advice would you give to those who are looking to use hints? Please share your experiences, good and bad, in Comments.