coe_xplain.sql

oracle常用管理脚本

    CT.TABLE_NUM;

PROMPT
PROMPT I.b TABLES Storage Parameters
PROMPT =============================
SELECT 
    CT.TABLE_NUM,          -- Table Number
    AT.TABLESPACE_NAME,    -- Tablespace
    AT.PCT_FREE,           -- Minimum percentage of free space per block
    AT.PCT_USED,           -- Minimum percentage of used space per block
    AT.INITIAL_EXTENT,     -- Initial Extent size in bytes
    AT.NEXT_EXTENT,        -- Next Extent size in bytes
    AT.MIN_EXTENTS,        -- Minimum number of Extents for this Table
    AT.MAX_EXTENTS,        -- Maximum number of Extents for this Table
    AT.PCT_INCREASE       -- Percentage increase size for Next Extent
FROM 
    ALL_TABLES            AT,
    COE_TABLES_&&initials CT  -- All Tables referenced in Explain Plan
WHERE 
    substr(upper('&include_storage'),1,1) IN ('Y','D')
AND CT.TABLE_OWNER  = AT.OWNER 
AND CT.TABLE_NAME   = AT.TABLE_NAME
ORDER BY
    CT.TABLE_NUM;

BREAK ON TABLE_NUM SKIP 1 ON TABLESPACE_NAME;
COMPUTE SUM LABEL "" OF BLOCKS BYTES ON TABLE_NUM;
SELECT
    CT.TABLE_NUM,          -- Table Number
    DE.TABLESPACE_NAME,    -- Tablespace
    DE.EXTENT_ID,          -- Extent ID
    DE.FILE_ID,            -- File ID
    DE.BLOCK_ID,           -- Block ID from
    DE.BLOCK_ID + DE.BLOCKS - 1
        BLOCK_TO,          -- Block ID to
    DE.BLOCKS,             -- Number of Blocks
    DE.BYTES               -- Number of Bytes
FROM
    DBA_EXTENTS            DE,
    COE_TABLES_&&initials  CT
WHERE
    substr(upper('&include_storage'),1,1) = 'D'
AND CT.TABLE_OWNER  = DE.OWNER
AND CT.TABLE_NAME   = DE.SEGMENT_NAME
AND DE.SEGMENT_TYPE = 'TABLE'
ORDER BY
    CT.TABLE_NUM,
    DE.TABLESPACE_NAME,
    DE.EXTENT_ID;

/* IX. INDEXES Section                                                        */
/* ===========================================================================*/
PROMPT
PROMPT II. INDEXES
PROMPT ===========
BREAK ON TABLE_NUM SKIP 1;
SELECT 
    CI.TABLE_NUM,           -- Table Number
    CI.INDEX_NUM,           -- Index Number
    AA.OBJECT_ID,           -- For Event 10053
    CI.INDEX_OWNER||'.'||CI.INDEX_NAME
        OWNER_INDEX,        -- Index Owner and Name
    AI.INDEX_TYPE,          -- Index Type
    AI.UNIQUENESS,          -- UNIQUE or NONUNIQUE
    AI.STATUS,              -- VALID or UNUSABLE
    to_number(AI.DEGREE)
        DEGREE,            -- Degree of Parallelism
    AI.PARTITIONED,        -- Is this Table partitioned?
    AI.FREELISTS
FROM 
    ALL_OBJECTS            AA, 
    ALL_INDEXES            AI, 
    COE_INDEXES_&&initials CI  -- All Tables referenced in Explain Plan
WHERE 
    CI.INDEX_OWNER  = AI.OWNER 
AND CI.INDEX_NAME   = AI.INDEX_NAME 
AND CI.INDEX_OWNER  = AA.OWNER
AND CI.INDEX_NAME   = AA.OBJECT_NAME
AND AA.OBJECT_TYPE  = 'INDEX'
ORDER BY 
    CI.TABLE_NUM,
    CI.INDEX_NUM;

