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 the SQL statement that you want
to tune. See Locations from which you can capture
an SQL statement for single-query tuning.
- Select one of the captured statements
and click the Invoke Advisors and Tools button.
The Run Single-Query Advisors and Analysis Tools page
in 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.