Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

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

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

-- Copyright © 2003 by Rampant TechPress

-- 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"

set pages 1000 

select

   sql_text,

   parse_calls,

   executions,

   parse_calls/executions "Ratio"

from

   v$sql

where:

See Code Depot


The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Iinterface Analysis 

ISBN 0-9745993-7-9  

Stephen Andert 

http://www.rampant-books.com/book_2004_2_wait_tuning.htm

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.