PROMPT
PROMPT II.a INDEXES Statistics
PROMPT =======================
SELECT 
    CI.TABLE_NUM,           -- Table Number
    CI.INDEX_NUM,           -- Index Number
    AA.OBJECT_ID,           -- For Event 10053
    CI.INDEX_NAME,          -- Index Name
    to_char(AI.LAST_ANALYZED,'YYYYMMDD:HH24MISS')
        LAST_ANALYZED,      -- Last Analyzed
    AI.SAMPLE_SIZE,         -- Sample Size used when Analyzed
    AI.NUM_ROWS NUM_ROWS_I, -- Number of Rows in Index according to Analyze
    AI.DISTINCT_KEYS,       -- Number of Distinct code combinations in Index
    CEIL(AI.NUM_ROWS/DECODE(AI.DISTINCT_KEYS,0,null,AI.DISTINCT_KEYS))
       INDEX_CARDINALITY,   -- The lower the better.  1 is the best
    AI.NUM_ROWS/DECODE(AI.DISTINCT_KEYS,0,null,AI.DISTINCT_KEYS)/
    DECODE(AT.NUM_ROWS,0,null,AT.NUM_ROWS)
       INDEX_SELECTIVITY    -- Index Selectivity
FROM 
    ALL_OBJECTS             AA,
    ALL_TABLES              AT,
    ALL_INDEXES             AI,
    COE_INDEXES_&&initials  CI -- All Tables referenced in Explain Plan
WHERE 
    CI.INDEX_OWNER  = AI.OWNER 
AND CI.INDEX_NAME   = AI.INDEX_NAME 
AND CI.TABLE_OWNER  = AT.OWNER
AND CI.TABLE_NAME   = AT.TABLE_NAME
AND CI.INDEX_OWNER  = AA.OWNER
AND CI.INDEX_NAME   = AA.OBJECT_NAME
AND AA.OBJECT_TYPE  = 'INDEX'
ORDER BY 
    CI.TABLE_NUM,
    CI.INDEX_NUM;

SELECT 
    CI.TABLE_NUM,           -- Table Number
    CI.INDEX_NUM,           -- Index Number
    AA.OBJECT_ID,           -- For Event 10053
    CI.INDEX_NAME,          -- Index Name
    AI.BLEVEL,              -- B*-Tree level (index depth)
    AI.LEAF_BLOCKS,         -- Number of leaf blocks
    AI.AVG_LEAF_BLOCKS_PER_KEY, -- Avg num of leaf blocks per key
    AI.AVG_DATA_BLOCKS_PER_KEY, -- Avg num of data blocks per key
    SUM(ATC.AVG_COL_LEN)    -- 8.1
       AVG_ROW_LEN,         -- 8.1
    AI.CLUSTERING_FACTOR    -- Between Num Blocks and Num Rows
FROM                        
    ALL_OBJECTS             AA,
    ALL_TAB_COLUMNS         ATC,
    ALL_IND_COLUMNS         AIC,
    ALL_INDEXES             AI,
    COE_INDEXES_&&initials  CI -- All Tables referenced in Explain Plan
WHERE 
    CI.INDEX_OWNER  = AI.OWNER 
AND CI.INDEX_NAME   = AI.INDEX_NAME 
AND 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.INDEX_OWNER  = AA.OWNER
AND CI.INDEX_NAME   = AA.OBJECT_NAME
AND AA.OBJECT_TYPE  = 'INDEX'
GROUP BY
    CI.TABLE_NUM,
    CI.INDEX_NUM,
    AA.OBJECT_ID,
    CI.INDEX_NAME,
    AI.BLEVEL,
    AI.LEAF_BLOCKS,
    AI.AVG_LEAF_BLOCKS_PER_KEY,
    AI.AVG_DATA_BLOCKS_PER_KEY,
    AI.CLUSTERING_FACTOR
ORDER BY 
    CI.TABLE_NUM,
    CI.INDEX_NUM;

