 |
|
Extract and Explain the SQL
Statement
Oracle Tips by Burleson
|
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 an SQL Statement
To see the output of an explain plan, you must
first create a plan table in your schema. Oracle provides the syntax
to create a plan table in $ORACLE_HOME/ rdbms/admin/utlxplan.sql. The
listing here executes utlxplan.sql to create a plan table and
then creates a public synonym for the plan_table:
L 11-36
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 an SQL statement from the
stats$sql_summary table. We will show you the details for extracting
the SQL in the next section. Here is the statement that we suspect is
not optimized because it takes more than 11 minutes to execute. It is
not important that we understand the purpose of this SQL, only that we
note the basic structure of the statement.
L 11-37
SELECT
B.ISBN,B.BOOK_TITLE,B.EDITION_NBR,B.AUTHOR_NAME,B.THUMBNAIL_TYPE,
B.GLOSSARY_NBR,B.TABLE_CONTENTS_NBR,B.INDEX_NBR,B.PUBLIC_DOMAIN_FLAG,
B.NBR_OF_REVIEWS,B.TOTAL_REVIEW_RATING,S.START_VISUAL_PAGE_NBR,
S.END_VISUAL_PAGE_NBR,S.START_PAGE_SEQ_NBR,S.END_PAGE_SEQ_NBR,
TO_CHAR(S.START_DATE,'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(S.END_DATE,'DD-MON-YYYYHH24:MI:SS'),
S.LAST_VIEWED_PAGE_SEQ_NBR,P.VISUAL_PAGE_NBR,
TO_CHAR(S.TIME_LAST_VIEWED,'DD-MON-YYYYHH24:MI:SS'),
S.PROFESSOR_USER_UNIQUE_ID,S.RETURNED_FLAG,
S.TRIAL_SUBSC_FLAG
FROM
BOOK B,
SUBSCRIPTION S,
PAGE P
WHERE
(S.USER_UNIQUE_ID = :b1 AND S.ISBN = B.ISBN AND S.BOOK_UNIQUE_ID =
P.BOOK_UNIQUE_ID AND S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR )
ORDER BY B.BOOK_TITLE;
It is always a good idea to get a visual “pattern” for
the SQL statement before you get the execution plan. This statement
can be used to extrapolate the following:
L 11-38
select
-----
from
book,
subscription,
page
where
user = :var
and
subscription.isbn = book.isbn
and
subscription.book_id – page.book_id
and
subscription last_page_nbr_viewed = page page_nbr
Here we see a simple three-way table JOIN where the
result set is limited for a single user. Now that we understand the
basic structure of the query, we can get the execution plan for this
SQL statement by inserting the SQL into the following snippet:
L 11-39
delete from plan_table where statement_id =
'test1';
explain plan set statement_id = 'test1'
for
SELECT
B.ISBN,B.BOOK_TITLE,B.EDITION_NBR,B.AUTHOR_NAME,B.THUMBNAIL_TYPE,
B.GLOSSARY_NBR,B.TABLE_CONTENTS_NBR,B.INDEX_NBR,B.PUBLIC_DOMAIN_FLAG,
B.NBR_OF_REVIEWS,B.TOTAL_REVIEW_RATING,S.START_VISUAL_PAGE_NBR,
S.END_VISUAL_PAGE_NBR,S.START_PAGE_SEQ_NBR,S.END_PAGE_SEQ_NBR,
TO_CHAR(S.START_DATE,'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(S.END_DATE,'DD-MON-YYYYHH24:MI:SS'),
S.LAST_VIEWED_PAGE_SEQ_NBR,P.VISUAL_PAGE_NBR,
TO_CHAR(S.TIME_LAST_VIEWED,'DD-MON-YYYYH24:MI:SS'),
S.PROFESSOR_USER_UNIQUE_ID,S.RETURNED_FLAG,S.TRIAL_SUBSC_FLAG
FROM
BOOK B,
SUBSCRIPTION S,
PAGE P
WHERE
(S.USER_UNIQUE_ID = :b1 AND S.ISBN = B.ISBN AND S.BOOK_UNIQUE_ID =
P.BOOK_UNIQUE_ID AND S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR )
ORDER BY B.BOOK_TITLE;
When you execute this code, you instruct Oracle to
display the execution plan inside the plan table. To display the data
inside the plan table, you can use the following script.
plan.sql
L 11-40
SET PAGES 9999;
SELECT lpad(' ',2*(level-1))||operation operation,
options,
object_name,
position
FROM plan_table
START WITH id=0
AND
statement_id = 'test1'
CONNECT BY prior id = parent_id
AND
statement_id = 'test1';
Here is the output from plan.sql. This display is
known as the execution plan for the SQL statement. It describes in
detail all of the access steps that are used to retrieve the requested
rows.
L 11-41
OPTIONS OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
SORT
ORDER
BY 1
NESTED LOOPS
1
NESTED LOOPS
1
TABLE ACCESS
FULL
PAGE 1
TABLE ACCESS
BY INDEX ROWID SUBSCRIPTION
2
INDEX
RANGE SCAN SUBSC_ISBN_USER_IDX
1
TABLE ACCESS
BY INDEX ROWID
BOOK 2
INDEX
UNIQUE SCAN BOOK_ISBN
1
9 rows selected.
In this listing, we see the TABLE ACCESS FULL PAGE. This
is the dreaded full table scan that causes excessive overhead for
Oracle. The next question is whether this query needs all of the rows
in the page table. Let's look at the WHERE clause for the query:
L 11-42
WHERE
S.USER_UNIQUE_ID = :b1
AND
S.ISBN = B.ISBN
AND
S.BOOK_UNIQUE_ID = P.BOOK_UNIQUE_ID
AND
S.LAST_VIEWED_PAGE_SEQ_NBR = P.PAGE_SEQ_NBR
Here we see that the only WHERE condition that applies
to the page table is:
L 11-43
S.LAST_VIEWED_PAGE_SEQ_NBR =
P.PAGE_SEQ_NBR
It then follows that Oracle should be able to
retrieve the page rows by using an index on the page_seq_nbr column of
the page table and there is no need to perform a time-consuming full
table scan.
This statement was extracted from a database
where optimizer_mode=RULE, so the first thing we can try is to
analyze all of the tables and indexes in the query and reexplain the
query with a FIRST_ROWS hint:
L 11-44
Analyze table page estimate statistics sample
5000 rows.
Analyze table book estimate statistics sample 5000 rows.
Analyze table subscription estimate statistics sample 5000 rows.
Analyze index isbn_seq_idx compute statistics;
Analyze index subsc_pub_name_idx compute statistics;
Here is the original explain with the FIRST_ROWS hint:
L 11-45
delete from plan_table where statement_id =
'test1';
explain plan set statement_id = 'test1'
for
SELECT /*+ first_rows */
B.ISBN,B.BOOK_TITLE,B.EDITION_NBR,B.AUTHOR_NAME,B.THUMBNAIL_TYPE,
B.GLOSSARY_NBR,B.TABLE_CONTENTS_NBR,B.INDEX_NBR,B.PUBLIC_DOMAIN_FLAG,
B.NBR_OF_REVIEWS,B.TOTAL_REVIEW_RATING,S.START_VISUAL_PAGE_NBR,
S.END_VISUAL_PAGE_NBR,S.START_PAGE_SEQ_NBR,S.END_PAGE_SEQ_NBR,
TO_CHAR(S.START_DATE,'DD-MON-YYYY HH24:MI:SS'),TO_CHAR(S.END_DATE,
'DD-MON-YYYY
HH24:MI:SS'),S.LAST_VIEWED_PAGE_SEQ_NBR,P.VISUAL_PAGE_NBR,
TO_CHAR(S.TIME_LAST_VIEWED,'DD-MON-YYYY HH24:MI:SS'),
S.PROFESSOR_USER_UNIQUE_ID,S.RETURNED_FLAG,
S.TRIAL_SUBSC_FLAG FROM BOOK B,SUBSCRIPTION S,
PAGE P WHERE (S.USER_UNIQUE_ID = :b1 AND S.ISBN = B.ISBN AND
S.BOOK_UNIQUE_ID = P.BOOK_UNIQUE_ID AND S.LAST_VIEWED_PAGE_SEQ_NBR
=
P.PAGE_SEQ_NBR )ORDER BY B.BOOK_TITLE;
@plan
Now, when we run plan.sql, we see a totally
different execution plan without any full table scans:
L 11-46
OPERATION
----------------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT
STATEMENT
27
SORT
ORDER
BY
1
NESTED
LOOPS
1
NESTED
LOOPS
1
TABLE
ACCESS
BY INDEX ROWID
SUBSCRIPTION 1
BITMAP
CONVERSION
TO ROWIDS
1
BITMAP
INDEX
FULL SCAN SUBSC_PUB_NAME_IDX
1
TABLE
ACCESS
BY INDEX ROWID
BOOK 2
INDEX
UNIQUE SCAN BOOK_ISBN
1
TABLE ACCESS
BY INDEX ROWID
PAGE 2
INDEX
UNIQUE SCAN ISBN_SEQ_IDX
1
11 rows selected.
When we reexecute the SQL in SQL*Plus with set timing
on, the whole query executes in 18 seconds, for a savings of more
than 10 minutes! This is just a simple example of the dramatic
improvements you can make by tuning your SQL statements.
NOTE: There is a host of third-party tools in the
market that show the execution plan for SQL statements. The most
common way of determining the execution plan for an SQL statement is
by using Oracle's explain plan utility. By using explain plan, the
Oracle DBA can ask Oracle to parse the statement, and display the
execution class path without actually executing the SQL statement.
Now that we have covered the extraction and
explaining of the SQL statement, let's go into more detail on the
process of tuning each SQL statement.
Tune the SQL Statement
For those SQL statements that possess a
nonoptimal execution plan, the SQL will be tuned by one of the
following methods:
-
Adding SQL hints to modify the execution plan.
-
Adding B-tree indexes to remove full table
scans.
-
Adding bitmapped indexes to all low-cardinality
columns that are mentioned in the WHERE clause of the query.
-
Rewriting the SQL in PL/SQL. For certain
queries, this can result in more than a 20x 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 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 tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
