Overview

Bind variables are extremel useful in SQL, because they prevent hard parses. In a nutshell, statment is given as a tamplate with place holders. When executing the cursor, these place holders are filled with bind varialbes. Because of that, when a cursor is parsed, it is parsed with a place holders and not with the actual varialbes. Because of that, Oracle (and not only Oracle) Will generate an SQL plan which can be re-used with different search setting.

Of course, if you don't use “where” clause, bind variables are kinda useless and Oracle will do the most expensive execution anyway. Some databases like MySQL, have very little of a cursor sharing and most of their parsing is hard parse.

For Oracle there are 4 types of parsing:

Hard Parse - The most expensive one

Soft Parse - Still Expensive

Soft-Soft Parse - Less Expensive

No-Parse - The best you can have

It is obvious to notice, that we want always to have no-parse. Unfortunetely that is rarely the case

Parsing

Let's get through all of them:

Hard Parse

This one is the easiest one to understand. It’s also the most expensive one. Whenever the fist time oracle run a SQL, there is no plan about this SQL in the memory. Oracle needs to create a plan(optimizing) and save it in library cache. That’s the hard parse.

Here is a good picture to distinguish bitween the Hard parse and the Softparse:

That shared area check, can be seen below. If it succeed, we will have a soft parse, if not, we will have a hard parse and we will continue with the most expensive parts:

Optimization - Generate Multiply Execution Plans

Row Source Generation - Generate a query plan.

Soft Parse

This one is the second easiest to understand. It’s also the second expensive one. When you submit a SQL, oracle searches the library cache, and it found a plan matches your request exactly. Oracle didn’t need to create a plan but need to do the search work. Some notes from the book about the work oracle need to do:

So if the 2nd session, finds that Plan in the shared area, it will re-use it.

Soft soft parse

This one is still called soft parse in Oracle’s document. I don’t think Oracle give it a proper name to make it easy to understand. Sometimes DBA call it soft-soft parse. When you run a SQL a few times in the same session (with session_cache_cursors enabled), Oracle will create a short cut in your session memory, so when you run it next time, oracle don’t need to do the search work. It knows where it’s already. It’s cheaper in cost than soft parse.

Here is an example in the book(although it’s using pl/sql cursor cache, the effect is the same):

Soft Soft Parse

No Parse

The most significant difference of this one from the previous three is it will not increase the parse count. The reason is obviously “No parse at all ” – execute the code by using given bind variables. It the best if a same SQL run multiple times. Unfortunetely that can only be done within the same program. It is VERY BAD idea to re-use an open cursor as it will violate couple security checks by Oracle.

No Parse