Transcript Chapter 7 - Automated Cluster Controlled HADR

IBM Software Group | DB2 Curriculum Course
DB2 Optimizer Guidelines Usage
Speaker name: Anthony E. Reina
Email: [email protected]
1
IBM Software Group | DB2 Information Management Software | Curriculum Course
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/
2
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
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>
 Profile Header
 Global optimization
guidelines section. Optional
but at most one.
 Statement guidelines section.
Zero or more.
</OPTPROFILE>
5
IBM Software Group | DB2 Information Management Software | Curriculum Course
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.
<STMTKEY SCHEMA="TPCD">
<![CDATA[SELECT * FROM TAB1]]>
</STMTKEY>
 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.
<OPTGUIDELINES>
<IXSCAN TABID=“TAB1" INDEX="I_SUPPKEY"/>
</OPTGUIDELINES>
6
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
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,
NAME VARCHAR(128) NOT NULL,
PROFILE BLOB (2M) NOT NULL,
PRIMARY KEY ( SCHEMA, NAME ));

 Schema name
 Profile name
 Profile XML
Using the SYSINSTALLOBJECTS procedure:
db2 "call sysinstallobjects('opt_profiles', 'c', '', '')"
2. Compose optimization profile in XML file prof1.xml.
8
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
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:
Extended Diagnostic Information:
-------------------------------No extended Diagnostic Information for this statement.
Db2exfmt Profile Information section informs on which guidelines were
used:
Profile Information:
-------------------OPT_PROF: (Optimization Profile Name)
EXAMPLE.PROF1
STMTPROF: (Statement Profile Name)
Guidelines for the items out query
10
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
Optimizer Profiles
Example1.xml
12
IBM Software Group | DB2 Information Management Software | Curriculum Course
Optimizer Profiles
Example1.sql
Example1.del
13
IBM Software Group | DB2 Information Management Software | Curriculum Course
Optimizer Profiles
Example1.bat
14
IBM Software Group | DB2 Information Management Software | Curriculum Course
Example1-base.exfmt
Optimizer Profiles
15
IBM Software Group | DB2 Information Management Software | Curriculum Course
Optimizer Profiles
Example1-prof1.exfmt
16
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
Optimizer Profiles
Example 4 : Change Optimization Level
 Change the optimization level for a particular sql.
 Possible Values – same as setting the DFT_QUERYOPT
19
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
Optimizer Profiles
Example 7 : Sub-Query to Join
 By enabling the SUBQ2JOIN, a sub-query is transformed to a
join during query rewrite.
22
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
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
IBM Software Group | DB2 Information Management Software | Curriculum Course
Optimizer Profiles
Q&A
25