PROMPT
PROMPT II.b INDEXES Storage Parameters
PROMPT ===============================
SELECT 
    CI.TABLE_NUM,          -- Table Number
    CI.INDEX_NUM,          -- Index Number
    AI.TABLESPACE_NAME,    -- Tablespace
    AI.PCT_FREE,           -- Minimum percentage of free space per block
    AI.INITIAL_EXTENT,     -- Initial Extent size in bytes
    AI.NEXT_EXTENT,        -- Next Extent size in bytes
    AI.MIN_EXTENTS,        -- Minimum number of Extents for this Index
    AI.MAX_EXTENTS,        -- Maximum number of Extents for this Index
    AI.PCT_INCREASE        -- Percentage increase size for Next Extent
FROM 
    ALL_INDEXES            AI,
    COE_INDEXES_&&initials CI -- All Indexes referenced in Explain Plan
WHERE 
    substr(upper('&include_storage'),1,1) IN ('Y','D')
AND CI.INDEX_OWNER  = AI.OWNER 
AND CI.INDEX_NAME   = AI.INDEX_NAME
ORDER BY
    CI.TABLE_NUM,
    CI.INDEX_NUM;

BREAK ON TABLE_NUM SKIP 2 ON INDEX_NUM SKIP 1 ON TABLESPACE_NAME;
COMPUTE SUM LABEL "" OF BLOCKS BYTES ON INDEX_NUM;
SELECT
    CI.TABLE_NUM,          -- Table Number
    CI.INDEX_NUM,          -- Index Number
    DE.TABLESPACE_NAME,    -- Tablespace
    DE.EXTENT_ID,          -- Extent ID
    DE.FILE_ID,            -- File ID
    DE.BLOCK_ID,           -- Block ID from
    DE.BLOCK_ID + DE.BLOCKS - 1
        BLOCK_TO,          -- Block ID to
    DE.BLOCKS,             -- Number of Blocks
    DE.BYTES               -- Number of Bytes
FROM
    DBA_EXTENTS            DE,
    COE_INDEXES_&&initials CI
WHERE
    substr(upper('&include_storage'),1,1) = 'D'
AND CI.INDEX_OWNER  = DE.OWNER
AND CI.INDEX_NAME   = DE.SEGMENT_NAME
AND DE.SEGMENT_TYPE = 'INDEX'
ORDER BY
    CI.TABLE_NUM,
    CI.INDEX_NUM,
    DE.TABLESPACE_NAME,
    DE.EXTENT_ID;

/* X. COLUMNS Section                                                         */
/* ===========================================================================*/
PROMPT
PROMPT III. COLUMNS
PROMPT ============
BREAK ON TABLE_NUM ON INDEX_NUM ON OBJECT_ID ON INDEX_NAME SKIP 1;
SELECT
    CI.TABLE_NUM,          -- Table Number
    CI.INDEX_NUM,          -- Index Number
    AA.OBJECT_ID,          -- For Event 10053
    CI.INDEX_NAME,         -- Index Name
    AIC.COLUMN_POSITION,   -- Position within Index
    ATC.COLUMN_ID,         -- For Event 10053
    AIC.COLUMN_NAME,       -- Column Name (ordered by column_position)
    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_OBJECTS            AA,
    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.INDEX_OWNER  = AA.OWNER
AND CI.INDEX_NAME   = AA.OBJECT_NAME
AND AA.OBJECT_TYPE  = 'INDEX'
ORDER BY 
   CI.TABLE_NUM,
   CI.INDEX_NUM,
   AIC.COLUMN_POSITION;

PROMPT 
PROMPT III.a INDEX COLUMNS Statistics
PROMPT ==============================
BREAK ON INDEX_NAME SKIP 1;
SELECT
    CI.INDEX_NAME,         -- Index Name
    AIC.COLUMN_NAME,       -- Column Name (ordered by column_position)
    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