 |
|
The Oracle Statspack Analyzer
Oracle Tips by Burleson Consulting |
Since the dawn of Oracle (circa
1989), Oracle DBA's have struggled to interpret the plethora of statistics
that are provided by the Oracle time-series performance reports. These
reports have taken several forms over the years, but the core content has
remained identical:
- bstat-estat reports
- Oracle6 through Oracle8
- STATSPACK reports -
Oracle8i to present
- Automated Workload
Repository reports - Oracle 10g
Originally, Oracle's
elapsed-time reports were intended solely for Oracle technical supports and they
remain full of undocumented performance metrics, page after page of undocumented
gobbledygook:
Statistic Total per Second per Trans
--------------------------------- --------- -------------- ------------
DFO trees
parallelized 1 0.0 0.0
calls to get snapshot scn:
kcmgss 55,918 15.5 860.3
calls to kcmgas
9,929 2.8 152.8
calls to kcmgcs
59 0.0 0.9
cleanout - number of ktugct
calls 11,057 3.1 170.1
The tuning challenge
for the Oracle DBA
Every Oracle tuning expert has
their own method for reading and interpreting STATSPACK reports, sifting through
thousands of lines of details to arrive at a set of valid tuning
recommendations.
With the incorporation of the
Automated Workload Repository into the database kernel in Oracle 10g, STATSPACK
(AWR) reports have become the de-facto standard for Oracle performance reports.
The challenge for the Oracle DBA is to separate the wheat from the chaff and
focus on those metrics that are the most important for understanding the nature
of the performance problem.
To see the manual report
analysis steps, I've devoted an entire chapter to reading a STATSPACK report in
my book "Oracle
Tuning: The Definitive Reference".
Interpreting an Oracle
STATSPACK/AWR report will always be complex and it's doubtful that anyone will
invest the thousands of hours of work required to replicate the analysis of a
human expert.
On the other hand, it is
possible to create basic decision rules (heuristics) that can be generalized and
applied to all STATSPACK and AWR reports. For a simple example, it's easy
to see when an Oracle database has slow disks (latency > 50 ms) and it's always
worth noting whenever a tablespace has disk I/O latency that is more than 30%
slower than the average I/O speed.
Towards an expert
system for automated tuning analysis
There are many applications of
expert systems and applied
artificial Intelligence with Oracle, but the Oracle community has always
desired a software tool that could apply basic decision rules to generic
STATSPACK and AWR reports.
Burleson Consulting in
conjunction with Texas Memory Systems has taken-on this challenge to build an
expert system for intelligent analysis of STATSPACK reported. The web site
(www.statspackanalyzer.org), is
the result of hundreds of hours of work by a consortium of Oracle performance
tuning experts, all working to create valid decision rules that might be applied
to any time-series Oracle report.
A work-in-progress, the
StatspackAnalyzer tool is already
the best STATSPACK analyzer in the world, and we hope to continue to expand and
refine the decision rules as the site sponsors get feedback from actual users
and DBA's.
The
StatspackAnalyzer already does an excellent job
of locating obvious bottlenecks for
server resources (disk, RAM, CPU, Network) and it has many sophisticated
decision rules for analysis of Oracle internal performance.
Of course, locating Oracle
internal resource bottlenecks is more challenging. Analyzing a thousand-line report is not
trivial, and many dozens of observation points may be required to formulate a
decision rule. Further, the generalization of these complex rules may make
them prone to "false positives", the reporting of a non-existent bottleneck.
As the the
StatspackAnalyzer effort gains
momentum, the team of experts will continuously refine and expand the decision
rules, working towards a software solution that will mimic the analysis of a
human expert.
Obviously, Statspackanalyzer
will never be able to model the intuition of an expert because it's impossible
to quantify those "I have a feeling" hunches that distinguish the real
experts, but this tool shows great promise as a research tool.
References:
