In AWR, the ‘Instance Efficiency Percentages’ table comes under the ‘Report Summary’ section. Refer to the below figure:
The following metrics are available in the ‘Instance Efficiency Percentages’ table of an AWR report:
Buffer Nowait % (Buffer Nowait Ratio)
This metric provides the percentage of availability of the data buffers. This ratio gives an idea of the accessibility of buffer cache without any wait time.
This metric includes statistics for all types of buffers. If the percentage is low, check the ‘Buffer Wait Statistics’ section in the report to identify the root cause and get an idea of which type of block is being contended. Additional RAM might be required to resolve the issue.
Buffer Hit % (Buffer Hit Ratio)
It is also known as the buffer cache hit ratio. It measures the percentage of the number of hits when a block is found in the buffer cache vs the total number of hits made by an application.
Formula: Buffer Hit % = (No. of hits when a block is found in the buffer * 100) / Total no. of hits
Getting the block in the buffer is performance efficient than having it in the physical disk.
Figure 03 shows that the required blocks found in the buffer cache are 99.94, instead of incurring the overhead of a physical I/O (reading from disk).
Although known as one of the most important statistics to evaluate, this ratio can sometimes be misleading. A low buffer hit ratio does not necessarily mean the cache is too small; it may be that potentially valid full-table scans are artificially reducing what is otherwise a good ratio.
Similarly, a high buffer hit ratio (say, 99%) normally indicates that the cache is adequately sized, but this assumption may not always be valid.
For example, frequently executed SQL statements that repeatedly refer to a small number of buffers via indexed lookups can create a misleadingly high buffer-hit ratio.
When these buffers are read, they are placed at the most recently used (MRU) end of the buffer cache; iterative access to these buffers can artificially inflate the buffer hit ratio. This inflation makes tuning the buffer cache a challenge. Sometimes you can identify a too-small buffer cache by the appearance of the ‘Write complete waits event’, which indicates that hot blocks (that is, blocks that are still being modified) are ageing out of the cache while they are still needed; check the ‘Wait Events’ list for evidence of this event.
Library Hit % (Library Hit Ratio)
This metric is also known as the library-cache hit ratio. It gives the percentage of pin requests that result in pin hits. A pin hit occurs when the SQL or PL/SQL code to be executed is already in the library cache and it is valid to execute.
The following are the value ranges:
If the percentage of Library Hit = 100%: Best
Library Hit % < 100% & > 95 : Ok
Library Hit % < 95: Require Investigation
If the Library Hit % is low, it could be indicative of:
- A shared pool that is too small (SQL is prematurely pushed out of the shared pool), so increase the SHARED_POOL_SIZE init parameter
- Incorrect use of bind variables in the application
- SHARED_POOL_RESERVED_SIZE may be too small
- Inefficient sharing of SQL, PLSQL or JAVA code
- CURSOR_SHARING may need to be set to FORCE
Execute to Parse %
The ‘Execute to Parse’ ratio is a measure between the number of times a SQL is executed versus the number of times it is parsed.
Formula: Execute to Parse % = round(100*(1-(No. of Parse/No. of Execute),2)
| Case | No. of Parse | No. of Execute | Change in Execute to Parse |
|---|---|---|---|
| Case 1 | 1000 | 1000 | Result: 0% If the number of parse calls is near the number of execute calls, then the ratio drifts towards zero. |
| Case 2 | 1000 | 10000 | Result: 90% If the number of executed calls increases, then the ratio drifts towards 100%. That means parsing a statement once and executing it many times (Best Case). |
| Case 3 | 1000 | 900 | Result: -11.11% If the number of executed calls is less than parse calls, then the ratio drifts towards negative. That means parsing the same statement multiple times without executing them (Worst Case). |
In the ideal scenario, the ‘Execute to Parse’ ratio should be very high. The ratio will move higher as the number of executes goes up, while the number of parses either goes down or remains the same. The ratio will be close to zero if the number of executes and parses are almost equal. The ratio will be negative executes are lower but the parses are higher. Another cause for a negative ‘Execute to Parse’ ratio is if the shared pool is too small and queries are ageing out of the shared pool and need to be reparsed. This is another form of thrashing which also significantly degrades performance.
As per Figure 04, the average ‘Execute to Parse %’ is 61.82 showing that 3818 SQL statements are parsed and executed 10000 times.
Execute To Parse = 100 X (1-(3818/10000))
= 100 X (1-0.3818)
= 100 X 0.6162
Output: 61.62
The following is the reason for the low ‘Execute to Parse %’:
- Bad bind variable usage
- Insufficient memory
- It will also be co-indicated by a low percentage of memory for multiple SQL execution
Parse CPU to Parse Elapsd %
It measures the percentage of available CPU cycles for SQL parsing. If this is low, you may see ‘Latch free’ as one of your top wait events.
- Parse CPU to Parse Elapsed%: 71.45
- It is a percentage. 71.45 means 0.7145
- If 1 is divided by 0.7145 then 1/0.7145 = ~1.4
- This means 1.4 seconds (wall clock time) must be elapsed for every CPU second for parsing
- It represents resource contention while parsing
What may be the possible reasons for such a low ratio?
- There might be no “Prepared Statement caching” in the Java layer or in the JDBC connection pool
- There might be the case, before the execution of a query, the cursor was closed
- There might not be enough “session cached cursors” to hold the queries to reuse
- There might be a case where new queries were coming into play quite frequently
- There might be connection interruptions
Flash Cache Hit %
The low flash cache hit rates may have minimal impact on this particular database’s performance, hence this metric can be ignored.
Redo NoWait % (Redo NoWait Ratio)
It indicates the space availability for the redo logs. A value close to 100% shows that the instance didn’t have to wait to use the redo log which is a good sign. This is because of two reasons:
- The logs are not filling up very often
- The database switched to a new log quickly whenever the current log fills up
To tune the performance further, check:
- If the alert log shows that switching the log files frequently (that is, more than once every 15 minutes), then reduce the amount of switching by increasing the size of the online redo logs.
- If the log switches are not frequent, check the disks on which the redo logs reside to see why the switches are not happening quickly. If these disks are not overloaded, they may be slow, which means better to put the files on faster disks.
In-memory Sort %
This ratio gives the percentage of sorts that were performed in memory, rather than requiring a disk-sort segment to complete the sort.
In-memory Sort % = 100%: Good
In Memory Sort % < 90%: It indicates PGA_AGGREGATE_TARGET or SORT_AREA_SIZE issues
Soft Parse % (Soft Parse Ratio)
It measures the percentage of soft parses as opposed to hard parses.
Soft Parse: When a session attempts to execute a SQL statement and a usable version of the statement is already in the shared pool.
Hard Parse: When the current SQL statement is either not in the shared pool or not there in a shareable form then hard parse occurs.
The soft parse ratio should be 100% in the best case. A percentage greater than 95% is considered OK. If it is below 80% then investigation is required.
Latch Hit % (Latch Hit Ratio)
This is the ratio of the number of latches acquired to the total number of latches.
Best: Latch Hits % = 100%
Good: Latch Hits % => 99%
Need Investigation: Latch Hits % < 99%
% Non-Parse CPU
It measures the percentage of CPU utilization mostly for statement execution, but not for parsing.
If this value is near 100% means most of the CPU resources are used in operations other than parsing, which is good for database health.
You may be interested: