oracle hard parse soft parse

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.     Optimization - Oracle them creates an execution plan, based on your schema statistics (or maybe dynamic sampling in 10g).

5.     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 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.