 |
|
Step 2: Extract and Explain the SQL Statement
Oracle Tips by Burleson Consulting |
As each SQL statement is identified, it will
be “explained” to determine its existing execution plan and then
tuned to see if the execution plan can be improved.
Explaining a SQL Statement
To see the output of an explain plan, you
must first create a plan table in your schema. While we will review
this in detail in Chapter 8, let’s take a quick tour. Oracle
provides the syntax to create a plan table in $ORACLE_HOME/rdbms/admin/utlxplan.sql.
The listing that follows executes utlxplan.sql to create a
plan table and then creates a public synonym for the plan table.
sql> @$ORACLE_HOME/rdbms/admin/utlxplan
Table created.
sql> create public synonym plan_table for sys.plan_table;
Synonym created.
Once the plan table is created, you are ready to
populate the plan table with the execution plan for SQL statements.
We start by lifting a SQL statement from the
stats$sql_summary table or from the library cache. I will
show you the details for extracting the SQL in the next section.
Here is a sample SQL statement that we have changed to add the
explain plan statement:
delete from
plan_table;
select ename from emp
where
reverse(ename) like 'GNI%';
explain plan
set statement_id = 'test3'
for
select ename from emp
where
reverse(ename) like 'GNI%';
@plan
Note that we use the plan.sql script
to display the execution plan.
plan.sql
rem
plan.sql - displays contents of the explain plan table
set pages 9999;
select lpad(' ',2*(level-1))||operation operation,
options,
object_name,
position,
other_tag
from plan_table
start with id=0
and
statement_id = 'test3'
connect by prior id = parent_id
and
statement_id = 'test3';
Now, let’s see what happens when we execute
this listing:
SQL> @exp
2 rows deleted.
ENAME
----------
KING
Explained.
1 explain plan
2 set statement_id = 'test3'
3 for
4 select ename from emp
5 where
6* reverse(ename) like 'GNI%'
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1
TABLE ACCESS
BY INDEX ROWID
EMP
1
INDEX
RANGE SCAN ENAME_REVERSE_IDX
1
Now that you see how the execution plan will
change, let’s turn our attention to the process of tuning a SQL
statement.
Step 3: Tune the SQL Statement
For those SQL statements that possess a
suboptimal execution plan, the SQL will be tuned by one of the
following methods:
-
Hints Adding SQL hints will
modify the execution plan.
-
Index Adding B-tree indexes can
remove full-table scans.
-
Re-write Rewriting the SQL can
change the execution plan, especially when changing the table
order in the from clause with the RBO.
-
Bitmap Indexes Adding bitmapped
indexes allows you to index all low-cardinality columns that are
mentioned in the WHERE clause of the query.
-
PL/SQL Rewriting the SQL in
PL/SQL can often improve performance. For certain queries, this
can result in more than a twenty-fold performance improvement. The
SQL would be replaced with a call to a PL/SQL package that
contained a stored procedure to perform the query.
By far the most common approach is to add
indexes and hints to the query. While we can instantly see the
execution plan change as we add indexes and change hints, it is not
always immediately evident which execution plan will result in the
best performance.
Hence, the Remote DBA will normally take the three
most promising execution plans and actually execute the statement in
SQL*Plus, noting the total elapsed time for the query by using the
SQL*Plus set timing on command.
The details of all of the SQL hints are way
beyond the scope of this book, but you can get details on all of the
hints in the forthcoming Oracle Press book Oracle
High-Performance SQL Tuning (October 2001), by Don Burleson.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
