ALL_TABLES             AT,

    ALL_TAB_COLUMNS        ATC,

    ALL_IND_COLUMNS        AIC, 

    COE_INDEXES_&&initials CI -- All Indexes referenced in Explain Plan

WHERE 

    CI.INDEX_OWNER  = AIC.INDEX_OWNER 

AND CI.INDEX_NAME   = AIC.INDEX_NAME

AND CI.TABLE_OWNER  = ATC.OWNER 

AND CI.TABLE_NAME   = ATC.TABLE_NAME

AND AIC.COLUMN_NAME = ATC.COLUMN_NAME 

AND CI.TABLE_OWNER  = AT.OWNER 

AND CI.TABLE_NAME   = AT.TABLE_NAME

ORDER BY   

   CI.TABLE_NUM,

   CI.INDEX_NUM,

   AIC.COLUMN_POSITION;



PROMPT

PROMPT III.b TABLE COLUMNS

PROMPT ===================

CLEAR breaks;

SELECT

    CT.TABLE_NUM,          -- Table Number

    CT.TABLE_NAME          -- Table Name

FROM

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_all_columns'),1,1) = 'Y';



BREAK ON TABLE_NUM SKIP 1;

SELECT

    CT.TABLE_NUM,          -- Table Number

    ATC.COLUMN_ID,         -- Sequence number of column as created

    ATC.COLUMN_NAME,

    DECODE(ATC.NULLABLE,'N','NOT NULL') 

        NULLABLE,          -- NULL or NOT NULL

    ATC.DATA_TYPE||DECODE(ATC.DATA_TYPE,

        'VARCHAR2','('||ATC.DATA_LENGTH||')',

        'CHAR','('||ATC.DATA_LENGTH||')',

        'NUMBER',DECODE(ATC.DATA_PRECISION,NULL,NULL,'('||ATC.DATA_PRECISION||

           DECODE(ATC.DATA_SCALE,NULL,NULL,0,NULL,','||ATC.DATA_SCALE)||')'))

        DATA_TYPE          -- Data Type and length

FROM

    ALL_TAB_COLUMNS        ATC,

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_all_columns'),1,1) = 'Y'

AND CT.TABLE_OWNER  = ATC.OWNER

AND CT.TABLE_NAME   = ATC.TABLE_NAME

ORDER BY

    CT.TABLE_NUM,

    ATC.COLUMN_ID;



PROMPT

PROMPT III.c TABLE COLUMNS Statistics

PROMPT ==============================

CLEAR breaks;

SELECT

    CT.TABLE_NUM,          -- Table Number

    CT.TABLE_NAME          -- Table Name

FROM

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_all_columns'),1,1) = 'Y';



BREAK ON TABLE_NUM SKIP 1;

SELECT

    CT.TABLE_NUM,          -- Table Number

    ATC.COLUMN_ID,         -- Sequence number of column as created

    ATC.COLUMN_NAME,       -- Column Name

    to_char(ATC.LAST_ANALYZED,'YYYYMMDD:HH24MISS')

        LAST_ANALYZED,     -- Last Analyzed

    ATC.SAMPLE_SIZE,       -- Sample Size used when Analyzed

    ATC.AVG_COL_LEN,       -- 8.1 Average column length

    ATC.NUM_BUCKETS        -- Num. of Buckets for Histograms

FROM

    ALL_TAB_COLUMNS        ATC,

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_all_columns'),1,1) = 'Y'

AND CT.TABLE_OWNER  = ATC.OWNER 

AND CT.TABLE_NAME   = ATC.TABLE_NAME

ORDER BY

    CT.TABLE_NUM,

    ATC.COLUMN_ID;



CLEAR breaks;

SELECT

    CT.TABLE_NUM,          -- Table Number

    CT.TABLE_NAME          -- Table Name

FROM

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_all_columns'),1,1) = 'Y';



BREAK ON TABLE_NUM SKIP 1;

