You can use an optimization profile
to influence directly how an access plan for an SQL statement is created.
In the profile, you can specify the indexes to use, how to access
tables that the SQL statement references, the join method to use,
and other settings.
About this task
An optimization profile can
contain global guidelines, which apply to all data manipulation language
(DML) statements that are executed while the profile is in effect,
and it can contain specific guidelines that apply to individual DML
statements in a package.
Procedure
To open the visual editor for creating, validating, and
deploying optimization profiles:
- Capture or select the SQL statement for which you want
to create an optimization hint.
- If you want to work from a single captured SQL statement,
follow these steps:
- Capture the SQL statement that you want
to tune. See Locations from which you can capture
an SQL statement for single-query tuning.
- Select the statement and click the Tune
Statement button. The Run Single-Query Advisors and Analysis Tools
page in the Invoke section opens.
- If you want to work from an SQL statement that is in a query
workload, follow these steps:
- Click the Manage tab on the left side of
the workflow assistant.
- On the Manage Workloads page, select the
query workload that contains the SQL statement.
- In the More actions field, select Show
Statements.
- On the Show Statements page, right-click
the SQL statement and select Run Single-Query Advisors
and Tools on the Selected Statement. The Run
Single-Query Advisors and Analysis Tools page of the Invoke section
opens.
- On the left-hand side of the workflow assistant, under Single
Query expand Advanced and select Create
Optimization Profile.
What to do next
Use the sections of the Customize Access Plan
with an Optimization Profile page to specify guidelines
for an optimization profile.
This page
consists of three sections.
- Join diagram and join sequence diagram
- This section appears in the top-left corner of the page.
Join
diagrams display as nodes the tables that a statement references.
Edges between the nodes show the relationships between the tables.
The type of predicate that an edge represents appears alongside the
edge. Predicates can be local predicates or join predicates. You can
double-click a node to change how the access plan accesses the corresponding
table.
Join sequence diagrams display the join sequence in the
access plan for an SQL statement.
Each node that represents
a table displays the following information:
- The name of the table in the statement as rewritten by the DB2
optimizer
- The fully-qualified table name
- The cardinality of the table
- The type of access that is specified for the table
- Editable join sequence diagram
- This section appears in the top-right corner of the page.
Edit
the join sequence by changing the type of joins, or by adding or deleting
joins. You can also add and remove nodes that represent tables that
the SQL statement references.
- Guidelines in the Optimization Profile
- This section appears in the bottom portion of the page.
You can use the
Guidelines
in the Optimization Profile section to complete these
tasks:
- Set guidelines for all statements that use the optimization profile
- Enable or disable the use of materialized query tables. If enabling
the use of them, can specify which MQTs to consider using.
- Enable or disable consideration of computational partition group
optimization. If you enable this consideration, then you can specify
the partition group for the DB2 optimizer to consider using.
- Override the setting of
the REOPT bind option with a different value.
- Override the setting of
the DEGREE bind option with a different value.
- Override the setting of
the QUERYOPT bind option with a different value.
- Enable or disable the
collection of real-time statistics. If you enable this collection,
you can specify the maximum amount of time to allow the DB2 optimizer
to collect these statistics. If the optimizer estimates that collecting
them will require more time, the optimizer does not collect them.
- Enable MQT enforcement.
- Set guidelines for the current SQL statement
- Override the setting of
the REOPT bind option with a different value.
- Override the setting of
the DEGREE bind option with a different value.
- Override the setting of
the QUERYOPT bind option with a different value.
- Enable or disable the
collection of real-time statistics. If you enable this collection,
you can specify the maximum amount of time to allow the DB2 optimizer
to collect these statistics. If the optimizer estimates that collecting
them will require more time, the optimizer does not collect them.
- Enable MQT enforcement.
- Set guidelines that affect the transformations that are considered
during the query rewrite optimization phase, which transforms the
original statement into a semantically equivalent optimized statement.
- Enable or disable the IN-LIST predicate-to-join rewrite transformation.
- Enable or disable the NOT-EXISTS predicate-to-anti-join rewrite
transformation.
- Enable or disable the NOT-IN predicate-to-anti-join rewrite transformation.
- Enable or disable the subquery-to-join rewrite transformation.
- Customize how a table is accessed
- Set a plan optimization guideline.
- Double-click the table in the join diagram and change the way
that the table is accessed.
You can check your optimization
profile for problems, so that you can correct them before deploying
the profile.
Finally, you can deploy the optimization
profile. Deploying means inserting the optimization profile as a row
in the SYSTOOLS.OPT_PROFILE table.
Use the
OPTPROFILE bind option to specify that an optimization profile is
to be used at the package level, or use the CURRENT OPTIMIZATION PROFILE
special register to specify that an optimization profile is to be
used at the statement level.
This special register
contains the qualified name of the optimization profile that is used
by statements that are dynamically prepared for optimization. For
CLI applications, you can use the CURRENTOPTIMIZATIONPROFILE client
configuration option to set this special register for each connection.