Oracle Parse to Execute Ratio

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 www.statspackanalyzer.com 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.

REFERENCES

Oracle Tips by Burleson Consulting