?? coe_xplain.sql
字號:
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
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -