Oracle - SPREPORT Efficiency Percentages

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

SPREPORT Efficiency Percentages

The percentages shown in Figure 7.4 are, in fact, the often maligned ratios. There has been much debate in the DBA community about ratios as a tool for measuring performance, but the ratios are included in the SPREPORT, so it is important that DBAs understand when and how to use them in ways to benefit themselves and other users.

The “Buffer Hit” and “Buffer Nowait” numbers are only valid for comparison and trend analysis for the same instance and across a sufficient time interval. A single SQL statement with a large number of logical or physical reads could severely influence these values and not have a significant impact on any given user’s operations. A “good” ratio in one database may be a “terrible” ratio in another, so it is not advisable to compare these numbers across different databases. A Buffer Cache Hit Ratio (BCHR) is only valid when comparing snapshots from two different periods of time for a given database.

The “Execute to Parse” ratio is an important metric. If the database server is parsing every statement that is executing, this ratio will be close to 1% while the best case scenario is 100% which would indicate an application that “parses once and executes many times”.

If users are complaining about application performance, the system capacity is stretched, or maybe there are concerns about the scalability of the database, the “Execute to Parse” ratio can be one valid indicator of a problem. If the ratio is too low, it is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.

When excessive parsing is part of a performance problem, hard parsing is usually the biggest contributor. Soft parsing is less of a performance issue, but it can still negatively impact database performance in a significant way. In applications where there are connections to the database that are opened and closed frequently and repeatedly, cursor sharing can suffer. Opening and closing database connections also carries some additional overhead.

In cases where this is an issue, regular monitoring of parsing is important. In addition, when the “Execute to Parse” ratio is lower than the baseline for a given instance, find out what specific SQL statements have a parse count that is equal to the execute count. These statements are contributing to ineffective cursor sharing. In either of these situations, the following script is useful because it calculates a related ratio using SQL statement. parse_v_execute.sql results are interpreted the opposite from the STATSPACK ratio, in that a higher a score or ratio indicates a lower effectiveness of that particular statement in terms of the goal of “parse once – execute many”.

* parse_v_execute.sql

-- *************************************************

-- This script is free for non-commercial purposes

-- with no warranties. Use at your own risk.

-- To license this script for a commercial purpose,

-- contact info@rampant.cc

-- *************************************************

column sql_text format a50

column parse_calls format 9,999,999

column executions format 9,999,999

column ratio format 999.99 heading "Parse-Execute|Ratio"

column loaded_versions format 999,999 heading "Loaded|Versions"