coe_xplain.sql

oracle常用管理脚本

/*$Header: coe_xplain.sql 8.1/11.5     2000/01/15 16:00:00     csierra coe $ */
SET term off;
SET ver off;
/*=============================================================================
 OVERVIEW:
    Generates enhanced Explain Plan for one SQL statement.  Includes relevant
    statistics: table(s), index(es) and index(es)_column(s).  It optionally
    displays histograms, storage parameters and database parameters.

 INSTRUCTIONS:
    Insert your SQL statement under the 'III. Generate Explain Plan' section. 
    Finish your SQL statement with a semicolon ';'. Save and run this script.  

 NOTES:
 1. Download newer version from (case sensitive): 
    http://coe.us.oracle.com/~csierra/CoE_Scripts/coe_xplain.sql
 2. The Explain Plan is spooled to file COE_XPLAIN.LST contains.  The original
    SQL statement is spooled to COE_STATEMENT.LST
 3. Open the spooled files using WordPad, change the font to Courier New, style
    regular and size 8.  Set up the page to Lanscape with all 4 margins 0.5 in.
 4. This script has been tested on 8.0.5 and 8.1.6.  It is ready to run on any
    8.1.6+ server. If you need to run it on 8.0 remove all lines having the
    comment '-- 8.1' on them.
 5. For a list of all install init.ora parameters and values on 11i run script
    $FND_TOP/sql/AFCHKCBO.sql.  This script includes the display of such
    parameters for Apps 11i.
 6. This script is capable of tracing the CBO.  Find the two lines referencing
    event 10053 (do find on '10053) and remove comment (--).
    Read Note:72346.1 for interpretation.
 7. Table COE_HISTOGRAMS has been created to workaround Bug 894549 (poor
    performance on TAB_HISTOGRAMS views)

 PARAMETERS:
 1. Include count(*) of Tables in SQL Statement? <Y/N> 
        N - Does not display count(*) information for all Tables (DEFAULT)
        Y - Creates nd runs a SQL script to perform a count(*) on all Tables
            referenced in the Explain Plan.  It may be slow but it is highly
            recommended for RULE based optimizer 
 2. Include Table and Index Storage Parameters? <N/Y> 
        Y - Displays both Table and Index Storage Parameters from ALL_TABLES
            and ALL_INDEXES.
        D - Same as Y.  It also displays, counts and summarizes Extents for
            each Object referenced in Explain Plan.  This step may be slow.
            Request this Detailed option only when really needed.
        N - Skips the extract and display of Storage Parameters (DEFAULT)
 3. Include all Table Columns? <N/Y> 
        Y - Extracts and displays all Columns for all Tables referenced in
            Explain Plan
        N - Displays statistics only for those Columns included in at least
            one Index of a Table referenced in the Explain Plan (DEFAULT)
 4. Include all Column Histograms? <N/Y>
        Y - Display all Histogram information from ALL_TAB_HISTOGRAMS for
            all Columns in all Tables referenced in Explain Plan
        N - Skips the display of all Histograms (DEFAULT)
 5. Include relevant INIT.ORA DB parameters? <N/Y>
        Y - Displays relevant DB parameters from v$parameter.  
        N - Skips the display of v$parameter (DEFAULT)
 6. Enter your initials to suffix objects <null>
        xx  Create COE temp objects with xx suffix (DEFAULT)
            Use this option if more than one analyst is using this script
            at the same time (concurrency).
            To activate this parameter, remove comments in ACCEPT INITIALS
 DISCLAIMER:
    This script is provided for educational purposes only.  It is not supported
    by Oracle World Wide Technical Support.  The script has been tested and 
    appears to works as intended.  However, you should always test any script 
    before relying on it. 
    Proofread this script prior to running it!  Due to differences in the way 
    text editors, email and operating systems handle text formatting (spaces, 
    tabs and carriage returns), this script may not be in an executable state 
    when you first receive it.  Check over the script to ensure that errors of 
    this type are corrected. 
    This script can be sent to customers.  Do not remove disclaimer paragraph.

 HISTORY:
    02-DEC-99 Created                                                   csierra
    21-JAN-00 Row Count(*) for Tables in Explain Plan is added          csierra
    17-FEB-00 Index summary is added                                    csierra
    20-MAR-00 Statistics information is enhanced and index_column added csierra
    06-APR-00 Operation Type and Order columns are incorporated         csierra
    08-MAY-00 Parameter include_count added to avoid redundant count(*) csierra
    01-SEP-00 COE_PLAN_TABLE is incorporated replacing PLAN_TABLE       csierra
    08-SEP-00 (RBO or CBO has been used) is displayed in Plan           csierra
    08-SEP-00 Display of DBA_TABLES data is splited by blocks           csierra
    14-SEP-00 COE_INDEXES table is created                              csierra
    21-SEP-00 Table Columns are added                                   csierra
    22-SEP-00 Storage Parameters plus Table and Index Extents           csierra
    25-SEP-00 Include relevant DB parameters from v$parameter           csierra
    06-NOV-00 Fixing some column sizes                                  csierra
    20-NOV-00 Include Object_id for tables and indexes (event 10053)    csierra
    27-NOV-00 Added parameter 'initials' to avoid sync crashes multiusr csierra
    12-DEC-00 Hide parameter 'initials' and CBO traceing                csierra
    13-DEC-00 Include all Column Histograms                             csierra 
    21-DEC-00 Fixed High Water Mark - adding 1 to blocks + empty bloks  csierra
    05-JAN-01 Table COE_HISTOGRAMS is created to workaround 894549      csierra
    05-JAN-01 Histograms and FND_HISTOGRAM_COLS are incorporated        csierra
 =============================================================================*/

