| |
 |
|
Advanced SQL Execution Plan Analysis
Oracle Tips by Burleson
|
Oracle Corporate Technical Support provides a
great supplement to the standard explain plan utility in the form of a
SQL*Plus script called coe_xplain.sql. This script enhances the
SQL analysis by providing additional details about the database and
all tables and indexes in the query.
You can download this script from the Oracle
Web site at the following URL:
http://coe.us.oracle.com/~csierra/CoE_Scripts/coe_xplain.sql
The purpose of this script is to supplement the
standard explain plan output with additional information about the
status of the tables and indexes in your database. Let's take a look
at how this script is used:
1.
First, you download the latest version of the script from
Oracle's Web site.
2.
Next, you transfer the script to your server.
3.
To add your SQL statement, go to section III and paste the SQL
statement into the script, making sure that the SQL ends with a
semicolon.
4.
Finally, you enter SQL*Plus as the schema owner and execute
coe_xplain.
This script begins by asking you what details you would
like in addition to the standard explain plan. Following the data
collection, this script generates two files:
-
coe_statement.lst This is a
display of the SQL you inserted into section III of the script.
-
coe_explain.lst This file
contains the detailed execution plan for the SQL and lots of other
useful information.
Let's take a look at the output from this
script. When executed, this script prompts the user about the amount
of additional detail they need. When analyzing an SQL statement, it is
a good idea to request all of the ancillary information.
>sqlplus system/manager
SQL*Plus: Release 9.0.3.0.0 - Production on Wed Feb 7 06:38:22 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle Enterprise Edition Release 8.1.6.1.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.3.0.0 - 64bit Production
SQL> @coe_xplain
Unless otherwise instructed by Support, hit <Enter> for each
parameter
1. Include count(*) of Tables in SQL Statement?
<n/y> y
2. Include Table and Index Storage Parameters?
<n/y/d> y
3. Include all Table Columns? <n/y> y
4. Include all Column Histograms? <n/y> y
5. Include relevant INIT.ORA DB parameters? <n/y> y
Now that the script has gathered our
requirements, it displays the SQL statement and the execution plan for
the SQL:
Generating...
explain plan set statement_id = 'COE_XPLAIN' into COE_PLAN_TABLE_&&initials
for
/*===========================================================================
Generate Explain Plan for SQL statement below (ending with a
semicolon ';')
===========================================================================
*/
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 H24: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;
Explained.
/*==========================================================================
*/
SET echo off;
Ope Exec
Typ Order Explain Plan (coe_xplain.sql 8.1/11.5 20010115)
--- -----
----------------------------------------------------------------------------
--------------------------------------------------------------------------------------
ROW 11 SELECT STATEMENT Opt_Mode:HINT: FIRST_ROWS Total_Cost:28 (CBO
has been used)
SET 10 SORT (ORDER BY) (Cost=28 Card=4 Bytes=588)
ROW 9 . NESTED LOOPS (Cost=24 Card=4 Bytes=588)
ROW 6 .. NESTED LOOPS (Cost=20 Card=4 Bytes=552)
ROW 3 ... TABLE ACCESS (BY INDEX ROWID) OF 'DONALD.SUBSCRIPTION'
(Cost=15 Card=4 Bytes=196)
ROW 2 .... BITMAP CONVERSION (TO ROWIDS)
ROW 1 ....| BITMAP INDEX ***(FULL SCAN)*** OF
DONALD.SUBSC_PUB_NAME_IDX'
ROW 5 ... TABLE ACCESS (BY INDEX ROWID) OF 'DONALD.BOOK' (Cost=1
Card=22
Bytes=1958)
ROW 4 .... INDEX (UNIQUE SCAN) OF 'DONALD.BOOK_ISBN' (UNIQUE)
(Card=22)
ROW 8 .. TABLE ACCESS (BY INDEX ROWID) OF 'DONALD.PAGE'
(Cost=1 Card=13352 Bytes=120168)
ROW 7 ... INDEX (UNIQUE SCAN) OF 'DONALD.ISBN_SEQ_IDX' (UNIQUE)
(Card=13352)
Note: Card=Computed or Default Object Cardinality
Next, the report will be created and spooled to
coe_statement.lst and coe_explain.lst. The
coe_statement.lst shows the input SQL statement, but the valuable
information is in coe_explain.lst. From this listing, we get
far more detail than just the execution plan for the SQL. This report
contains all of the information for any Oracle object that
participates in the query.
First, we see additional information about the
internal structure of Oracle tables and indexes:
-
Section I: Table information This
section of the report shows all details for the table involved in
the query, including the number of rows in the table, the parallel
degree, a note if the table is partitioned, the chain count, and the
number of freelists for the table.
-
Section I.a: Table statistics Next we see details on each
table from the data dictionary, including the high-water mark, used
blocks, empty blocks, and free space per allocated block. This
information can be quite useful for detecting tables where the
high-water mark is far above the table's row space.
-
Section I.b: Table storage parameters This section displays
the PCTFREE, PCTUSED, and extent sizes for each table in the query.
-
Section
II: Index parameters This includes everything you would want to
know about the index, including the index type, index status
parallelism, partitioning, and freelists.
-
Section II.a: Index statistics In this section, the report
provides details on the cardinality of the index and the number of
distinct keys.
-
Section II.b: Index storage parameters This section shows all
of the indexes and the index column detail.
-
Section III: Table columns The next section displays all of the
available information about each table column that participates in
the query.
-
Section III.a: Index column statistics This section examines
all of the available statistics for each column in the query. This
includes the column size, cardinality, number of distinct values,
and the index selectivity.
-
Section III.b: Table column statistics This section of the
report shows the individual characteristics of each column that is
referenced in the SQL query. It shows all of the CBO statistics that
have been collected about each column.
-
Section IV: Histograms The histograms
section is useful in cases where you may have a table column with a
highly skewed distribution of values. As you may know, it is not a
good idea to analyze column histograms unless you identify columns
where the distribution of values is not uniform.
-
Section V: Oracle initialization parameters This
section dumps the init.ora parameters from the v$parameter
view. This completes the overall package, so the analyst will have
access to every possible factor that influences the execution plan
for the SQL statement.
This output listing should provide everything
that is needed to properly tune the SQL statement, and most
professional DBAs make frequent use of this script.
Table Histograms
As you may recall, the following command is
used to analyze table columns. The size parameter tells Oracle
how many distinct buckets to use for the column histograms.
L 11-54
analyze table
CUSTOMER
compute statistics for columns
CUSTOMER_REGION
size 10;
Once analyzed, you can issue SQL commands to
see the distribution of values within the table column:
L 11-55
column table_name format a20;
column column_name format a25;
select
table_name,
column_name,
endpoint_number,
endpoint_value
from
dba_histograms
where
table_name = 'SUBSCRIPTION'
order by
column_name
;
You can then see the distribution of values by querying
the dba_tab_histograms table. The endpoint_value shows the
column value and the endpoint_number shows the cumulative
number of rows.
L 11-56
SQL> @disp_hist
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
ENDPOINT_VALUE
-------------------- -------------------- ---------------
--------------
SUBSCRIPTION BOOK_NAME 0
3.3952E+35
SUBSCRIPTION BOOK_NAME 1
4.5399E+35
SUBSCRIPTION BOOK_UNIQUE_ID
0 1
SUBSCRIPTION BOOK_UNIQUE_ID
1 55
SUBSCRIPTION COUPON_DISCOUNT_AMOUNT
0 0
SUBSCRIPTION COUPON_DISCOUNT_AMOUNT
1 0
SUBSCRIPTION DATE_TIME_SUBSCRIBED 0
2451877.59
SUBSCRIPTION DATE_TIME_SUBSCRIBED 1
2451952.01
This histogram information should tell you when a data
column is skewed, that is, whenever an index column contains an uneven
distribution of values, you may want to consider analyzing the column
to tell the CBO about the skew.
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. |
|
