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.
Cursor creation can either occur implicitly or be explicitly declared.
What is parsing? Parsing is the process of:
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.
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.
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.
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.
Oracle can parallelize DML and some DDL in this step.
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.
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.
The last step of processing a SQL statement is to close the cursor.