Oracle workload analysis tips
Oracle Tips by Burleson Consulting
Oracle started to make a
commitment to workload analysis with the introduction of
STATSPACK in Oracle 8i, and we see even more exciting workload
analysis features in Oracle 11g, with the SQL performance
Analyzer, a tool that allows you to capture a representative
Oracle workload from AWR and test it under different
The fundamentals of proactive tuning are predicated on Oracle
workload analysis and it's very clear that holistic tuning is
the best way to achieve the most efficient allocation of system
resources (disk, RAM, CPU).
The best tool of all for Oracle
workload analysis is the Automatic Workload Repository (AWR), an
exciting new feature introduced in Oracle10g that is a gold-mine
of performance insights. The AWR history is crucial for analysis
and tuning, and the AWR forms a knowledge base for linear
regression and trend analysis.
The AWR workload data can also be
used as input to the Oracle Data Mining tool, and Dr. Hamm's
Data Mining", has some great insights into analyzing
workloads with ODM.
Analyzing an Oracle workload is
straightforward and can be somewhat automated, but workload
analysis can be complex, especially in-light of the thousands of
metrics. I have been working with Texas Memory System to
create an expert system to automate workload analysis, a free
What's in an Oracle workload?
To a strategic manager, the Oracle
workload is nothing more than a measure of throughput (often
expressed in rows fetched per second) and a measure of overall
RAM and CPU consumption.
Other measures of Oracle workload
might include transactions per second, queries per second, or
transaction arrival rate, but they all serve a common purpose,
to allow management to correlate Oracle performance with
Oracle Corporation has invested
heavily in workload analysis technology and has this patent
optimizer cost model", which contains some fascinating
features of Oracle patented optimizer workload technology:
"For example, when the workload on the DBMS is a large number
of short, CPU-intensive transactions, as is typical for an on-line
transaction processing (OLTP) environment, then the demand on the CPU is
high, relative to the demand on the I/O system.
Thus, the optimal execution plan for OLTP is one that favors minimizing CPU
usage over minimizing I/O usage.
However, when the workload is a smaller number of long, I/O-intensive
transactions, as is typical for a night-time batch job processing
environment (BATCH), then the demand on the CPU is low, relative to the
demand on the I/O system. Thus, the optimal execution plan for BATCH
processing is one that favors minimizing I/O usage over minimizing CPU
Conventional cost-based optimizers will not only be inaccurate under these
conditions, but they will be inaccurate in different ways at different
times, as the relative costs change. These variable inaccuracies render the
optimization decisions made by conventional optimizers even less useful."
Regardless of the metrics used,
Oracle workload analysis is critical to exception-based tuning,
predictive modeling and Oracle trend analysis.
I have several hundred pages
dedicated to Oracle workload analysis
in my book "Oracle
Tuning: The Definitive Reference", highly recommended for
understanding Oracle workload analysis.
My other Oracle workload analysis