Hard vs Soft Parse in Oracle...

Was reading on asktom, came across this response from Tom, that seemed so direct and clear...

So, shamelessly copying.. :)



[From AskTom]
Parsing

This is the first step in the processing of any statement in Oracle. Parsing is the act of breaking the submitted statement down into its component parts ? determining what type of statement it is (query, DML, DDL) and performing various checks on it.

The parsing process performs two main functions:

o Syntax Check: is the statement a valid one? Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL.

o Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ?

For example if there are two tables T1 and T2 and both have a column X, the
query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on.

So, you can think of parsing as basically a two step process, that of a syntax check to
check the validity of the statement and that of a semantic check ? to ensure the
statement can execute properly. The difference between the two types of checks are hard for you to see ? Oracle does not come back and say "it failed the syntax check", rather it returns the statement with a error code and message. So for example, this statement
fails with a syntax error:

SQL> select from where 2;

select from where 2

*

ERROR at line 1:

ORA-00936: missing expression

While this statement failed with a semantic error ? if the table NOT_A_TABLE existed and we had permission to access it, this statement would have succeeded:

SQL> select * from not_a_table;

select * from not_a_table

*

ERROR at line 1:

ORA-00942: table or view does not exist

That is the only way to really tell the difference between a semantic and syntactic error ? if the statement COULD have executed given the proper objects and privileges, you had a semantic error, otherwise if the statement could not execute under any circumstances, you
have a syntax error. Regardless ? Oracle will not execute the statement for you!

The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session. If it has ? we may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation. If we can skip these next two steps in the process, we have done what is known as a Soft Parse ? a shorter process to getting our query going. If we cannot, if we must do all of the steps, we are performing what is known as a Hard Parse ? we must parse, optimize, generate the plan for the query. This distinction is very important. When developing our applications we want a very high percentage of our queries to be Soft Parsed ? to be able to skip the optimize/generate phases ? as they
are very CPU intensive as well as a point of contention (serialization). If we have to
Hard Parse a large percentage of our queries, our system will function slowly and in some cases ? not at all.

The way this sharing of SQL in Oracle is accomplished is via the shared pool, a piece of memory in the SGA maintained by Oracle. After Oracle parses the query and it passes the syntax and semantic checks ? it will look in the shared pool component of the SGA to see if that same exact query has already been processed by another session.

Since it has performed the semantic check it has already figured out:

o Exactly what tables are involved
o That we have access to the tables (the proper privileges are there)

And so on. Now, it can look at all of the queries in the shared pool that have already been parsed/optimized and generated to see if the work has already been done.

[Raghav]

As has been discussed here, the soft parsing of queries makes our application faster, since it doesn't have to do everything all over again (from parsing point of view).  Bind variables come to help in this case.

For example, a query like the following -

select a,b from tbl1 where c = 2333;

would be parsed and stored under a different identifier than

select a,b from tbl1 where c = 58763;

whereas, essentially they are same queries only, the literal value being different for the where condition.

In this case, the second query would go for hard parse, instead of our expectation of a soft parse. So, what could be done to tell oracle to think that these are actually same queries and there is no need to do the hard parse again. Well, you can do that by using a bind variable, in the sense, removing the only differentiating part of query by a runtime replacement.

Look at the following query -

select a,b from tbl1 where c = :bb;

now, this query can be used for both the examples above, at the runtime (after parsing), the variable :bb would be replaced by the respective literal value, and you have your answer.  However, this gains a lot of performance, since, the second run goes for a soft parse, instead of a hard parse, so, every subsequent run, after the first one, is re-using the parsing information collected the first time.

So, simple recommendation, use bind variables wherever you see a query being reused with different literal values, and use run time substitution of the literals.