Parsing is the process of preparing a SQL statement for execution and involves checking the syntax, permissions and object validity as well as the creation of an execution plan, which describes the step by step process Oracle will undertake to resolve the SQL statement.

Parsing consumes quite a bit of CPU, and so Oracle goes to a fair bit of effort to avoid parsing unnecessarily .  

Oracle maintains a cache of SQL statements in the shared pool. If a matching SQL is found in the shared pool then most of the parse overhead can be avoided. A parse where a match is found in the shared pool is called a soft parse. If no matching SQL is found, a hard parse must be performed. Hard parsing not only consumes CPU but can also create contention when too many sessions try to cache SQLs in the shared pool simultaneously.

Bind variables

The usual cause of parse contention is poor use of bind variables. Bind variables allow us to parameterize the variable parts of an SQL statement so that we only need to parse it once, even if we execute it many times with different values.  In PL/SQL we don't have to worry much about bind variables except when we use dynamic SQL.  In other languages such as Java, its important to use them always.  Let's look at a PL/SQL example of bind variables.  

This PL/SQL routine concatenates the CUST_ID  into a SQL string that is then executed dynamically.  From Oracle's point of view, every new customer is a new SQL that needs to be parsed:

Now let's use bind variables.  The value of the CUST_ID variable is represented as :cust_id in the SQL string.  Regardless of which customer id is provided to the function, Oracle will always see the same SQL string and wil usuallyl find a match in the shared pool - avoiding a hard parse.

Monitoring parse overhead in Spotlight

Spotlight has always identified parsing related contention by monitoring the ratio of parse requests to executions, and monitoring for signs of parse related contention such as library cache latch waits.  However, we improved the parse diagnostics in Spotlight on Oracle version 6.1 - available as part of the Toad DBA suite - to take advantage of additional information available from Oracle 10.2 onwards.  The old diagnostics are still in place for those on older versions of Oracle.

Here's an example of a Spotlight home page for a database which is has a parse related bottleneck:



In previous releases of Spotlight - and still in versions of Oracle prior to 10g release 2 - the parse alarm was based on the ratio of parse requests to execute requests.  This was a good approach, but now that we can measure the time spent in parse operations it makes more sense to fire the alarm only if the amount of time spent parsing is excessive.  This means that high rates of trivial parse operations will no longer cause an alarm, and makes the alarm more sensitive to the effect of parsing on overall response time.  In the above example, we know that parsing is consuming 70% of database time and so is a significant bottleneck on throughput.

The time spent parsing is of course a primary concern, but high rates of parsing also tend to create serious contention issues.  Consequently, you'll usually see general latch alarms firing when you have a parse problem.  For example, here we see that time spent waiting on latches (which includes the mutex waits as well) is also very significant:



Prior to 10g Release 2, you would usually see library cache latch waits when high levels of hard parsing were encountered.  From 10.2 onwards,  Oracle has replaced these latches with light weight mutexes.   If you examine waits in Spotlight you will typically see the shared pool latch and the library cache mutex near the top of the wait list when hard parsing is an issue:
 


Clicking on the parse alarm provides some guidance on the cause and resolution of the problem.



High parse wait times are generally due to a failure to use bind variables.   Probably the most important thing we can do first is identify the SQLs and sessions that are not using bind variables effectively.  The parse activity drilldown - new in 6.1 - helps here:



The drilldown has four main sections.  I've marked the appropriate sections with a red number (like this: ) in the screenshot above:

1. The  Parse ratios chart shows the ratio of hard parses to total parses , the percentage of time taken up by parsing , the percentage of time spent waiting on latches and mutexes  and the miss rate in the library cache.  The "miss rate" is the percentage of SQLs that aren't found in the library cache when parsed.  It's about 100% for this database indicating that virtually no SQLs are found in the library cache when they are parsed.  Also note that the parse time and latch time can add up to more than 100%;  this is because some of the parse time may be spent waiting on latches. 
2. The Parse wait times chart shows the time spent by the database in total DB time, total parse time, hard parse, failed parse and latch.  In this case we can see that hard parse accounts for the vast majority of DB time and that latch waits constitute about half the parse time.  Clearly,  latch waits are significantly driving up the total parse time. 
3. The SQL grid (shown above) shows the SQL statements that have the most "similar" statements in the shared pool.  These are statements that are identical apart from literals.  In the example above there are about 4,000 statements similar to the SELECT from SH.SALES shown on the first row of the grid.  These statements require a hard parse in about 93% of  executions.  The next statement - an OracleOEM internal statement - is still a problem, but nowhere near as significant as the SH.SALES query. 
4. The second grid (not shown above) shows the sessions that are experiencing the greatest amount of parse overhead. 

Cursor_Sharing

Now that Spotlight has revealed the SQL that is most responsible for our high parse rates and consequent latch contention, what next?  Well,  the very best thing to do is to modify the application code to use bind variables.  However, that's not always possible and luckily Oracle has a workaround for when you cannot archive that - the CURSOR_SHARING parameter.

The CURSOR_SHARING parameter can be set so as to instruct Oracle to transparently replace literals with bind variables.  For instance, when CURSOR_SHARING=FORCE, Oracle will transparently replace this SQL statement:

UPDATE sh.customers
    SET cust_valid = 'Y'
    WHERE cust_id = 998

With this one:

UPDATE sh.customers
    SET cust_valid = :"SYS_B_0"
    WHERE cust_id = :"SYS_B_1"

And will substitute the appropriate values into the system generated bind variables as appropriate. 

CURSOR_SHARING can take the following values:

 Let's see what happens when we set CURSOR_SHARING.  Below we use Spotlight's parameter editor to change CURSOR_SHARING to SIMILAR:



The effect is dramatic:  when cursor sharing is set to SIMILAR, we get a sudden and huge reduction in parse time.  This is clearly visible from the Parse wait times chart in the  Parse activity drilldown:



The CURSOR_SHARING is one of the few Oracle parameters that can have a "silver bullet" effect - instantly and dramatically increasing throughput on a parse-constrained database.

Conclusion

Minimizing parsing is critically important both to reduce the CPU overhead of parsing, and to reduce the latch/mutex contention that usually accompanies excessive hard parse rates.  Using bind variables in application code is the best way to achieve this, though the CURSOR_SHARING parameter can also be very effective.  Spotlight 6.1 has new diagnostics to help you identify and correct bottlenecks caused by excessive parse rates.

Guy Harrison is a Director of Research and Development at Quest Software, and has over 20 years experience in application and database administration, performance tuning and software development. Guy is the author of Oracle Performance Survival Guide (Prentice Hall, 2009) and  MySQL Stored Procedure Programming (O’Reilly with Steven Feuerstein) as well as other books, articles and presentations on database technology.  Guy  is the architect of Quest's Spotlight® family of diagnostic products and has contributed to the development of other Quest products, such as Toad®.  Guy can be found on the internet at www.guyharrison.net, on email at  guy.harrison@quest.com and is @guyharrison on twitter.