|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
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:
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
Versions of statements
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:
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.