You can find out whether the access plans for SQL statements
on a test system remained the same when you deployed those SQL statements
on a production system. If any of the access plans on the production
system need improvement, you can tune the problematic SQL statements
or revert to the access plans that are used on the test system.
About this task
In this procedure, you create a query workload of the SQL
statements for an application that you are testing. You tune this
query workload, and then deploy the application to a production system.
Next, on that production system, you create a query workload of the
same SQL statements. Finally, you compare the access plans that the
SQL statements used on the test system with the access plans that
the statements use on the production system. If there are detrimental
changes to the access plans on the production system, you can correct
the problems by creating and tuning a query workload of the problematic
statements, or by reverting to the access plans from the test system.
To identify and address changes to access plans due to
deploying an application from a test system to a production system:
What to do next
On the
Comparison History page, double-click
the entry for the comparison. On the
View Comparison Results
by Workload EXPLAIN Snapshots and SQL Statements page,
review the
Statements table to look for statements
with differences in the two EXPLAIN snapshots. If you need to share
the results with other people, select
Generate Comparison
HTML Report in the
More actions field
above the table.
If you find that the access plans or estimated
costs of the SQL statements on the production system need improvement,
you can create a query workload from those statements. You can then
tune the query workload. In the More actions field
on the View Comparison Results by Workload EXPLAIN Snapshots
and SQL Statements page, select Create a New
Query Workload for Tuning.
A different approach
would be to replace the access plans on the production system with
the access plans from the test system. In the More actions field
on the View Comparison Results by Workload EXPLAIN Snapshots
and SQL Statements page, select Lock Down Access
Plans for All SQL Statements. If you choose this approach,
you must understand that changes to your system that could improve
the access plans, such as improving statistics or applying maintenance
that contains improvements to the optimizer, will have no effect on
the access plans that you locked down. However, the access plans will
not experience sudden changes and will execute predictably.