Which two options are part of a Soft Parse operation?

What is a Soft Parse?

A Soft Parse occurs when a SQL statement is already present in the shared SQL area (Shared Pool) of the database. Instead of recreating the execution plan, Oracle reuses the existing plan, making the process much faster and more efficient. This is an essential optimization step in Oracle Database to reduce overhead and improve performance.

Steps Involved in a Soft Parse

Syntax Check (Step A)

This is the first step of the parsing process.

Purpose: Ensures the SQL statement conforms to proper syntax rules defined by the SQL language.

Example:

SELECT FROM employees;

This query will fail at the Syntax Check step because it doesn’t specify any columns to retrieve. Oracle ensures that such malformed queries are identified early.

Semantic Check (Step E)

The Semantic Check happens after the Syntax Check if the statement passes the syntax validation.

Purpose:

Verify Object Existence: Check if all referenced tables, columns, and other database objects exist. Example:

SELECT salary FROM non_existent_table;

This query will fail because the table non_existent_table does not exist.

User Privileges: Ensure the user has sufficient permissions to access the objects. Example:

SELECT * FROM employees;

If the user doesn’t have SELECT privileges on the employees table, the query will fail.

Validate Data Types: Ensure that columns used in expressions or comparisons are compatible in terms of data types. Example:

SELECT * FROM employees WHERE hire_date = '01-01-2023';

If hire_date is stored as a DATE type, and the literal is not implicitly convertible, this will fail.

Steps Skipped in a Soft Parse

SQL Row Source Generation (Option B):

This step involves breaking the query into operations (row sources) to fetch data. It is part of execution plan generation, which happens only during a Hard Parse.

SQL Optimization (Option C):

The SQL Optimizer calculates the most efficient execution plan during a hard parse. In a soft parse, the existing plan is reused, so this step is skipped.

Shared Pool Memory Allocation (Option D):

A Hard Parse allocates memory in the shared pool for a new SQL statement. In a soft parse, Oracle reuses the existing shared memory, avoiding additional allocation.

Why Are Syntax Check and Semantic Check the Correct Steps?

These steps are mandatory validations for all SQL statements, even during a soft parse. Without them, Oracle would risk executing invalid or unauthorized SQL statements.

By reusing the execution plan but performing these lightweight checks, Oracle ensures both efficiency and correctness.

References to Oracle Database 19c: Performance Management and Tuning

Oracle Documentation:

Oracle Database 19c Concepts: SQL Parsing and Execution

Oracle Database Performance Tuning Guide: Understanding Hard Parses and Soft Parses

Key Features Discussed in the Guide:

Shared Pool and Library Cache: The role of the shared SQL area in reducing parsing overhead.

SQL Execution Workflow: Detailed explanation of syntax and semantic checks.

SQL Optimizer: The differences between hard and soft parsing in relation to the optimizer.

Tools for Analysis:

AWR Reports: Monitor the number of hard vs. soft parses for query performance.

V$SQL: View cached SQL statements and their parsing statistics.