Oracle hard-parse vs. soft parse

Oracle hard-parse vs. soft parse

OracleSQLCache .

Oracle SQL is parsed before execution, and a hard parse includes these steps:

1.Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)

2.Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.

3.Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.

4.Query Transformation - If enabled (query_rewrite=true ), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.

5.Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).

6.Create executable - Oracle builds an executable file with native file calls to service the SQL query.

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 y our 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.