How an SQL Statement is Processed in Oracle

In Oracle, processing a SQL statement follows several steps before returning the final output to the user. Each step ensures the statement is executed efficiently and securely.

1. Syntax Check

The first step in processing a SQL statement is a syntax check. Oracle verifies whether the SQL statement is syntactically correct. If there’s an error in syntax, Oracle immediately returns an error and stops further processing.

2. Semantic Check

After passing the syntax check, Oracle performs a semantic check to validate the elements referenced by the SQL statement:

  • Oracle confirms that the table and columns referenced in the statement exist within the database.
  • This information is fetched from the data dictionary cache, ensuring that the statement references valid schema objects.

3. Privilege Check

Oracle then checks if the user has the appropriate privileges to execute the SQL statement. The data dictionary cache is again referenced to verify these privileges. If the user lacks the required privileges, Oracle stops execution and returns an error. Only if the syntax, semantic, and privilege checks pass does Oracle proceed to the next stage.

4. Private SQL Area Allocation

At this stage, Oracle allocates a private SQL area in the user’s Program Global Area (PGA) to manage the statement’s memory requirements. This area stores query results temporarily. Once the user disconnects, the allocated memory returns to the Shared Global Area (SGA).

5. Query Execution Process

The query execution process begins with Oracle attempting to locate an execution plan. An execution plan is a series of steps defining how Oracle retrieves and processes data. If the query has been executed before, Oracle will find a pre-existing execution plan in the shared SQL area of the shared pool.

  • Soft Parse (Library Cache Hit): If an execution plan for the query already exists in the shared pool, Oracle performs a “soft parse.” The query can proceed without the costly steps of re-optimization.
  • Hard Parse (Library Cache Miss): If no plan exists in the shared pool, Oracle performs a “hard parse,” which includes additional steps to generate a new execution plan.

A hard parse involves three primary steps:

1) Allocating Shared SQL Area:
Oracle first allocates space in the shared SQL area. If the shared area is full, Oracle might remove aged plans to free up memory.

2) Optimization:
The optimizer, a crucial component in Oracle, generates the most efficient execution plan. It evaluates several possible execution paths and chooses the best one, typically within a second. The optimization level can be adjusted to allow more time for complex queries to generate optimal plans.

3) Row Source Generation:
After creating an execution plan, Oracle generates row sources, defining each step of execution. For instance, it may fetch data from the “customers” table, then join with the “products” table, followed by sorting. Each step defines a specific way to retrieve and manipulate data.

6. Execution and Result Return

Once the row source is created, Oracle executes the query following the defined steps and returns the result to the user. If the same query is run again, Oracle bypasses parsing by directly referencing the execution plan stored in the library cache using the stored rowid, speeding up execution by avoiding costly parsing.

Oracle retains addresses for up to 50 queries by default in the library cache, but this limit can be adjusted. Parsing, especially a hard parse, is a resource-intensive process and, when possible, is best avoided through query caching techniques.

Harshad Vengurlekar

Experienced OCM-certified Oracle Database Administrator with over 18 years of expertise in designing, implementing, and managing complex database solutions. My expertise spans performance optimization, security, and high-stakes solution implementation. Adept at managing complex environments with precision.

No Comments

    Leave a Message

    Your email address will not be published. All fields are mandatory. **