SQL tuning using OPTHINT technique
DB2 Access Path : The DB2 optimizer can choose from a variety of different techniques as it creates optimal access paths for each SQL statement. These techniques range from a simple series of sequential reads to much more complicated strategies such as using multiple indexes to access data. It accomplishes this by parsing the SQL statement to determine which tables and columns must be accessed. It then queries statistics stored in the DB2 Catalog to determine the best method of accomplishing the tasks necessary to satisfy the SQL request.
The Access path of Static SQL changes only during BIND or REBIND. In case of Dynamic SQL the access path can change frequently as the access path is created during run time.
Static SQL Tuning : Tunning static SQL using OPTHINT technique is straight forward when you know the best access path. Update the PLAN_Table with the correct access path and update the field OPTHINT with XXXXXXXX – max 8 character OPTHINT value. Rebind the package with OPTHINT(XXXXXXXX).
Examine the SQLCODE and take appropriate action if needed
a. SQLCODE = +394 means the hint was used
b. SQLCODE = +395 means the hint was not used
Dynamic SQL Tuning: Unlike static SQL, which is available in the catalog tables, dynamic SQLs pose significant challenges to a DBA unless you capture the best access path and store it on the plan table.
- EXPLAIN STMTCACHE ALL
– EXPLAIN STMTCACHE STMTID
– EXPLAIN STMTCACHE STMTTOKEN
EXPLAIN STMTCACHE ALL dumps the entire statement cache into the DSN_STATEMENT_CACHE table
Column QUERYNO is given the value of the statement ID in every row inserted into the plan table, statement table, or function table by the EXPLAIN statement.
Update the PLAN_Table with the correct access path and update the field OPTHINT with XXXXXXXX – max 8 character OPTHINT value.
If a hint is provided for dynamic SQL, the application should issue the following:
1. SET CURRENT OPTIMIZATION HINT = ‘XXXXXXXX’
2. Add corresponding QUERYNO ####### to the Dynamic SQL
3. PREPARE the dynamic SQL statement
4. Examine the SQLCODE and take appropriate action if needed
a. SQLCODE = +394 means the hint was used
b. SQLCODE = +395 means the hint was not used.
When using dynamic SQL, using the QUERYNO clause has some advantages. Applications,such as DSNTEP2 or QMF, use the same PREPARE statement for each dynamic statement. Therefore the same query number (statement number) is used to identify each dynamic statement. By assigning a QUERYNO to each statement that uses optimization hints, you can eliminate ambiguity as to which rows in the PLAN_TABLE are associated with each query.
For Rexx ProgramsFor Dynamic SQL tuning for Rexx Program below fields also need to be updated other OPTHINT field.
PROGNAME = ‘DSNREXX’
COLLID = ‘DSNREXX’
VERSION = 'UK55753' For DB2 version 10
‘UK35514’ – For DB2 version 9.
‘UK35513’ – For DB2 version 8.
Note : This values can be obtained for SYSIBM.SYSPACKAGE for required version
When searching the PLAN_TABLE for qualifying rows, the statement being prepared must match up on the following columns: QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID and OPTHINT. The query number (QUERYNO) is normally the same as the statement number, unless the QUERYNO clause was used in the SQL statement.
For BMC Unload - For Dynamic SQL tuning for BMC Program below fields also need to be updated other OPTHINT field. UNLOAD PLUS for DB2 does not currently support the SQL statement SET CURRENT OPTIMIZATION HINT. Unload plus needs BMC package rebind with OPTHINT, which makes it more complex to make it work.
PROGNAME ='ADUEDSQ*’ (ADUEDSQ8 for DB2V8, and ADUEDSQL for DB2V9)
COLLID = 'ADU0920'
VERSION = 'ADU_V093_U_BPU2706’
Note : This values can be obtained for SYSIBM.SYSPACKAGE for required version
When searching the PLAN_TABLE for qualifying rows, the statement being prepared must match up on the following columns: QUERYO, APPLNAME, PROGNAME, VERSION, COLLID and OPTHINT. The query number (QUERYNO) is normally the same as the statement number, unless the QUERYNO clause was used in the SQL statement.
For DSNTEP2 and DSNTAIUL : It is simple and straight forward. We can explain the SQL using them and apply the required access change with OPTHINT.
The application should issue the following:
1. SET CURRENT OPTIMIZATION HINT = ‘XXXXXXXX’
2. Add corresponding QUERYNO ####### to the Dynamic SQL
Verify by explaining again to confirm it is picking the access path. This can be confirmed either by checking the SQLCODE = +394 or HINT_USED value in the PLAN Table.
For DSNTEP4 - It is not good for running the SQL with OPTHINT. It can used only to check whether OPTHINT will be applied or not. Actual SQL will not run when OPTHINT is applied, it will just say whether OPTHINT will be used or not alone.
DB2 Access Path : The DB2 optimizer can choose from a variety of different techniques as it creates optimal access paths for each SQL statement. These techniques range from a simple series of sequential reads to much more complicated strategies such as using multiple indexes to access data. It accomplishes this by parsing the SQL statement to determine which tables and columns must be accessed. It then queries statistics stored in the DB2 Catalog to determine the best method of accomplishing the tasks necessary to satisfy the SQL request.
The Access path of Static SQL changes only during BIND or REBIND. In case of Dynamic SQL the access path can change frequently as the access path is created during run time.
Static SQL Tuning : Tunning static SQL using OPTHINT technique is straight forward when you know the best access path. Update the PLAN_Table with the correct access path and update the field OPTHINT with XXXXXXXX – max 8 character OPTHINT value. Rebind the package with OPTHINT(XXXXXXXX).
Examine the SQLCODE and take appropriate action if needed
a. SQLCODE = +394 means the hint was used
b. SQLCODE = +395 means the hint was not used
Dynamic SQL Tuning: Unlike static SQL, which is available in the catalog tables, dynamic SQLs pose significant challenges to a DBA unless you capture the best access path and store it on the plan table.
- EXPLAIN STMTCACHE ALL
– EXPLAIN STMTCACHE STMTID
– EXPLAIN STMTCACHE STMTTOKEN
EXPLAIN STMTCACHE ALL dumps the entire statement cache into the DSN_STATEMENT_CACHE table
Column QUERYNO is given the value of the statement ID in every row inserted into the plan table, statement table, or function table by the EXPLAIN statement.
Update the PLAN_Table with the correct access path and update the field OPTHINT with XXXXXXXX – max 8 character OPTHINT value.
If a hint is provided for dynamic SQL, the application should issue the following:
1. SET CURRENT OPTIMIZATION HINT = ‘XXXXXXXX’
2. Add corresponding QUERYNO ####### to the Dynamic SQL
3. PREPARE the dynamic SQL statement
4. Examine the SQLCODE and take appropriate action if needed
a. SQLCODE = +394 means the hint was used
b. SQLCODE = +395 means the hint was not used.
When using dynamic SQL, using the QUERYNO clause has some advantages. Applications,such as DSNTEP2 or QMF, use the same PREPARE statement for each dynamic statement. Therefore the same query number (statement number) is used to identify each dynamic statement. By assigning a QUERYNO to each statement that uses optimization hints, you can eliminate ambiguity as to which rows in the PLAN_TABLE are associated with each query.
For Rexx ProgramsFor Dynamic SQL tuning for Rexx Program below fields also need to be updated other OPTHINT field.
PROGNAME = ‘DSNREXX’
COLLID = ‘DSNREXX’
VERSION = 'UK55753' For DB2 version 10
‘UK35514’ – For DB2 version 9.
‘UK35513’ – For DB2 version 8.
Note : This values can be obtained for SYSIBM.SYSPACKAGE for required version
When searching the PLAN_TABLE for qualifying rows, the statement being prepared must match up on the following columns: QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID and OPTHINT. The query number (QUERYNO) is normally the same as the statement number, unless the QUERYNO clause was used in the SQL statement.
For BMC Unload - For Dynamic SQL tuning for BMC Program below fields also need to be updated other OPTHINT field. UNLOAD PLUS for DB2 does not currently support the SQL statement SET CURRENT OPTIMIZATION HINT. Unload plus needs BMC package rebind with OPTHINT, which makes it more complex to make it work.
PROGNAME ='ADUEDSQ*’ (ADUEDSQ8 for DB2V8, and ADUEDSQL for DB2V9)
COLLID = 'ADU0920'
VERSION = 'ADU_V093_U_BPU2706’
Note : This values can be obtained for SYSIBM.SYSPACKAGE for required version
When searching the PLAN_TABLE for qualifying rows, the statement being prepared must match up on the following columns: QUERYO, APPLNAME, PROGNAME, VERSION, COLLID and OPTHINT. The query number (QUERYNO) is normally the same as the statement number, unless the QUERYNO clause was used in the SQL statement.
For DSNTEP2 and DSNTAIUL : It is simple and straight forward. We can explain the SQL using them and apply the required access change with OPTHINT.
The application should issue the following:
1. SET CURRENT OPTIMIZATION HINT = ‘XXXXXXXX’
2. Add corresponding QUERYNO ####### to the Dynamic SQL
Verify by explaining again to confirm it is picking the access path. This can be confirmed either by checking the SQLCODE = +394 or HINT_USED value in the PLAN Table.
For DSNTEP4 - It is not good for running the SQL with OPTHINT. It can used only to check whether OPTHINT will be applied or not. Actual SQL will not run when OPTHINT is applied, it will just say whether OPTHINT will be used or not alone.
No comments:
Post a Comment