SELECT

    CT.TABLE_NUM,          -- Table Number

    ATC.COLUMN_ID,         -- Sequence number of column as created

    ATC.COLUMN_NAME,       -- Column Name

    ATC.NUM_NULLS,         -- Number of Rows with NULLs in this column

    AT.NUM_ROWS - ATC.NUM_NULLS

        NOT_NULLS,         -- Number of Rows with Value

    ATC.NUM_DISTINCT,      -- Number of Distinct values on this column

    CEIL((AT.NUM_ROWS-ATC.NUM_NULLS)/

           DECODE(ATC.NUM_DISTINCT,0,null,ATC.NUM_DISTINCT))

        COLUMN_CARDINALITY,-- The Lower the better.  1 is the best.

    (AT.NUM_ROWS-ATC.NUM_NULLS)/

           DECODE(ATC.NUM_DISTINCT,0,null,ATC.NUM_DISTINCT)/

           DECODE(AT.NUM_ROWS,0,null,AT.NUM_ROWS)

        COLUMN_SELECTIVITY,-- Column Selectivity

    ATC.DENSITY            -- Column Density (possible due to Histograms)

FROM

    ALL_TAB_COLUMNS        ATC,

    ALL_TABLES             AT,

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_all_columns'),1,1) = 'Y'

AND CT.TABLE_OWNER  = AT.OWNER 

AND CT.TABLE_NAME   = AT.TABLE_NAME

AND CT.TABLE_OWNER  = ATC.OWNER

AND CT.TABLE_NAME   = ATC.TABLE_NAME

ORDER BY

    CT.TABLE_NUM,

    ATC.COLUMN_ID;



/* XI. Histograms Section                                                     */

/* ===========================================================================*/

PROMPT

PROMPT IV. HISTOGRAMS

PROMPT ==============

CLEAR breaks;

SELECT

    CT.TABLE_NUM,          -- Table Number

    CT.TABLE_NAME          -- Table Name

FROM

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_histograms'),1,1) = 'Y';



BREAK ON TABLE_NUM SKIP 1;

SELECT

    CT.TABLE_NUM,          -- Table Number

    FHC.COLUMN_NAME,       -- Column Name

    FHC.PARTITION,         -- Table Partition

    FHC.HSIZE              -- Number of Buckets (Max)

FROM

    FND_HISTOGRAM_COLS  FHC,

    COE_TABLES_&&initials  CT

WHERE

    substr(upper('&include_histograms'),1,1) = 'Y'

AND CT.TABLE_NAME   = FHC.TABLE_NAME

ORDER BY

    CT.TABLE_NUM,

    FHC.COLUMN_NAME;



BREAK ON TABLE_NUM SKIP 1 ON COLUMN_ID ON COLUMN_NAME SKIP 1;

SELECT 

    CH.TABLE_NUM,          -- Table Number

    ATC.COLUMN_ID,         -- Column Id

    CH.COLUMN_NAME,        -- Column Name

    CH.ENDPOINT_NUMBER,    -- Bucket

    CH.ENDPOINT_VALUE

        endpoint_value_e,  -- Normalized numeric value (e10)

    DECODE(ATC.DATA_TYPE,'NUMBER',CH.ENDPOINT_VALUE)

        endpoint_value,    -- Normalized numeric value

    DECODE(ATC.DATA_TYPE,'DATE',

    TO_CHAR(TO_DATE(CH.ENDPOINT_JULIAN,'J'),'DD-MON-YYYY'))

        endpoint_value_d,  -- Normalized numeric value (date)

    CH.ENDPOINT_ACTUAL_VALUE -- Normalized actual value

FROM

    ALL_TAB_COLUMNS            ATC,

    COE_HISTOGRAMS_&&initials  CH

WHERE

    substr(upper('&include_histograms'),1,1) = 'Y'

AND CH.TABLE_OWNER  = ATC.OWNER

AND CH.TABLE_NAME   = ATC.TABLE_NAME

AND CH.COLUMN_NAME  = ATC.COLUMN_NAME

ORDER BY

    CH.TABLE_NUM,

    ATC.COLUMN_ID,

    CH.COLUMN_NAME,

    CH.ENDPOINT_NUMBER;



/* X. v$parameter Section                                                     */

/* ===========================================================================*/

PROMPT

PROMPT V. INIT.ORA parameters

