196 lines
7.7 KiB
SQL
196 lines
7.7 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display STALE Statistics of a table (including part, sub-par, ind, ind-part, ind-subpart)
|
|
* Parameters : 1 - OWNER
|
|
* 2 - Table Name
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 16-Mar-12 Vishal Gupta Intial version
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE partition_name
|
|
UNDEFINE subpartition_name
|
|
UNDEFINE object_type
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE partition_name="&&3"
|
|
DEFINE subpartition_name="&&4"
|
|
DEFINE object_type="&&5"
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _partition_name NEW_VALUE partition_name NOPRINT
|
|
COLUMN _subpartition_name NEW_VALUE subpartition_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
set term off
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
, DECODE('&&partition_name','','%','&&partition_name') "_partition_name"
|
|
, DECODE('&&subpartition_name','','%','&&subpartition_name') "_subpartition_name"
|
|
, DECODE('&&object_type','','%','&&object_type') "_object_type"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
PROMPT ***********************************************************************
|
|
PROMPT * S T A L E S T A T I S T I C S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Table Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
PROMPT * - Partition Name = '&&partition_name'
|
|
PROMPT * - SubPartition Name = '&&subpartition_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT ***********************************************************************
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************
|
|
PROMPT * Stale Table Statistics
|
|
PROMPT ***********************************
|
|
PROMPT
|
|
|
|
COLUMN owner HEADING "Owner" FORMAT a20
|
|
COLUMN table_name HEADING "TableName" FORMAT a60
|
|
COLUMN index_name HEADING "IndexName" FORMAT a60
|
|
COLUMN partition_name HEADING "Partition" FORMAT a25
|
|
COLUMN partition_position HEADING "Part|Pos" FORMAT 999
|
|
COLUMN subpartition_name HEADING "Sub-Parition" FORMAT a25
|
|
COLUMN subpartition_position HEADING "Sub|Part|Pos" FORMAT 999
|
|
COLUMN last_analyzed HEADING "LastAnalyzed" FORMAT a18
|
|
COLUMN stattype_locked HEADING "Lock" FORMAT a5
|
|
COLUMN global_stats HEADING "Global|Stat" FORMAT a5
|
|
COLUMN user_stats HEADING "User|Stat" FORMAT a4
|
|
COLUMN stale_stats HEADING "Stale|Stat" FORMAT a5
|
|
COLUMN sample_size HEADING "Sample|Size" FORMAT 999,999,999
|
|
COLUMN num_rows HEADING "RowCount" FORMAT 99,999,999,999
|
|
COLUMN blocks HEADING "Blocks" FORMAT 999,999,999
|
|
COLUMN empty_blocks HEADING "Empty|Blocks" FORMAT 99,999,999
|
|
COLUMN avg_space HEADING "Avg|Space" FORMAT 99,999,999
|
|
COLUMN chain_cnt HEADING "Chain|Count" FORMAT 99,999,999
|
|
COLUMN avg_row_len HEADING "Avg|Row|Length" FORMAT 9999
|
|
|
|
|
|
select /*+ RULE */
|
|
s.owner || '.' || s.table_name
|
|
|| NVL2(s.partition_name,':' || s.partition_name, '')
|
|
|| NVL2(s.subpartition_name,':' || s.subpartition_name, '')
|
|
table_name
|
|
, s.stattype_locked
|
|
, s.global_stats
|
|
, s.user_stats
|
|
, s.stale_stats
|
|
, to_char(s.last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
|
|
, s.sample_size
|
|
, s.num_rows
|
|
, s.blocks
|
|
, s.empty_blocks
|
|
, s.avg_space
|
|
, s.chain_cnt
|
|
, s.avg_row_len
|
|
FROM dba_tab_statistics s
|
|
JOIN dba_tables t ON t.owner = s.owner
|
|
AND t.table_name = s.table_name
|
|
AND t.temporary = 'N'
|
|
WHERE s.stale_stats = 'YES'
|
|
AND s.owner NOT IN ('SYS','XDB')
|
|
AND s.table_name NOT LIKE 'BIN$%'
|
|
AND s.owner LIKE upper('&&owner')
|
|
AND s.table_name LIKE upper('&&table_name')
|
|
AND NVL(s.partition_name,'%') LIKE upper('&&partition_name') ESCAPE '\'
|
|
AND NVL(s.subpartition_name,'%') LIKE upper('&&subpartition_name') ESCAPE '\'
|
|
AND NVL(DECODE(s.object_type
|
|
,'PARTITION','TABLE PARTITION'
|
|
,'SUBPARTITION','TABLE SUBPARTITION'
|
|
,s.object_type),'%') LIKE upper('&&object_type') ESCAPE '\'
|
|
ORDER BY s.owner
|
|
, s.table_name
|
|
-- , s.partition_position
|
|
-- , s.subpartition_position
|
|
/
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************
|
|
PROMPT * Stale Index Statistics
|
|
PROMPT ***********************************
|
|
|
|
|
|
COLUMN table_name HEADING "TableName" FORMAT a30
|
|
COLUMN index_name HEADING "IndexName" FORMAT a65
|
|
COLUMN distinct_keys HEADING "DistinctKeys" FORMAT 99,999,999,999
|
|
COLUMN blevel HEADING "BLevel" FORMAT 99999
|
|
COLUMN leaf_blocks HEADING "Leaf|Blocks" FORMAT 99,999,999
|
|
COLUMN clustering_factor HEADING "Clustering|Factor" FORMAT 99,999,999,999
|
|
COLUMN avg_leaf_blocks_per_key HEADING "Avg|LeafBlocks|PerKey" FORMAT 999,999,999
|
|
COLUMN avg_data_blocks_per_key HEADING "Avg|DataBlocks|PerKey" FORMAT 999,999,999
|
|
|
|
select /*+ RULE */
|
|
s.owner || '.' || s.index_name
|
|
|| NVL2(s.partition_name,':' || s.partition_name, '')
|
|
|| NVL2(s.subpartition_name,':' || s.subpartition_name, '')
|
|
index_name
|
|
, s.stattype_locked
|
|
, s.global_stats
|
|
, s.user_stats
|
|
, s.stale_stats
|
|
, to_char(s.last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
|
|
, s.sample_size
|
|
, s.blevel
|
|
, s.num_rows
|
|
, s.leaf_blocks
|
|
, s.clustering_factor
|
|
FROM dba_ind_statistics s
|
|
JOIN dba_indexes i ON i.owner = s.owner
|
|
AND i.index_name = s.index_name
|
|
AND i.temporary = 'N'
|
|
WHERE s.stale_stats = 'YES'
|
|
AND s.owner NOT IN ('SYS','XDB')
|
|
AND s.table_name NOT LIKE 'BIN$%'
|
|
AND s.owner LIKE upper('&&owner')
|
|
AND s.table_name LIKE upper('&&table_name')
|
|
AND NVL(s.partition_name,'%') LIKE upper('&&partition_name') ESCAPE '\'
|
|
AND NVL(s.subpartition_name,'%') LIKE upper('&&subpartition_name') ESCAPE '\'
|
|
AND NVL(DECODE(s.object_type
|
|
,'PARTITION','INDEX PARTITION'
|
|
,'SUBPARTITION','INDEX SUBPARTITION'
|
|
,s.object_type),'%') LIKE upper('&&object_type') ESCAPE '\'
|
|
ORDER BY s.owner
|
|
, s.table_name
|
|
, s.index_name
|
|
-- , partition_position
|
|
-- , subpartition_position
|
|
/
|
|
|
|
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE partition_name
|
|
UNDEFINE subpartition_name
|
|
UNDEFINE object_type
|
|
|
|
|
|
@@footer
|