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.
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:
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.
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.
Very clearly and nice explained.
Worth always to read something brief and logical like this.
Respect and thanx.