Question: What are the internal SQL execution
steps? How does Oracle translate a table name into a read request
from a physical datafile?
Between hitting "enter" and seeing your results,
there are many steps in processing a SQL statement. For complete
Vadim Tropashko's book "SQL
Design Patterns: The Expert Guide to SQL Programming"
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
A syntax check - Are all keywords
present "select . . . from", etc . .
semantic check against the dictionary - Are all table names spelled
creation of the cost-based decision tree of possible plans
generation of the lowest cost
plan - This is where the table--> tablespace --> datafile
Executing the query and
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
- 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
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.
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
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
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