coe_xplain.sql

来自「oracle常用管理脚本」· SQL 代码 · 共 1,091 行 · 第 1/4 页

COLUMN column_cardinality FORMAT 99,999,999 HEADING -
                    '(I)|Computed|Column|Cardinality|(G/H)';
COLUMN column_selectivity FORMAT 9.9999eeee HEADING -
                    'Computed|Column|Selectivity|(I/B)';
COLUMN density FORMAT 9.9999eeee HEADING -
                    'Column|Density';
COLUMN table_num FORMAT 99 HEADING 'Tab|Num';
COLUMN index_num FORMAT 999 HEADING 'Indx|Num';
COLUMN uniqueness FORMAT a10 HEADING 'Uniqueness';
COLUMN column_name FORMAT a30 HEADING 'Column';
COLUMN column_position FORMAT 999 HEADING 'Pos';
COLUMN nullable FORMAT a9 HEADING 'Null?';
COLUMN num_nulls FORMAT 99,999,999 HEADING -
                    '(F)|Number of|Rows with|NULLs in|this column';
COLUMN not_nulls FORMAT 99,999,999 HEADING -
                    '(G)|Number of|Rows with|Value|(B-F)';
COLUMN avg_space FORMAT b9,999,999,999 HEADING - 
                    'Average|free space|per allocated|block (bytes)';
COLUMN avg_space_percent FORMAT b99,999,999.9 HEADING - 
                    'Average|free space|per allocated|block (%)';
COLUMN degree FORMAT b99,999 HEADING 'Degree|of Para-|llelism';
COLUMN partitioned FORMAT a7 HEADING 'Parti-|tioned?';
COLUMN sample_size FORMAT b9,999,999 HEADING 'Sample|Size|(% or|rows)';
COLUMN chain_cnt FORMAT 99,999,999 HEADING 'Chain|Count|(rows)';
COLUMN tablespace_name FORMAT a20 HEADING 'Tablespace';
COLUMN pct_free FORMAT b999 HEADING - 
                    'Minimum|percent of|free space|per block|(pct_free)';
COLUMN pct_used FORMAT b999 HEADING - 
                    'Minimum|percent of|used space|per block|(pct_used)';
COLUMN initial_extent FORMAT b999,999,999,999 HEADING - 
                    'Initial|Extent|size|(bytes)';
COLUMN next_extent FORMAT b999,999,999,999 HEADING -
                    'Next|Extent|size|(bytes)';
COLUMN min_extents FORMAT b999,999 HEADING 'Minimum|num. of|Extents';
COLUMN max_extents FORMAT b9,999,999,999 HEADING 'Maximum num.|of Extents';
COLUMN pct_increase FORMAT b99,999 HEADING - 
                    'Percent|increase|size for|Next|Extent';
COLUMN count_extents FORMAT b99,999 HEADING 'Actual|Extents|Count';
COLUMN index_type FORMAT a12 HEADING 'Index Type';
COLUMN status FORMAT a8 HEADING 'Status';
COLUMN blevel FORMAT 999,999 HEADING 'B*-Tree|level|(index|depth)';
COLUMN leaf_blocks FORMAT 9,999,999 HEADING 'Number|of|leaf|blocks';
COLUMN avg_leaf_blocks_per_key FORMAT 9,999,999 HEADING - 
                    'Avg num of|leaf blocks|per key';
COLUMN avg_data_blocks_per_key FORMAT 9,999,999 HEADING -
                    'Avg Num of|data blocks|per key';
COLUMN clustering_factor FORMAT 99,999,999 HEADING 'Clustering|factor';
COLUMN data_type FORMAT a14 HEADING 'Type';
COLUMN column_id FORMAT 9999 HEADING 'Col';
COLUMN extent_id FORMAT 999999999 HEADING 'Extent ID';
COLUMN file_id FORMAT 9999999 HEADING 'File ID';
COLUMN block_id FORMAT 9999999999 HEADING 'Block ID|from';
COLUMN block_to FORMAT 9999999999 HEADING 'Block ID|to';
COLUMN blocks FORMAT 99,999,999 HEADING 'Blocks';
COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes';
COLUMN vparameter FORMAT a50 HEADING - 
                    'Relevant init.ora parameters from v$parameter'; 
COLUMN init11i FORMAT a14  HEADING -
                    'init.ora value|required for|apps 11i';
COLUMN object_id FORMAT 999999 HEADING 'Object';
COLUMN indexed_column FORMAT a7 HEADING 'Indexed|Column';
COLUMN endpoint_number FORMAT 999999 HEADING 'Bucket|Number';
COLUMN endpoint_value FORMAT B999999999999 HEADING -
                    'Normalized|end point|numeric|value';
COLUMN endpoint_value_e FORMAT 9.999999eeee HEADING -
                    'Normalized|end point|numeric|value (e10)';
COLUMN endpoint_value_d FORMAT a11 HEADING -
                    'Normalized|end point|numeric|value (date)';
