DB2 Optimizer Guidelines Usage

1. DB2 Optimizer Guidelines Usage Speaker name: Anthony E. Reina Email: aereina@ca.ibm.com

2. IBM Software Accelerated Value Program • The IBM Software Accelerated Value Program delivers a proactive, cost-reducing, and productivity enhancing advisory service. The program pairs you with an assigned team who build a foundational understanding of your overall environment. Through that understanding, the trusted partnet works to facilitate faster deployment, lifecycle leadership, risk mitigation, and more by identifying ways to improve your environment, staff skill set, and processes. http://www-01.ibm.com/software/support/acceleratedvalue/

3. Optimizer Profiles • Mechanism to alter default access plan • Overrides the default access plan selected by the optimizer. • Instructs the optimizer how to perform table access or join. • Allows users to control specific parts of access plan. • Can be employed without changing the application code • Compose optimization profile, add to db, rebind targeted packages. • Should only be used after all other tuning options exhausted • Query improvement, RUNSTATS, indexes, optimization class, db and dbm configs, etc. • Should not be employed to permanently mitigate the effect of inefficient queries. 3

4. Optimizer Profiles Anatomy of an optimizer profile: • XML document • Elements and attributes used to define optimization guidelines. • Must conform to a specific optimization profile schema. • Profile Header (exactly one) • Meta data and processing directives. • Example: schema version. • Global optimization guidelines (at most one) • Applies to all statements for which profile is in effect. • Example: eligible MQTs guideline defining MQTs to be considered for routing. • Statement optimization guidelines (zero or more) • Applies to a specific statement for which profile is in effect. • Specifies directives for desired execution plan. 4

5. Optimizer Profiles Anatomy of an optimizer profile (continued): <?xml version="1.0" encoding="UTF-8"?> <OPTPROFILE VERSION="9.7.0.0"> <OPTGUIDELINES> <MQT NAME="Test.AvgSales"/> <MQT NAME="Test.SumSales"/> </OPTGUIDELINES> <STMTPROFILE ID="Guidelines for TPCD"> <STMTKEY SCHEMA="TPCD"> <![CDATA[SELECT * FROM TAB1]]> </STMTKEY> <OPTGUIDELINES> <IXSCAN TABLE=“TAB1" INDEX="I_SUPPKEY"/> </OPTGUIDELINES> </STMTPROFILE> </OPTPROFILE> • Profile Header  Global optimization guidelines section. Optional but at most one.  Statement guidelines section. Zero or more. 5

6. Optimizer Profiles • Each statement profile contains a statement key (STMTKEY) and one or more statement-level optimization guidelines (OPTGUIDELINES). • The statement key identifies the statement or query to which the statement-level optimization guidelines apply. • The statement-level optimization guidelines identify one of more directives, e.g. access or join requests, which specify methods for accessing or joining tables in the statement. 6

7. Optimizer Profiles Optimizer guidelines: • Access plan guidelines: • Base access request (method to access a table, e.g. TBSCAN, IXSCAN) • Join request (method and sequence for performing a join, e.g. HSJOIN, NLJOIN) • Query rewrite guidelines: • INLIST2JOIN (convert IN-list to join) • SUBQ2JOIN (convert subquery to join) • NOTEX2AJ (convert NOT EXISTS subquery to anti-join) • NOTIN2AJ (convert NOT IN subquery to anti-join) • General optimization guidelines: • REOPT (ONCE/ALWAYS/NONE) • MQT choices • QRYOPT (set the query optimization class) • RTS (limit the amount of time taken by real-time statistics collection) 7

8. Optimizer Profiles How to create and use an optimizer profile: 1. Create the SYSTOOLS.OPT_PROFILE table in the SYSTOOLS schema: • Using the CREATE TABLE statement: CREATE TABLE SYSTOOLS.OPT_PROFILE ( SCHEMA VARCHAR(128) NOT NULL,  Schema name NAME VARCHAR(128) NOT NULL,  Profile name PROFILE BLOB (2M) NOT NULL,  Profile XML PRIMARY KEY ( SCHEMA, NAME )); • Using the SYSINSTALLOBJECTS procedure: db2 "call sysinstallobjects('opt_profiles', 'c', '', '')" 2. Compose optimization profile in XML file prof1.xml. 8

9. Optimizer Profiles How to create and use an optimizer profile (continued): 3. Create file prof1.del as follows: "EXAMPLE","PROF1","prof1.xml" 4. Import prof1.del into SYSTOOLS.OPT_PROFILE table as follows: IMPORT FROM prof1.del OF DEL MODIFIED BY LOBSINFILE INSERT INTO SYSTOOLS.OPT_PROFILE; 5. Enable the PROF1 profile for the current session: db2 "set current optimization profile = 'EXAMPLE.PROF1'" 9

10. Optimizer Profiles Checking if the guidelines are applied: • SQL0437W reason code 13 is issued if there were problems with applying the guidelines (usually due to syntax error) • Db2exfmt output contains a section with details on the problems encountered while attempting to apply the guidelines: • Db2exfmt Profile Information section informs on which guidelines were used: 10

11. Optimizer Profiles Example 1 • Examine the optimize profile in example1.xml: • Global guideline forces OPTLEVEL 7 for all queries in the current session. • Statement guideline forces nested loop join method (NLJOIN) for tables MOVIE and TRANSACTION_DETAIL for a given query. • Run example1.bat – this will create and apply an optimizer profile named PROF1. It will also collect db2exfmt output before and after using the profile. • Compare the before and after .exfmt files – how can you tell if the guidelines were applied? 11

12. Optimizer Profiles Example1.xml 12

13. Optimizer Profiles Example1.sql Example1.del 13

14. Optimizer Profiles Example1.bat 14

15. Optimizer Profiles Example1-base.exfmt 15

16. Optimizer Profiles Example1-prof1.exfmt 16

17. Optimizer Profiles Example 2 : Specify to always use a specific index • Force the access plan to always use T1X index when accessing T1 table. 17

18. Optimizer Profiles Example 3 : Use REOPT always • Defer the query optimization until the input variables have been provided during runtime. • Possible Values – ONCE, ALWAYS, or NONE 18

19. Optimizer Profiles Example 4 : Change Optimization Level • Change the optimization level for a particular sql. • Possible Values – same as setting the DFT_QUERYOPT 19

20. Optimizer Profiles Example 5 : Runtime Degree setting • Change the runtime degree of a query for INTRA-PARTITION environment. • Possible Values – same as setting the DFT_DEGREE 20

21. Optimizer Profiles Example 6 : INLIST to Nested Loop Join • Change the list of values (inlist) to use the GENROW function which is more efficient and can improve query performance. 21

22. Optimizer Profiles Example 7 : Sub-Query to Join • By enabling the SUBQ2JOIN, a sub-query is transformed to a join during query rewrite. 22

23. Optimizer Profiles Example 8 : Influencing Join Order • Most of the time the join order of a query will greatly determine the query execution performance, as filtering rows early as possible is the most efficient. 23

24. Usefull Links • Developer Work Article - Influence query optimization with optimization and statistical views in DB2 V9x http://www.ibm.com/developerworks/data/library/techarticle/dm-0612chen/index.html • DB2 V9.7 Info Center - Optimization Profiles and Quidelines http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Fc0024522.html 24

25. Optimizer Profiles Q&A 25