Hard parse vs. soft parse

If a session executes an SQL statement that does not exist in the shared pool, then Oracle has to do a hard parse.

Oracle must then:

• Allocate memory for the statement from the shared pool.
• Check the statement syntactically
• Check if the user trying to execute the statement has the necessary rights to execute it

A hard parse is expensive in both terms of CPU used and number of shared pool latch and library cache latch it needs to acquire and release. It should be avoided whenever possible.

If event 10053 is set, this event will be triggered in a hard parse (not a soft parse).

If a session executes an SQL statement that exists in the shared pool and there is a version of the statement that can be used, then this is refered to as a soft parse.

A statement is identical to another statement, if there is absolutely no difference between the letters. For example select x from y and SELECT X FROM Y are not identical, although they clearly do the same thing.

Even if two statements are identical, this doesn't mean they are shareable. In order for two identical statements to be shareable, the following must be true

• Object names must resolve the same actual objects
• The optimizer goal is the same
• Types and length of bind variables is similar
• The NLS environment is the same

If two statements are identical but not shareable, they have different versions of the statement. High version counts for sql statements should be avoided. The number of versions for a statement can be found in v$sqlarea:

select sql_text from v$sqlarea where version_count > 1;

The parameter cursor_sharing affects the behaviour of un-identical sql statements. As of 9i, valid values for this parameter are: exact, force and similar.

If the value is exact, two statements are identical if they are textually identical. This is as is described above

If the value is force the requirment for two statements to be identical is relaxed: statements that differ in some literals, but are otherwise identical, are regarded as identical statements (and can share a cursor. So, the following two statements might be considered identical: select x from f where a='foo' and select x from f where a='bar'.

Sometimes, this is too much of a relaxation because a user might actually want two statements to be different (as they might have a different execution plan). This can be avoided with similar: Statements that differ in literals are consider identical unless the execution plan is different for the statements..

Both, soft parse and hard parse are counted as parse in tkprof. More specifically, the parse count is incremented when the statement is hashed.

Thanks to Alan White who pointed out an error on this page.