Oracle SQL is parsed before execution, and a hard
parse includes these steps:
Oracle gives us the
shared_pool_size parm to cache SQL so that we don't have to
parse, over-and-over again. However, SQL can age-out if
the shared_pool_size is too small or if it is cluttered with
non-reusable SQL (i.e. SQL that has literals "where name =
"fred") in the source.
What the difference between a hard
parse and a soft parse in Oracle? Just the first step,
step 1 as shown in red, above. In other words, a soft
parse does not require a shared pool reload (and the associated
RAM memory allocation).
A
general high "parse call" (> 10/sec.) indicates that your system
has many incoming unique SQL statements, or that your SQL is not
reentrant (i.e. not using bind variables).
A hard parse is when
your
SQL must be re-loaded into the shared pool.
A hard parse is worse than a soft parse because of the overhead
involved in shared pool RAM allocation and memory management.
Once loaded, the SQL must then be completely re-checked for
syntax & semantics and an executable generated.
Excessive hard parsing can occur when your shared_pool_size
is too small (and reentrant SQL is paged out), or when you have
non-reusable SQL statements without host variables.
See the cursor_sharing
parameter for a easy way to make SQL reentrant and remember that
you should always use host variables in you SQL so that they can
be reentrant.