COLUMN endpoint_actual_value FORMAT a30 HEADING -
                    'Normalized|end point|actual|value';
COLUMN partition FORMAT a30 HEADING 'Partition';
COLUMN hsize FORMAT 9999999 HEADING 'Num. of|Buckets|(max)';

/* VI. Populate COE_TABLES and COE_INDEXES Section                            
   ===========================================================================*/
INSERT 
    INTO COE_TABLES_&&initials 
SELECT 
    NULL,         -- TABLE_NUM
    OBJECT_OWNER, -- TABLE_OWNER
    OBJECT_NAME,  -- TABLE_NAME
    NULL          -- ROWS_COUNT
FROM 
    COE_PLAN_TABLE_&&initials 
WHERE 
    STATEMENT_ID = 'COE_XPLAIN' 
and OPERATION    = 'TABLE ACCESS'
UNION 
SELECT 
    NULL,        -- TABLE_NUM
    TABLE_OWNER, -- TABLE_OWNER
    TABLE_NAME,  -- TABLE_NAME
    NULL         -- ROWS_COUNT
FROM 
    ALL_INDEXES
WHERE 
    (OWNER, 
     INDEX_NAME) 
IN  (SELECT DISTINCT 
         OBJECT_OWNER, 
         OBJECT_NAME 
     FROM 
         COE_PLAN_TABLE_&&initials
     WHERE 
         STATEMENT_ID = 'COE_XPLAIN' 
     and OPERATION    = 'INDEX');

UPDATE 
    COE_TABLES_&&initials 
SET 
    TABLE_NUM = ROWNUM;

INSERT 
    INTO COE_INDEXES_&&initials 
SELECT DISTINCT
    CT.TABLE_NUM,   -- TABLE_NUM
    NULL,           -- INDEX_NUM
    AI.OWNER,       -- INDEX_OWNER
    AI.INDEX_NAME,  -- INDEX_NAME
    CT.TABLE_OWNER, -- TABLE_OWNER
    CT.TABLE_NAME   -- TABLE_NAME
FROM 
    COE_TABLES_&&initials CT,
    ALL_INDEXES           AI
WHERE 
    CT.TABLE_OWNER   = AI.TABLE_OWNER
AND CT.TABLE_NAME    = AI.TABLE_NAME;

UPDATE 
    COE_INDEXES_&&initials
SET 
    INDEX_NUM = ROWNUM;

