Undestanding AWR

Instance Efficiency

The Instance Efficiency section continues to grow and shows information for many of the common hit ratios

Things to look for include the following:

   A Buffer Nowait % of less than 99 percent. This value is the ratio of hits on a request for a specific buffer where the buffer was immediately available in memory. If the ratio is low, then there are (hot) blocks being contended for that should be found in the Buffer Wait section.

   A Buffer Hit % of less than 95 percent. This value is the ratio of hits on a request for a specific buffer when the buffer was in memory and no physical I/O was needed. While originally one of the few methods of measuring memory efficiency, it still is an excellent method for showing how often you need to do a physical I/O, which merits further investigation as to the cause. Unfortunately, if you have unselective indexes that are frequently accessed, that will drive your hit ratio higher, which can be a misleading indication of good performance to some DBAs. When you effectively tune your SQL and have effective indexes on your entire system, this issue is not encountered as frequently and the hit ratio is a better performance indicator. A high hit ratio is not a measure of good performance, but a low hit ratio is often a sign of performance that can be improved or should at least be looked into.

   A hit ratio that is steadily at 95 percent and then one day goes to 99 percent should be checked for bad SQL or a bad index that is causing a surge of logical reads (check the load profile and top buffer gets SQL).

   A hit ratio that is steadily at 95 percent and then drops to 45 percent should be checked for bad SQL or a dropped index (check the top physical reads SQL) causing a surge in physical reads that are not using an index or an index that has been dropped (I’ve seen this more often than you can imagine).

   A Library Hit % of less than 95 percent. A lower library hit ratio usually indicates that SQL is being pushed out of the shared pool early (could be due to a shared pool that is too small). A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case, a smaller shared pool may only be a bandage that potentially fixes a resulting library latch problem). Despite the rants about lowering your shared pool all the time to fix library cache and shared pool latching issues, most multiterabyte systems I’ve seen with heavy usage have shared pools in the gigabytes without any issues because they’ve fixed the SQL issues. You must fix the problem (use bind variables or CURSOR_SHARING) and then appropriately size the shared pool. I’ll discuss this further when I get to latch issues.

   An In-memory Sort % of less than 95 percent in OLTP. In an OLTP system, you really don’t want to do disk sorts. Setting the MEMORY_TARGET or PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE in previous versions) initialization parameter effectively eliminates this problem. Note that In-memory Sort % appears only in the AWR Report (as in 11g), not in the Statspack Report.

   A Soft Parse % of less than 95 percent. As covered in the Load Profile section (last section), a soft parse ratio that is less than 80 percent indicates that SQL is not being reused and needs to be investigated.

   A Latch Hit % of less than 99 percent is usually a big problem. Finding the specific latch will lead you to solving this issue. I cover this in detail in the “Latch Free” section later in the chapter.

   A Flash Cache Hit % (only appears in the AWR Report on 12c) of less than 90 percent could be a problem if you have a large amount of Flash Cache (system dependent). If you are using Exadata, Chapter 11 provides some queries to help tune this problem if you’re not appropriately using your Flash Cache.

Shared Pool Statistics

The Shared Pool Statistics section that follows the Instance Efficiency section (both in the AWR Report and Statspack) shows the percentage of the shared pool in use and the percentage of SQL statements that have been executed multiple times (as desired). 

Top Wait Events

The Top Wait Events section of Statspack is probably the most revealing section in the entire report when you are trying to eliminate bottlenecks quickly on your system.