Oracle's Center of Expertise (COE) SQL
Oracle Tips by Burleson Consulting
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:
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:
First, you download the latest version of the script from
Oracle’s Web site.
Next, you transfer the script to your server.
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
Finally, you enter SQL*Plus as the schema owner and execute
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
contain the detailed execution plan for the SQL and lots of other
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 a SQL statement, it
is a good idea to request all of the ancillary information.
SQL*Plus: Release 184.108.40.206.0 - Production on Wed Feb 7 06:38:22 2001
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Oracle9i Enterprise Edition Release 220.127.116.11.0 - 64bit Production
With the Partitioning option
JServer Release 18.104.22.168.0 - 64bit Production
Unless otherwise instructed by Support, hit <Enter> for each
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:
explain plan set statement_id = 'COE_XPLAIN' into COE_PLAN_TABLE_&&initials
Generate Explain Plan for SQL statement below (ending with a semicolon
SELECT /*+ first_rows */
HH24:MI:SS'), S.LAST_VIEWED_PAGE_SEQ_NBR, P.VISUAL_PAGE_NBR,
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;
Typ Order Explain Plan (coe_xplain.sql 8.1/11.5 20010115)
ROW 12 SELECT STATEMENT Opt_Mode:RULE (RBO has
SET 11 SORT (ORDER BY)
ROW 10 . NESTED LOOPS (OUTER)
ROW 7 .. NESTED LOOPS
ROW 4 ... NESTED LOOPS
ROW 2 .... TABLE ACCESS (BY INDEX ROWID) OF
ROW 1 ....| INDEX (UNIQUE SCAN) OF
ROW 3 .... INDEX (RANGE SCAN) OF
ROW 6 ... TABLE ACCESS (BY INDEX ROWID) OF 'MRP.MRP_FORECAST_DATES'
ROW 5 .... INDEX (RANGE SCAN) OF 'MRP.MRP_FORECAST_DATES_N1'
ROW 9 .. TABLE ACCESS (BY INDEX ROWID) OF 'MRP.MRP_FORECAST_UPDATES'
ROW 8 ... INDEX (RANGE SCAN) OF
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
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 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
abut 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
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 Remote DBAs make frequent use of this script.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.