/* I. Execution Parameters Section                                            
   ===========================================================================*/
SET term on;
PROMPT Unless otherwise instructed by Support, hit <Enter> for each parameter
accept include_count prompt - 
       '1. Include count(*) of Tables in SQL Statement? <n/y> ';
accept include_storage prompt - 
       '2. Include Table and Index Storage Parameters? <n/y/d> ';
accept include_all_columns prompt -
       '3. Include all Table Columns? <n/y> ';
accept include_histograms prompt -
       '4. Include all Column Histograms? <n/y> ';
accept include_vparameter prompt -
       '5. Include relevant INIT.ORA DB parameters? <n/y> ';
def initials='XX'; -- remove comments in next two lines to acctivate parameter
-- accept initials prompt -
--       '6. Enter your initials to suffix objects <null> ';
PROMPT Generating...
SET term off;

/* II. DDL Section - Create Tables COE_PLAN_TABLE, COE_TABLES and COE_INDEXES
   ========================================================================== */
DROP   TABLE COE_PLAN_TABLE_&&initials;
CREATE TABLE COE_PLAN_TABLE_&&initials
    (statement_id varchar2(30),timestamp date,remarks varchar2(80),
     operation varchar2(30),options varchar2(30),object_node varchar2(128),
     object_owner varchar2(30),object_name varchar2(30),
     object_instance numeric,object_type varchar2(30),
     optimizer varchar2(255),search_columns number,id numeric,
     parent_id numeric,position numeric,cost numeric,cardinality numeric,
     bytes numeric,other_tag varchar2(255),partition_start varchar2(255),
     partition_stop varchar2(255),partition_id numeric,other long,
     execution_order numeric);
DROP   TABLE COE_TABLES_&&initials;
CREATE TABLE COE_TABLES_&&initials 
    (TABLE_NUM NUMBER,TABLE_OWNER VARCHAR2(30),TABLE_NAME VARCHAR2(30),
     ROWS_COUNT NUMBER);
DROP   TABLE COE_INDEXES_&&initials;
CREATE TABLE COE_INDEXES_&&initials 
    (TABLE_NUM NUMBER,INDEX_NUM NUMBER,INDEX_OWNER VARCHAR2(30), 
     INDEX_NAME VARCHAR2(30),TABLE_OWNER VARCHAR2(30),TABLE_NAME VARCHAR2(30));
DROP   TABLE COE_HISTOGRAMS_&&initials;
CREATE TABLE COE_HISTOGRAMS_&&initials
    (TABLE_NUM NUMBER,TABLE_OWNER VARCHAR2(30),TABLE_NAME VARCHAR2(30),
     COLUMN_NAME VARCHAR2(30),ENDPOINT_NUMBER NUMBER,ENDPOINT_VALUE NUMBER,
     ENDPOINT_ACTUAL_VALUE VARCHAR2(30), ENDPOINT_JULIAN NUMBER(7));

/* III. Generate Explain Plan Section                                         
   ===========================================================================*/
-- alter session set events '10053 trace name context forever, level 1';
SPOOL coe_statement.lst;
SET pages 1000;
SET lin 150;
SET sqlp '';
SET sqln off;
SET autotrace off;
SET term on;
SET echo on;

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
   fc.forecast_designator        "forecast",
   fc.forecast_set               "set",
   fi.inventory_item_id          "item",
   fd.forecast_date              "fc date",
   fd.original_forecast_quantity "orig qty",
   fd.current_forecast_quantity  "curr qty",
   fd.transaction_id             "xtn id",
   fu.update_sales_order         "sales order"
from
    mrp_forecast_designators     fc,
    mrp_forecast_items           fi,
    mrp_forecast_dates           fd,
    mrp_forecast_updates         fu
where
    fc.organization_id         = 207
and fc.forecast_designator     = 'F-M1-SCP'
and fd.forecast_date           < sysdate
and fc.organization_id         = fi.organization_id
and fc.forecast_designator     = fi.forecast_designator
and fi.organization_id         = fd.organization_id
and fi.forecast_designator     = fd.forecast_designator
and fi.inventory_item_id       = fd.inventory_item_id
and fd.transaction_id          = fu.transaction_id(+)
order by 1,2,3,4;

