How The Oracle Database Processes SQL Statements

The Oracle Database Concepts is a great resource not only if you are just getting started with Oracle, but also if you are an experienced Oracle professional and looking to review the basic concepts that are often forgotten or hidden behind more complex topics. The foundation of complexity is simplicity.

While researching a topic related to FIPS (that I’ll blog about soon), I came across this simple but fundamental concept in non other than the Oracle Database Concepts manual: SQL statement execution. The following is a summary of how Oracle processes SQL statements. First the flowchart followed by a brief explanation of each step.

Step 1: Create a Cursor

Cursor creation can either occur implicitly or be explicitly declared.

Step 2: Parse the Statement

What is parsing? Parsing is the process of:

• Translating a SQL statement, verifying it to be a valid statement
• Performing data dictionary lookups to check table and column definitions
• Acquiring parse locks on required objects so that their definitions do not change during the statement’s parsing
• Checking privileges to access referenced schema objects
• Determining the optimal execution plan for the statement
• Loading it into a shared SQL area
• Routing all or part of distributed statements to remote nodes that contain referenced data

If a similar SQL statement exists in the shared pool, Oracle skips the parsing step. A SQL statement is parsed once no matter how many times the statement is run. As you can see, parsing does many things and consumes time and resources. You should always aim at minimizing parsing when writing SQL.

Step 3: Describe Results of a Query

This step is performed in the case of a query (SELECT) processing. The describe step determines the characteristics (datatypes, lengths, and names) of a query’s result.

Step 4: Define Output of a Query

This step is performed in the case of a query (SELECT) processing. In this step, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary.

Step 5: Bind Any Variables

At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to run the statement. Oracle needs values for any variables listed in the statement. The process of obtaining these values is called binding variables.

Step 6: Parallelize the Statement

Oracle can parallelize DML and some DDL in this step.

Step 7: Run the Statement

At this point, Oracle has all necessary information and resources, so the statement is run. If it is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database until the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction.

Step 8: Fetch Rows of a Query

This step is performed in the case of a query (SELECT) processing. The rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result until the last row has been fetched.

Step 9: Close the Cursor

The last step of processing a SQL statement is to close the cursor.

Related Oracle documentation

Related articles:

• 5 Recommendations About Cursor FOR Loops in Oracle PL/SQL
• Keep first, Keep last
• Oracle SQL tip

Filed in Oracle, Tips on 28 Jun 06 | Tags: concepts, parsing, sql

---