 |
|
Understanding SQL Execution
Oracle Tips by
Burleson
|
This chapter will focus on the internal
processes that occur when Oracle receives and executes a SQL
statement. We will examine the process of parsing the SQL statement,
generating the execution plan, and executing the SQL statement. The
four most important phases in SQL are parsing, binding, executing, and
fetching the result set. Let’s take a closer look at how an Oracle SQL
statement is transformed within the library cache.
1.
Parse the SQL During
the parse phase, the Oracle software searches for the SQL statement in
the shared pool, checks the syntax and semantics, and in some cases,
rewrites the query.
- Check security This phase validates
the security rules to ensure that the requestor is authorized to
view the data. This involves comparing the privileges of the user
against the names of the tables in the SQL.
- Check SQL syntax This phase
checks the syntax of the SQL statement to ensure that the
statement is properly formatted.
- Optional query rewrite In certain
cases Oracle will rewrite the query, changing table names or
replacing literals with host variables before passing the SQL to
the optimizer.
2. Execute the SQL This
phase involves the creation of the execution plan and the actual
fetching of the table data.
- Generate the execution plan The
generator accepts the parsed SQL and passes it to the SQL
optimizer. The optimizer examines the SQL and the data dictionary
and generates the internal execution plan. This plan is
machine-level code that makes the required API calls to retrieve
the data from disk. Oracle has numerous access methods to choose
from when deciding how to service a query, and the optimizer is
programmed to choose the access method that will either maximize
speed or minimize computing resources.
- Bind the execution plan The
bind process scans the SQL statement for bind variables and then
assigns a value to each variable.
- Execute the execution plan The
Oracle database applies the parse tree to the data buffers and
performs all necessary disk I/O.
- Fetch the result row This step
retrieves rows for a SELECT statement during the fetch phase. Each
fetch retrieves multiple rows, using an array fetch.
3. Present the result set
This phase performs all required sorting and translates and
reformats all column data.
- Sort the result set This phase
involves satisfying all order by or group conditions in the SQL
statement.
4. Translate column data
This phase involves reformatting and translating any column
data that has been transformed with a BIF. We will go into greater
detail on some of these steps as we move through this chapter, but our
focus will be on SQL parsing and the generation of the execution plan.
Let’s begin by examining the parsing phase.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
