BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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

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


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter