Remote DBA Plans
Remote DBA Service

Oracle's Center of Expertise (COE) SQL Analysis Report

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:

http://www.osborne.com/oracle/code_archive.html

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 contain 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 a 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:
Oracle9i 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.

Ope Exec


Typ Order Explain Plan (coe_xplain.sql 8.1/11.5 20010115)

--- ----- ----------------------------------------------------------------------
----------------------------------------------------------------------
ROW    12 SELECT STATEMENT Opt_Mode:RULE (RBO has been used)

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 'MRP.MRP_FORECAST_DESIGNATORS'

ROW     1 ....| INDEX (UNIQUE SCAN) OF 'MRP.MRP_FORECAST_DESIGNATORS_U1' (UNIQUE
)
ROW     3 .... INDEX (RANGE SCAN) OF 'MRP.MRP_FORECAST_ITEMS_U1' (UNIQUE)

ROW     6 ... TABLE ACCESS (BY INDEX ROWID) OF 'MRP.MRP_FORECAST_DATES'

ROW     5 .... INDEX (RANGE SCAN) OF 'MRP.MRP_FORECAST_DATES_N1' (NON-UNIQUE)

ROW     9 .. TABLE ACCESS (BY INDEX ROWID) OF 'MRP.MRP_FORECAST_UPDATES'

ROW     8 ... INDEX (RANGE SCAN) OF 'MRP.MRP_FORECAST_UPDATES_N2' (NON-UNIQUE)


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 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 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 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.