 |
|
SQL
Performance Analyzer Using OEM in 11g
Oracle 11g New Features Tips by Burleson
Consulting
July 8, 2008 |
Oracle 11g SQL New Features Tips
The SPA can be found through different links in
the Enterprise Manager. The workload capture can be managed from the
page where STSs can be created, exported and imported. It can be
reached from the Performance Tuning page. In this case, it is a
cluster database:
Figure 2:
Additional Links
The SQL Performance Analyzer can be reached
directly as well as by going to the Advisor Central page. OEM offers
full control for the workload filtering:
Figure 3: Oracle
Enterprise Manager Filter Options
It is also possible to view the SQL for the
workload capture:
Figure 4: SQL
Tuning Set Options
After capturing the workload by creating an STS,
call the SPA on the testing system.
SPA offers three
different options in the OEM:
Figure 5:
Optimizer Upgrade Simulation makes two runs of the
workload with different optimizer options enabled:
Figure 6: Optimizer
Upgrade Simulation
Parameter Change performs two runs of the workload
with different parameter settings, as shown in Figure 7:
Figure 7: Parameter
Change screen
Guided Workflow shows a step by step through a
user defined test:
Figure 8: Guided
Workflow screen
After all steps of the guide workflow have been
performed, view the comparison report for the two test runs. The
report shows the list of regressing and improving SQL statements and
from there the results can be analyzed:
Figure 9:
Comparison Report of Two Test Runs
Examine the details of the execution results for
the single statements and find out what the exact differences for the
metrics collections are:
Figure 10:
From here, call the SQL Performance Advisor and
schedule a tuning task for the regressing statements. The result may
be a recommendation to accept a Sql Profile because a potentially
better execution plan was found by the advisor…
Figure 11: SQL
Performance Advisor Recommendations, Part 1
… or the recommendation to run the SQL Access
Advisor to analyze the workload in order to find out if an additional
index could be helpful for instance, as shown in Figure 12:
Figure 12: SQL Performance Advisor
Recommendations, Part 2
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important New
Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain,
Brian Carr.
You can buy it direct from the publisher for 30% off. |
