Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  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.lstThis is a display of the SQL you inserted into section III of the script.

  • coe_explain.lstThis 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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter