Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Parse to Execute Ratio

Oracle Tips by Burleson Consulting


Somewhere in the DBA initiation ritual ?parse to execute ratio? became synonymous with ?hard parsing too much?. Oracle training, documentation, and many tuning blogs and sources don?t do much to expand or dispute this misconception about the parse to execute ratio.

Reducing hard parses is something you have to do, but simply reducing hard parses isn?t enough. If you do a hard parse and then execute once, you have a parse to execute ratio of 1:1. If you do a soft parse then execute you?re still at 1:1. A combination of hard parse reduction and cursor caching is required for a good parse to execute ratio.

Pay close attention to the client software being used against your database. Different application servers and programs will have different cursor caching mechanisms.

All Oracle SQL statements must be parsed the first time that they execute, and parsing involves a syntax check, a semantic check (against the dictionary), the creation of a decision tree, and the generation of the lowest cost execution plan.  Once the execution plan is created, it is stored in the library cache (part of the shared_pool_size) to facilitate re-execution.  There are two types of parses:

  • Hard parse - A new SQL statement must be parsed from scratch.  (See hard parse ratio, comparing hard parses to executes).  If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (see cursor_sharing=force).
     
  • Soft parse - A reentrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes).  The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that ?parses SQL once and executes many times? (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).   

In a real database, some SQL statements will be fully reentrant (execute to parse = 100%), while others must be re-parsed for every execution (execute to parse = 1%).  You can see this is the instance efficiency of any STATSPACK and AWR report:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:   97.97    In-memory Sort %:  100.00
            Library Hit   %:   98.30        Soft Parse %:   97.05
         Execute to Parse %:    0.91         Latch Hit %:   99.73
Parse CPU to Parse Elapsd %:   72.59     % Non-Parse CPU:   95.52

High parses suggests that your system has many incoming unique SQL statements, or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables), and consider setting cursor_sharing=force can cause dramatic performance improvements for systems with ad-hoc query tools such as Crystal Reports or Business Objects.

A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. 

Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated.  Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.  See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.

Note that the "soft parse ratio" and the "hard parse ratio" are totally independent metrics, and it's possible to see all combinations:

Soft Parse Ratio Hard parse Ratio
low low
high high
low high
high low

You can see these ratio's in any STATSPACK or AWR report, in the load profile and instance efficiency sections.  You can also paste-in your STATSPACK or AWR reports into our AWR Analyzer for a detailed analysis:

Load Profile
~~~~~~~~~~~~                          Per Second     Per Transaction
                                 ---------------     ---------------
                  Redo size:           26,032.63             2,148.01
              Logical reads:            4,943.63               407.91
              Block changes:              158.17                13.05
             Physical reads:               14.52                 1.20
            Physical writes:               26.77                 2.21
                User calls:                86.37                 7.13
                    Parses:              148.80                12.28
               Hard parses:                9.81                 0.81
                     Sorts:               134.58                11.10
                    Logons:                12.18                 1.01
                  Executes:               149.96                12.37
 
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:  100.00
            Buffer  Hit   %:   97.97    In-memory Sort %:  100.00
            Library Hit   %:   98.30        Soft Parse %:   97.05
         Execute to Parse %:    0.91         Latch Hit %:   99.73
Parse CPU to Parse Elapsd %:   72.59     % Non-Parse CPU:   95.52
 

If the execute to parse 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.  
 

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.