/* ========================================================================== */
SET echo off;
SPOOL OFF;
SET term off;
-- alter session set events '10053 trace name context off';

/* IV. Compute Execution Order Section                                        
   ===========================================================================*/
DECLARE
    coe_pointer     number := 0;  -- Row on COE_PLAN_TABLE being processed.
    coe_next_order  number := 1;  -- To update Execution Order
    coe_xplain      CONSTANT varchar2(30) := 'COE_XPLAIN'; -- statement_id.
    coe_parent_id   number;       -- To move pointer (only if needed).
    coe_curr_order  varchar2(80); -- Order in current row of COE_PLAN_TABLE.
    coe_count_child number;       -- Number of children for a parent.
BEGIN
    LOOP
        SELECT parent_id, execution_order    -- Reads COE_PLAN_TABLE w/pointer.
        INTO   coe_parent_id, coe_curr_order -- Starts on first row (id=0) and
        FROM   COE_PLAN_TABLE_&&initials     -- works its way down.
        WHERE  id           = coe_pointer
        AND    statement_id = coe_xplain;
        IF  coe_curr_order is not null THEN  -- When row has already its Order:
            EXIT WHEN coe_pointer = 0;       -- Exit Loop if back at the Top.
            coe_pointer := coe_parent_id;    -- Else, move pointer to parent.
        ELSE                                 -- When row doesn't have Order yet:
            SELECT count(*)                  -- Determines if there is any
            INTO   coe_count_child           -- child for the current parent
            FROM   COE_PLAN_TABLE_&&initials -- pending to receive Order.
            WHERE  parent_id        = coe_pointer
            AND    execution_order is null
            AND    statement_id     = coe_xplain;
            IF  coe_count_child     = 0 THEN -- If no child is pending:
                UPDATE COE_PLAN_TABLE_&&initials -- row gets updated with Order.
                SET execution_order = to_char(coe_next_order)
                WHERE  id           = coe_pointer
                AND    statement_id = coe_xplain;
                coe_next_order := coe_next_order + 1; -- Order gets incremented.
            ELSE                             -- If at least one pending child:
                SELECT id                    -- Move pointer to first pending
                INTO   coe_pointer           -- child.
                FROM   COE_PLAN_TABLE_&&initials
                WHERE  parent_id        = coe_pointer
                AND    execution_order is null
                AND    rownum           = 1
                AND    statement_id     = coe_xplain;
            END IF;
        END IF;
    END LOOP;
END;
/

/* V. COLUMN Definition Section                                               
   ===========================================================================*/
SET feed off;
SET numf 999,999,999;
CLEAR columns;
CLEAR breaks;
COLUMN typ FORMAT a3 HEADING 'Ope|Typ';
COLUMN execution_order FORMAT 9999 HEADING 'Exec|Order';
COLUMN query_plan FORMAT a140 HEADING - 
                    'Explain Plan (coe_xplain.sql 8.1/11.5 20010115)' wor;
COLUMN owner_table FORMAT a37 HEADING 'Owner.Table';
COLUMN owner_index FORMAT a40 HEADING 'Owner.Index';
COLUMN table_name FORMAT a30 HEADING 'Table';
COLUMN index_name FORMAT a33 HEADING 'Index';
COLUMN last_analyzed HEADING 'Last|Analyzed';
COLUMN num_rows FORMAT 99,999,999 HEADING -
                    '(B)|Num of rows|in Table|(Cardinality)';
COLUMN num_rows_i FORMAT 99,999,999 HEADING '(C)|Number|of rows|in Index';
COLUMN delta_percent FORMAT b999.9 HEADING 'Delta|Percent|ABS(A-B)/A';
COLUMN avg_row_len FORMAT b99,999 HEADING 'Avg Row|Length|(bytes)';
COLUMN avg_col_len FORMAT b99,999 HEADING 'Avg Col|Length|(bytes)';
COLUMN num_buckets FORMAT b99,999 HEADING 'Number|Buckets|Histogr';
COLUMN hwm_blocks FORMAT b99,999,999 HEADING 'High|Water-Mark|(blocks)';
COLUMN empty_blocks FORMAT b99,999,999 HEADING 'Empty|Blocks';
COLUMN used_blocks FORMAT b99,999,999 HEADING 'Used|Blocks';
COLUMN freelists FORMAT b99 HEADING 'Free|Lists';
COLUMN global_stats FORMAT a6 HEADING 'Global|Stats';
COLUMN distinct_keys FORMAT 99,999,999 HEADING '(D)|Distinct|Keys';
COLUMN num_distinct FORMAT 99,999,999 HEADING '(H)|Num of|Distinct|Values';
COLUMN rows_count FORMAT b99,999,999 HEADING '(A)|Rows from|Count(*)';
COLUMN index_cardinality FORMAT 99,999,999 HEADING - 
                    '(E)|Computed|Index|Cardinality|(C/D)';
COLUMN index_selectivity FORMAT 9.9999eeee HEADING -
                    'Computed|Index|Selectivity|(E/B)';