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 configurations.

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 book "Oracle 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 tool dubbed Statspackanalyzer.

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 hardware utilization.

Oracle Corporation has invested heavily in workload analysis technology and has this patent titled "Query 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 usage.

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 references include: