Oracle SQL execution steps

Question:  What are the internal SQL execution steps?  How does Oracle translate a table name into a read request from a physical datafile?

Answer:  Between hitting "enter" and seeing your results, there are many steps in processing a SQL statement.  For complete details, see Vadim Tropashko's  book "SQL Design Patterns: The Expert Guide to SQL Programming" and "Oracle Tuning: The Definitive Reference".

All Oracle SQL statements must be processed the first time that they execute (unless they are cached in the library cache). and SQL execution steps include:

1. A syntax check - Are all keywords present "select . . . from", etc . .

2. A semantic check against the dictionary - Are all table names spelled correctly, etc.

3. The creation of the cost-based decision tree of possible plans

4. The generation of the lowest cost execution plan

5. Binding the execution plan - This is where the table--> tablespace --> datafile translation occurs.

6. Executing the query and fetching the rows. 

Parse Phase - During the parse phase, Oracle opens the statement handle, checks whether the statement is OK (both syntactically and whether the involved objects exist and are accessible) and creates an execution plan for executing this statement. Parse call does not return an error if the statement is not syntactically correct.

Parsing can be a very expensive operation that takes a lot of resources to execute. Special problem are so called “hard parses” which happen when there is no previously parsed version of the SQL to reuse.

Once the execution plan is created, it is stored in the library cache (part of the shared_pool_size) to facilitate re-execution.  There are two types of parses:

• Hard parse - A new SQL statement must be parsed from scratch.  (See hard parse ratio, comparing hard parses to executes).  If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (see cursor_sharing=force).
   
• Soft parse - A reentrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes).  The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that “parses SQL once and executes many times” (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).   

Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.  See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.

• Bind Phase - Once the plan is syntactically created, Oracle gathers the parameters from the client program needed for the execution. It makes the addresses of the program variables “known” to Oracle.

• Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.

• Define Phase - Define is analogous to binds, only “output oriented”. The OCI define makes addresses of the output variables “known” to the Oracle process in order to make it possible to the fetch call to know where to put the output variables. The define call is frequently skipped and is largely considered unnecessary because of the automatic variables allocation in PHP.

• Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them accessible by the PHP interpreter. Once more, the define and fetch phases are relevant for queries only. The Oracle OCI interface and the PHP OCI8 module contain calls to facilitate each of those phases.

 

---