How to Interpret STATSPACK Output in Oracle 12c
The report you get from running Oracle 12c STATSPACK Output will most likely be more than 50 pages, in text format, and saved in the directory you were in when you created the report. Believe it or not, there is more information in there than most people will use. However, understanding a few key sections can give you a leg up on making use of the results.
Here are some of the key sections you should focus on when looking at the report output:
The First Page: This section contains all the relevant information about the state of the database for which the snapshot period applies. It contains the length of time between snapshots, the number of logged in users, memory component sizes, and the database and instance names.
It also includes some high-level metrics such as the instance memory efficiency percentages, number of physical reads and writes, and SQL parsing information. This section gives you a good overview of what was going on when the snapshots were taken in case you’re just the interpreter of the report and were not involved with the taking of the snapshots.
Top 5 Timed Events: This section is one of our favorites. It boils down to where the database spent most of its time and puts the information into five buckets. If you see any buckets that consume the lion’s share of the time, it can lead you down the first path for focused tuning.
For example, if IO or CPU were in the 90 percent range, you may want to look at the SQL section, the file sections, or the memory-tuning sections and start looking for items to tune that fit those buckets.
SQL Sections: The SQL sections break down the top SQL statements by CPU, elapsed time, physical IO, buffer gets, reads, executions, parses, and shareable memory. These sections can help identify problem SQL that’s responsible for heavy use of resources during the snap period.
A Database Administrator or application developer can also look for specific SQL to identify that is part of the application. You can then attack and tune the SQL.
Tablespace and Datafile IO Stats: Finding what tablespaces and data files comprise most of your reads and writes can help you identify hot (heavily used) files and devices that may benefit from striping or other storage adjustments. You can also use this information to decide whether certain tablespaces may benefit from more separation of objects that may be getting concurrently accessed.
Memory Advisory Sections: The memory advisory sections contain Oracle’s self-evaluation of the different memory pools for things like the shared pool, buffer cache, and PGA. By looking at the findings Oracle has come up with, you can increase the memory areas to tune things such as IO, parsing, or sorting.
Init Parameter Section: This section lists the initialization parameters that were set to non-default values during the report period. You can look for anything which represents an anomaly or a value which can explain certain performance characteristics. When you’re comparing different reports over time, this section can also help identify instance configuration parameters that may have taken place.
For example, if you have a report from a week ago when performance was good and a report from today where performance was poor, a good way to start would be comparing the initialization parameters. Finding a difference is what would be called “low-hanging fruit” or “easy pickings” as a potential cause for bad performance.