Oracle Parse to Execute Ratio Oracle Consulting Tips by Burleson

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.