coe_xplain.sql

oracle常用管理脚本

    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                                                                    */