How Oracle Database Processes SQL Statements
Back
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.
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:
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 calledbinding 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.
Back
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
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 calledbinding 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.
Back
