 |
|
Oracle Concepts by Burleson
Consulting |
Understanding the Process of
Executing SQL Statements
In order to be
effective at tuning SQL statements, the database professional must
have an intimate understanding of how the SQL is transformed from the
original source code into an executable form. At a high level, SQL
processing is broken down into several steps:
-
Parsing the source
code to locate any syntax errors
-
Invoking the SQL
optimizer to derive an execution plan
-
Create and run an
executable, based on the execution plan
-
Fetching the
results set from the database and return it to the calling query.
Oracle has a special
RAM memory region within the System Global Area (SGA) to process SQL
statements. Most relational databases contain multiple areas of RAM
caches:
- RAM cache for
data blocks
- RAM cache for
SQL statements
- RAM for caching
the data dictionary
- RAM for caching
program execution code
Just as the data
caches within a relational database use a most frequently used
algorithm to cache data blocks that are frequently referenced, the
Oracle database provides a special cache called library cache
to store frequently executed SQL statements.
Every time an SQL
statements enters the Oracle system, Oracle begins by looking to see
if the SQL statement has already been processed. Oracle does this by
invoking a hashing algorithm, using the SQL statement as input, and
goes to the RAM address to see if the SQL statement has already been
parsed. If the statement has already been parsed, Oracle grabs the
executable and immediately re-executes the program, at the same time
incrementing the executions column in the v$sql view.
In order to make
effective use of the library cache, it is important for the Oracle
database professional to ensure that all of the SQL in the library
cache is fully reentrant. By fully reentrant, we mean that the SQL is
free of literal variables, such that the SQL can be re-executed using
different bind variables as input. For example, the following code
would not be considered reentrant because of the embedded literal.
select *
from customer where name = ‘BURLESON’;
To make this SQL
reentrant, we can replace the literals within the SQL statement with a
bind variable, thereby making the SQL fully re-entrant, and re-usable:
select * from customer where name = ‘:var1’;
To facilitate this
type of literal substitution, Oracle provides a tool called
cursor_sharing. Setting cursor_sharing=force automates the
process of substituting out literal values within SQL statements.
This is very important tool to those Oracle database administrators
whose vendor applications generate dynamic SQL that contains literal
values.
The best way to locate and tune Oracle SQL statements is by extracting
the SQL directly from Oracle's library cache. The v$sql and
v$sql_plan views can be used to allow the immediate extraction of
SQL statements from the library cache. Further, the v$sql_plan
view can tell us valuable information about the access patterns of the
SQL that are currently within the library cache.
We have to note that
the process speed of executing SQL statements can be very quick, and
tens of thousands of SQL statements can be executed every minute in a
busy Oracle database. Hence, we need some kind of a monitoring tool
that will tell us to salient characteristics of SQL activity over
fixed periods of time. We use the Oracle STATSPACK utility for this
purpose and part of your reading assignments will be to understand how
we can use STATSPACK to monitor the behavior all the library cache and
see the behavior of individual SQL statements over time.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
