/*$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)';