Understanding Oracle SQL Hard and Soft Parsing: Full Process Explained

1. Syntax Check

Oracle first verifies the SQL statement for syntactic correctness, ensuring keywords and structure are valid; any syntax error (e.g., a misspelled FROM clause) aborts the parsing process.

2. Semantic Check

The semantic phase validates objects, privileges, and name resolution in three steps:

Confirm that all referenced tables, columns, and other objects exist in the data dictionary.

Resolve synonyms or aliases to their actual object names; failure to resolve stops parsing.

Check that the executing user has the necessary privileges on the resolved objects.

3. Parse Phase

This stage determines whether the SQL can reuse an existing cursor or needs a new one, performing five main checks and generating an execution plan:

Compute a hash of the SQL text and locate the corresponding object handle in the library cache.

Verify that the object in the library cache belongs to the same owner as the current statement.

Compare bind variable names; mismatched names (e.g., :SYS_B_0 vs :b1) prevent sharing.

Compare bind variable data types and lengths; differences in type (CHAR vs NUMBER) or length also break sharing.

Check language and sort settings; a session that changes NLS parameters cannot share the cursor.

Generate an execution plan using the Rule-Based Optimizer (RBO) or Cost-Based Optimizer (CBO), which is the most CPU‑intensive step.

4. Execution Phase

After passing the previous checks, the statement moves to execution, which consists of three steps:

Load the execution plan, SQL text, and related metadata into the library cache heaps.

Execute the statement via a private cursor.

If the statement is a SELECT, fetch rows using the User Global Area (UGA).

When all four stages are performed, Oracle performs a hard parse , typically on the first execution of a statement. Subsequent executions may skip some steps, resulting in a soft parse . Soft parsing occurs when the cursor is shared or when only minor differences exist; the number of soft parses is controlled by parameters such as cursor_sharing and session_cached_cursors.

Common reasons why a SQL statement cannot be shared include:

SQL_TYPE_MISMATCH – data type mismatch.

AUTH_CHECK_MISMATCH – authorization mismatch.

LANGUAGE_MISMATCH – language environment mismatch.

USER_BIND_PEEK_MISMATCH – different bind values under cursor_sharing=SAME generate separate cursors.

UNBOUND_CURSOR – missing bind variables.

Understanding the full parsing process helps DBAs diagnose performance issues related to the shared pool and CPU consumption.