Oracle: SQL parsing

This is a non-trivial and costly (in terms of CPU cycles) task:

Syntactical check: does the statement adheres to the rules of SQL
If the SQL text references views, they need to be parsed also and merged into the statement
If the SQL text references synonyms, they need to be translated to actual objects
Semantic check: Are referred tables and columns available; are used data types ok etc.
Authorization check: Does the user have the necessary privileges to execute the statement (See also error message ORA-01031: insufficient privileges)
Is Row Level Security (RLS) involved?

Hard and soft parse

Because parsing a statement is costly, Oracle checks if Oracle already has parsed the SQL text (and stored the result in the library cahce) wheneven an SQL statement is requested to be executed.

If this is the case and the result of that previous parsing can be used for execution, then Oracle can use that result for the execution of the «new» SQL statement. This situation is referred to as soft parse and the reusable data structures are found in the shared SQL area of the library cache.

If Oracle does not find a suitable representation in the shared SQL area, the statement needs to be parsed (possibly again). This is referred to as hard parse or library cache miss.

Part of the hard parse is to determine if the available optimizer statistics are sufficient to generate an optimal execution plan. If this is not the case, Oracle determines dynamic statistics as part of the hard parse.

Oracle: SQL parsing

Hard and soft parse

See also