 |
|
Understanding Oracle SQL Utilities
Oracle Tips by Burleson Consulting |
This chapter is dedicated to looking at the
tools that are used to extract the execution details for a SQL
statement. While almost a dozen third-party tools can be used to
deliver SQL execution information, Oracle provides a wealth of free
tools and utilities for this purpose. The topics in this chapter
will include:
-
The explain plan utility
-
Running a fast SQL trace
-
Running TKPROF to get a SQL trace report
-
Oracle’s Center of Expertise (COE) SQL
execution report
-
Reporting on all SQL in the library cache
These reports provide a common basis for all
SQL tuning activities, since they are all free of charge and readily
available. Let’s begin with the most basic tool of SQL tuning, the
explain plan utility.
Explaining a SQL Statement
As I briefly mentioned in Chapter 7, to see
the explain plan for a SQL statement, 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
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.
The syntax for the explain plan utility is as follows:
explain
plan
set statement_id = ‘<your ID>’
Into table <table name>
for
<SQL statement>
;
To run an explain plan, we start by lifting a
SQL statement from the stats$sql_summary table. I 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.
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. The preceding
statement can be simplified into the following structure:
select
stuff
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 you 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:
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
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.
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
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 to access
all of the rows in the page table. To find out, let’s look at
the where clause for the query:
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:
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:
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:
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:
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
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.
Also note the use of the bitmap index in this execution plan.
Note: A host of third-party tools in the market
show the execution plan for SQL statements. The most common way of
determining the execution plan for a SQL statement is by using
Oracle’s explain plan utility. By using explain plan, the Oracle Remote 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
getting execution details for SQL statements.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
