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 
What New in SQL Server 2008
SQL Server 2008 capabilities deliver on the four key areas of the data platform vision.
- Mission-Critical Platform “ SQL Server 2008 enables IT groups to be more productive by providing a more secure, scalable, and manageable platform. It includes a new policy-based management framework that shifts from managing by scripts to managing by rules. SQL Server 2008 also protects valuable information in existing applications and disconnected devices. In addition, SQL Server 2008 delivers predictable query performance with an optimized platform.
- Dynamic Development “ SQL Server 2008 along with the .NET Framework enables developers to build the next generation of applications. Developers are more productive because they work with business entities instead of tables and columns. They can build applications that enable users to take their data with them and synchronize their data with back-end servers.
- Beyond Relational Data “ SQL Server 2008 enables developers to consume any type of data, from XML to documents, and build applications that incorporate location awareness.
- Pervasive Business Insight “ SQL Server 2008 provides a scalable infrastructure that can manage reports and analysis of any size or complexity while at the same time empowering users because of its close integration with the Microsoft Office System. This enables IT to drive business intelligence throughout the organization. SQL Server 2008 makes great strides in data warehousing, enabling users to consolidate data marts in an enterprise data warehouse.
The last step of processing a SQL statement is to close the cursor.