PROMPT ======================

SELECT

    name||' = '||value vparameter,

    decode(name,

        '_sort_elimination_cost_ratio',decode(value,'5','ok','5'),

        '_optimizer_mode_force',decode(value,'TRUE','ok','TRUE'),

        '_fast_full_scan_enabled',decode(value,'FALSE','ok','FALSE'),

        '_ordered_nested_loop',decode(value,'TRUE','ok','TRUE'),

        '_complex_view_merging',decode(value,'TRUE','ok','TRUE'),

        '_push_join_predicate',decode(value,'TRUE','ok','TRUE'),

        '_use_column_stats_for_function',decode(value,'TRUE','ok','TRUE'),

        '_push_join_union_view',decode(value,'TRUE','ok','TRUE'),

        '_like_with_bind_as_equality',decode(value,'TRUE','ok','TRUE'),

        '_or_expand_nvl_predicate',decode(value,'TRUE','ok','TRUE'),

        '_table_scan_cost_plus_one',decode(value,'TRUE','ok','TRUE'),

        '_optimizer_undo_changes',decode(value,'FALSE','ok','FALSE'),

        'db_file_multiblock_read_count',decode(value,'8','ok','8'),

        'optimizer_max_permutations',decode(value,'79000','ok','79000'),

        'optimizer_mode',decode(value,'CHOOSE','ok','CHOOSE'),

        'optimizer_percent_parallel',decode(value,'0','ok','0'),

        'optimizer_features_enable',decode(value,'8.1.6','ok','8.1.6'),

        'query_rewrite_enabled',decode(value,'TRUE','ok','TRUE'),

        'compatible',decode(value,'8.1.6','ok','8.1.6'),

        null) init11i

FROM

    v$parameter

WHERE

    (   name like '_optimizer%'

     OR name like 'optimizer%'

     OR name like 'always%join'

     OR name like 'compatible'

     OR name like 'db_block_buffers'

     OR name like 'db_block_size'

     OR name like 'db_file_multiblock_read_count'

     OR name like '_complex_view_merging'

     OR name like 'complex_view_merging'

     OR name like 'cursor_sharing'

     OR name like '_fast_full_scan_enabled'

     OR name like 'fast_full_scan_enabled'

     OR name like '_ordered_nested_loop'

     OR name like 'ordered_nested_loop'

     OR name like 'hash%'

     OR name like 'max_dump_file_size'

     OR name like 'parallel%'

     OR name like 'partition_view_enabled'

     OR name like '_push_join%'

     OR name like 'push_join%'

     OR name like 'shared_pool_size'

     OR name like '_sort%'

     OR name like 'sort%'

     OR name like '_use_column_stats_for_function'

     OR name like 'use_column_stats_for_function'

     OR name like '_table_scan_cost_plus_one'

     OR name like 'table_scan_cost_plus_one'

     OR name like '_like_with_bind_as_equality'

     OR name like 'like_with_bind_as_equality'

     OR name like '_or_expand_nvl_predicate'

     OR name like 'or_expand_nvl_predicate'

     OR name like 'star_transformation_enabled'

     OR name like 'query_rewrite_enabled'

     OR name like 'user_dump_dest')

AND substr(upper('&include_vparameter'),1,1) = 'Y'

ORDER BY

    name;



/* XI. Finishing Section                                                      */

/* ===========================================================================*/

COMMIT;

SPOOL OFF;

PROMPT

PROMPT coe_statement.lst and coe_xplain.lst files have been generated.

PROMPT

PROMPT To Print them nicely, open these two files using Wordpad or Word.

PROMPT Use File -> Page Setup (menu option) to change Orientation to Landscape.

PROMPT Using same menu option make all 4 Margins 0.5".  Exit this menu option.

PROMPT Do a 'Select All' (Ctrl+A) and change Font to 'Courier New' Size 8.

PROMPT

PAUSE Hit <Enter> to close this SQL*Plus session

DROP TABLE COE_PLAN_TABLE_&&initials;

DROP TABLE COE_TABLES_&&initials;

DROP TABLE COE_INDEXES_&&initials;

exit;

/* The-End                                                                    */