SET hea off;
SET pages 0;
SET lin 400;
SPOOL coe_xplain_tables.sql;
SELECT 
    'UPDATE COE_TABLES_&&initials CT SET ROWS_COUNT=(SELECT '||
    'COUNT(*) FROM '||TABLE_OWNER||'.'||TABLE_NAME||
    ') WHERE CT.TABLE_OWNER='''||
    TABLE_OWNER||''' AND CT.TABLE_NAME='''||TABLE_NAME||''';'
FROM 
    COE_TABLES_&&initials
WHERE
    NVL(substr(upper('&include_count'),1,1),'N') = 'Y';
SPOOL OFF;
@coe_xplain_tables.sql;
SPOOL coe_xplain_histograms.sql;
SELECT
    'INSERT INTO COE_HISTOGRAMS_&&initials SELECT '||TABLE_NUM||', '''||
    TABLE_OWNER||''', '''||TABLE_NAME||''', COLUMN_NAME, ENDPOINT_NUMBER, '||
    'ENDPOINT_VALUE, SUBSTR(ENDPOINT_ACTUAL_VALUE,1,30), NULL '||
    'FROM ALL_TAB_HISTOGRAMS WHERE OWNER='''||TABLE_OWNER||
    ''' AND TABLE_NAME='''||TABLE_NAME||''';'
FROM 
    COE_TABLES_&&initials
WHERE
    NVL(substr(upper('&include_histograms'),1,1),'N') = 'Y';
SPOOL OFF;
@coe_xplain_histograms.sql;
UPDATE
    COE_HISTOGRAMS_&&initials
SET
    ENDPOINT_JULIAN = TRUNC(ENDPOINT_VALUE)
WHERE
    ENDPOINT_VALUE BETWEEN 1 AND 5373484;
COMMIT;

/* VII. Creates coe_xplain.lst spool file with Explain Plan                   
   ===========================================================================*/
SET hea on;
SET pages 10000;
SPOOL coe_xplain.lst;
SET lin 150;
SET term on;

SELECT 
    DECODE(OPERATION,'SORT','SET','VIEW','SET','HASH JOIN','S/R',
           'ROW') typ,  -- Operation Type
    EXECUTION_ORDER, -- Processing Order
    lpad(' ',LEVEL,rpad(' ',80,'....|'))||OPERATION||' '|| -- Operation
    DECODE(OPTIONS,NULL,'',DECODE(SUBSTR(OPTIONS,1,4),'FULL', 
           '***('||OPTIONS||')*** ','('||OPTIONS||') '))|| -- Options
    DECODE(OBJECT_OWNER,null,'','OF '''||OBJECT_OWNER||'.')|| -- Owner
    DECODE(OBJECT_NAME,null,'',OBJECT_NAME||''' ')|| -- Object Name
    DECODE(OBJECT_TYPE,null,'','('||OBJECT_TYPE||') ')|| -- Object Type
    DECODE(ID,0,'Opt_Mode:')||
    DECODE(OPTIMIZER,null,'','ANALYZED','',OPTIMIZER||' ')||
    DECODE(ID,0,DECODE(POSITION,null,'(RBO','Total_Cost:'||POSITION||' '||
           '(CBO')||' has been used)  ')|| -- CBO or RBO has been used
    DECODE(ID,0,'',DECODE(COST||CARDINALITY||BYTES,null,'','('))|| -- (
    DECODE(COST,NULL,'',DECODE(ID,0,'','Cost='||COST))|| -- Operation Cost
    DECODE(CARDINALITY,null,'',DECODE(ID,0,'',
    DECODE(COST,null,'',' ')||'Card='||CARDINALITY))||
    DECODE(BYTES,null,'',DECODE(ID,0,'',
    DECODE(COST||CARDINALITY,null,'',' ')||'Bytes='||BYTES))||
    DECODE(ID,0,'',DECODE(COST||CARDINALITY||BYTES,null,'',')')) -- )
    query_plan -- Explain Plan
FROM 
    COE_PLAN_TABLE_&&initials 
WHERE 
    STATEMENT_ID = 'COE_XPLAIN'
CONNECT BY 
    PRIOR ID     = PARENT_ID 
AND STATEMENT_ID = 'COE_XPLAIN'
START WITH 
    ID           = 0 
AND STATEMENT_ID = 'COE_XPLAIN';
PROMPT
PROMPT Note: Card=Computed or Default Object Cardinality
PROMPT
PAUSE Enter to continue

/* VIII. TABLES Section                                                       */
/* ===========================================================================*/
PROMPT
PROMPT I. TABLES
PROMPT =========
SELECT 
    CT.TABLE_NUM,          -- Table Number
    AA.OBJECT_ID,          -- For Event 10053
    CT.TABLE_OWNER||'.'||CT.TABLE_NAME
        OWNER_TABLE,       -- Table Owner and Name
    CT.ROWS_COUNT,         -- Count(*) on Table
    to_number(AT.DEGREE)
        DEGREE,            -- Degree of Parallelism
    AT.PARTITIONED,        -- Is this Table partitioned?
    AT.CHAIN_CNT,          -- Count of rows that are chained
    AT.FREELISTS           
FROM 
    ALL_OBJECTS            AA,
    ALL_TABLES             AT,
    COE_TABLES_&&initials  CT   -- All Tables referenced in Explain Plan
WHERE 
    CT.TABLE_OWNER = AT.OWNER 
AND CT.TABLE_NAME  = AT.TABLE_NAME
AND CT.TABLE_OWNER = AA.OWNER
AND CT.TABLE_NAME  = AA.OBJECT_NAME
AND AA.OBJECT_TYPE = 'TABLE'
ORDER BY
    CT.TABLE_NUM;

PROMPT
PROMPT I.a TABLES Statistics
PROMPT =====================
SELECT 
    CT.TABLE_NUM,          -- Table Number
    to_char(AT.LAST_ANALYZED,'YYYYMMDD:HH24MISS')
        LAST_ANALYZED,     -- Last Analyzed
    AT.SAMPLE_SIZE,        -- Sample Size used when Alalyzed
    AT.NUM_ROWS,           -- Number of Rows in Table according to Analyze
    ROUND(ABS(CT.ROWS_COUNT-AT.NUM_ROWS)/
    DECODE(AT.NUM_ROWS,0,null,AT.NUM_ROWS)*100,1)
        DELTA_PERCENT,     -- Delta % = ABS(A-B)/A
    AT.AVG_ROW_LEN,        -- Average Row Length in bytes
    AT.BLOCKS
       USED_BLOCKS,        -- Used Blocks in Table
    AT.EMPTY_BLOCKS,       -- Empty Blocks in Table
    AT.BLOCKS + AT.EMPTY_BLOCKS + 1 -- Includes Root block
       HWM_BLOCKS,         -- High Water Mark in Blocks
    AT.AVG_SPACE,          -- Avg free space per allocated block in bytes
    ROUND(AT.AVG_SPACE/TO_NUMBER(VP.VALUE)*100,3)
       AVG_SPACE_PERCENT   -- Avg free space per allocated block (percent)
FROM 
    V$PARAMETER            VP,
    ALL_TABLES             AT,
    COE_TABLES_&&initials  CT   -- All Tables referenced in Explain Plan
WHERE 
    CT.TABLE_OWNER = AT.OWNER 
AND CT.TABLE_NAME  = AT.TABLE_NAME
AND VP.NAME        = 'db_block_size'
ORDER BY