274 lines
10 KiB
MySQL
274 lines
10 KiB
MySQL
|
/*
|
||
|
|
*
|
||
|
|
* Author : Vishal Gupta
|
||
|
|
* Purpose : Display table statistics history
|
||
|
|
* Parameters : 1 - OWNER (% - wildchar, \ - escape char)
|
||
|
|
* 2 - Object Name (% - wildchar, \ - escape char)
|
||
|
|
* 3 - Partition Name (% - wildchar, \ - escape char)
|
||
|
|
* 5 - Object Type (% - wildchar, \ - escape char)
|
||
|
|
*
|
||
|
|
*
|
||
|
|
* Revision History:
|
||
|
|
* ===================
|
||
|
|
* Date Author Description
|
||
|
|
* --------- ------------ -----------------------------------------
|
||
|
|
* 08-Jul-15 Vishal Gupta Removed savetime column from output
|
||
|
|
* 19-JUN-14 Vishal Gupta Updated AvgRowLength heading
|
||
|
|
* 28-FEB-14 Vishal Gupta Changed the ordering
|
||
|
|
* 02-Jul-12 Vishal Gupta Added partition, sub-partition and objecttype as input
|
||
|
|
* 30-Apr-12 Vishal Gupta Created
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
|
||
|
|
|
||
|
|
/************************************
|
||
|
|
* INPUT PARAMETERS
|
||
|
|
************************************/
|
||
|
|
UNDEFINE owner
|
||
|
|
UNDEFINE object_name
|
||
|
|
UNDEFINE subobject_name
|
||
|
|
UNDEFINE object_type
|
||
|
|
|
||
|
|
DEFINE owner="&&1"
|
||
|
|
DEFINE object_name="&&2"
|
||
|
|
DEFINE subobject_name="&&3"
|
||
|
|
DEFINE object_type="&&4"
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
||
|
|
COLUMN _object_name NEW_VALUE object_name NOPRINT
|
||
|
|
COLUMN _subobject_name NEW_VALUE subobject_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('&&object_name'),'','%',UPPER('&&object_name'))
|
||
|
|
END "_object_name"
|
||
|
|
, DECODE('&&subobject_name','','%','&&subobject_name') "_subobject_name"
|
||
|
|
, DECODE('&&object_type','','%','&&object_type') "_object_type"
|
||
|
|
FROM DUAL
|
||
|
|
;
|
||
|
|
set term on
|
||
|
|
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT ***********************************************************************
|
||
|
|
PROMPT * S T A T I S T I C S H I S T O R Y
|
||
|
|
PROMPT *
|
||
|
|
PROMPT * Input Parameters
|
||
|
|
PROMPT * - Object Owner = '&&owner'
|
||
|
|
PROMPT * - Object Name = '&&object_name'
|
||
|
|
PROMPT * - SubObject Name = '&&subobject_name'
|
||
|
|
PROMPT * - Object Type = '&&object_type'
|
||
|
|
PROMPT ***********************************************************************
|
||
|
|
|
||
|
|
COLUMN object_name HEADING "ObjectName" FORMAT a60
|
||
|
|
COLUMN subobject_name HEADING "SubObjectName" FORMAT a30
|
||
|
|
COLUMN object_type HEADING "Object|Type" FORMAT a18
|
||
|
|
COLUMN object_type_sort_order NOPRINT
|
||
|
|
COLUMN savtime HEADING "SaveTime" FORMAT a18
|
||
|
|
COLUMN ANALYZETIME HEADING "AnalyzeTime" FORMAT a18
|
||
|
|
COLUMN samplesize HEADING "SampleSize" FORMAT 999,999,999,999
|
||
|
|
COLUMN rowcnt HEADING "RowCount" FORMAT 999,999,999,999
|
||
|
|
COLUMN blkcnt HEADING "BlockCount" FORMAT 999,999,999
|
||
|
|
COLUMN avgrln HEADING "Avg|Row|Length" FORMAT 999,999
|
||
|
|
COLUMN SizeMB HEADING "Size (MB)" FORMAT 9,999,999
|
||
|
|
|
||
|
|
select object_name
|
||
|
|
, object_type
|
||
|
|
, object_type_sort_order
|
||
|
|
--, to_char(savtime,'DD-MON-YY HH24:MI:SS') savtime
|
||
|
|
, to_char(ANALYZETIME,'DD-MON-YY HH24:MI:SS') ANALYZETIME
|
||
|
|
, SizeMB
|
||
|
|
, rowcnt
|
||
|
|
, avgrln
|
||
|
|
--, blkcnt
|
||
|
|
, samplesize
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
select o.owner || '.' || o.object_name
|
||
|
|
|| NVL2(o.subobject_name,':' || o.subobject_name, '')
|
||
|
|
object_name
|
||
|
|
, o.object_type
|
||
|
|
, DECODE (o.object_type
|
||
|
|
, 'TABLE' ,1
|
||
|
|
, 'TABLE PARTITION' ,2
|
||
|
|
, 'TABLE SUBPARTITION' ,3
|
||
|
|
, 'INDEX' ,4
|
||
|
|
, 'INDEX PARTITION' ,5
|
||
|
|
, 'INDEX SUBPARTITION' ,6
|
||
|
|
,9
|
||
|
|
) object_type_sort_order
|
||
|
|
, h.savtime
|
||
|
|
, h.ANALYZETIME
|
||
|
|
, h.samplesize
|
||
|
|
, h.rowcnt
|
||
|
|
, h.avgrln
|
||
|
|
, (h.blkcnt * p.value) /1024/1024 SizeMB
|
||
|
|
, h.blkcnt
|
||
|
|
FROM sys.wri$_optstat_tab_history h
|
||
|
|
, dba_objects o
|
||
|
|
, v$system_parameter p
|
||
|
|
WHERE h.obj# = o.object_id
|
||
|
|
AND p.name = 'db_block_size'
|
||
|
|
AND o.owner LIKE upper('&&owner') ESCAPE '\'
|
||
|
|
AND o.object_name LIKE upper('&&object_name') ESCAPE '\'
|
||
|
|
AND NVL(o.subobject_name,'%') LIKE upper('&&subobject_name') ESCAPE '\'
|
||
|
|
AND NVL(DECODE(o.object_type
|
||
|
|
,'PARTITION','TABLE PARTITION'
|
||
|
|
,'SUBPARTITION','TABLE SUBPARTITION'
|
||
|
|
,o.object_type),'%') LIKE upper('&&object_type') ESCAPE '\'
|
||
|
|
UNION
|
||
|
|
select s.owner || '.' || s.table_name
|
||
|
|
|| NVL2(s.partition_name,':' || s.partition_name, '')
|
||
|
|
object_name
|
||
|
|
, DECODE(s.object_type
|
||
|
|
,'PARTITION','TABLE PARTITION'
|
||
|
|
,'SUBPARTITION','TABLE SUBPARTITION'
|
||
|
|
,s.object_type) object_type
|
||
|
|
, DECODE (s.object_type
|
||
|
|
, 'TABLE' ,1
|
||
|
|
, 'TABLE PARTITION' ,2
|
||
|
|
, 'TABLE SUBPARTITION' ,3
|
||
|
|
, 'INDEX' ,4
|
||
|
|
, 'INDEX PARTITION' ,5
|
||
|
|
, 'INDEX SUBPARTITION' ,6
|
||
|
|
,9
|
||
|
|
) object_type_sort_order
|
||
|
|
, NULL savtime
|
||
|
|
, last_analyzed ANALYZETIME
|
||
|
|
, sample_size
|
||
|
|
, num_rows
|
||
|
|
, avg_row_len
|
||
|
|
, (s.blocks * p.value) /1024/1024 SizeMB
|
||
|
|
, s.blocks
|
||
|
|
FROM dba_tab_statistics s
|
||
|
|
, v$system_parameter p
|
||
|
|
WHERE p.name = 'db_block_size'
|
||
|
|
AND s.owner LIKE upper('&&owner') ESCAPE '\'
|
||
|
|
AND s.table_name LIKE upper('&&object_name') ESCAPE '\'
|
||
|
|
AND NVL(s.partition_name,'%') LIKE upper('&&subobject_name') ESCAPE '\'
|
||
|
|
AND NVL(DECODE(s.object_type
|
||
|
|
,'PARTITION','TABLE PARTITION'
|
||
|
|
,'SUBPARTITION','TABLE SUBPARTITION'
|
||
|
|
,s.object_type),'%') LIKE upper('&&object_type') ESCAPE '\'
|
||
|
|
) a
|
||
|
|
ORDER BY a.object_name
|
||
|
|
, a.object_type_sort_order
|
||
|
|
, a.savtime NULLS LAST
|
||
|
|
--, a.ANALYZETIME
|
||
|
|
/
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
COLUMN table_name HEADING "IndexName" FORMAT a40
|
||
|
|
COLUMN object_name HEADING "IndexName" FORMAT a50
|
||
|
|
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 object_name
|
||
|
|
, object_type
|
||
|
|
, object_type_sort_order
|
||
|
|
--, to_char(savtime,'DD-MON-YY HH24:MI:SS') savtime
|
||
|
|
, to_char(ANALYZETIME,'DD-MON-YY HH24:MI:SS') ANALYZETIME
|
||
|
|
, SizeMB
|
||
|
|
, rowcnt
|
||
|
|
, samplesize
|
||
|
|
, distinct_keys
|
||
|
|
, blevel
|
||
|
|
, clustering_factor
|
||
|
|
, leaf_blocks
|
||
|
|
, avg_leaf_blocks_per_key
|
||
|
|
, avg_data_blocks_per_key
|
||
|
|
FROM
|
||
|
|
(
|
||
|
|
select o.owner || '.' || o.object_name
|
||
|
|
|| NVL2(o.subobject_name,':' || o.subobject_name, '')
|
||
|
|
object_name
|
||
|
|
, o.object_type
|
||
|
|
, DECODE (o.object_type
|
||
|
|
, 'TABLE' ,1
|
||
|
|
, 'TABLE PARTITION' ,2
|
||
|
|
, 'TABLE SUBPARTITION' ,3
|
||
|
|
, 'INDEX' ,4
|
||
|
|
, 'INDEX PARTITION' ,5
|
||
|
|
, 'INDEX SUBPARTITION' ,6
|
||
|
|
,9
|
||
|
|
) object_type_sort_order
|
||
|
|
, h.savtime
|
||
|
|
, h.ANALYZETIME
|
||
|
|
, (h.leafcnt * p.value) /1024/1024 SizeMB
|
||
|
|
, h.rowcnt
|
||
|
|
, h.samplesize
|
||
|
|
, h.distkey distinct_keys
|
||
|
|
, h.blevel
|
||
|
|
, h.clufac clustering_factor
|
||
|
|
, h.leafcnt leaf_blocks
|
||
|
|
, h.lblkkey avg_leaf_blocks_per_key
|
||
|
|
, h.dblkkey avg_data_blocks_per_key
|
||
|
|
--, h.avgrln
|
||
|
|
FROM sys.wri$_optstat_ind_history h
|
||
|
|
, dba_objects o
|
||
|
|
, v$system_parameter p
|
||
|
|
WHERE h.obj# = o.object_id
|
||
|
|
AND p.name = 'db_block_size'
|
||
|
|
AND o.owner LIKE upper('&&owner') ESCAPE '\'
|
||
|
|
AND o.object_name LIKE upper('&&object_name') ESCAPE '\'
|
||
|
|
AND NVL(o.subobject_name,'%') LIKE upper('&&subobject_name') ESCAPE '\'
|
||
|
|
AND NVL(o.object_type,'%') LIKE upper('&&object_type') ESCAPE '\'
|
||
|
|
UNION ALL
|
||
|
|
select s.owner || '.' || s.index_name
|
||
|
|
|| NVL2(s.partition_name,':' || s.partition_name, '')
|
||
|
|
object_name
|
||
|
|
, DECODE(s.object_type
|
||
|
|
,'PARTITION','TABLE PARTITION'
|
||
|
|
,'SUBPARTITION','TABLE SUBPARTITION'
|
||
|
|
,s.object_type) object_type
|
||
|
|
, DECODE (s.object_type
|
||
|
|
, 'TABLE' ,1
|
||
|
|
, 'TABLE PARTITION' ,2
|
||
|
|
, 'TABLE SUBPARTITION' ,3
|
||
|
|
, 'INDEX' ,4
|
||
|
|
, 'INDEX PARTITION' ,5
|
||
|
|
, 'INDEX SUBPARTITION' ,6
|
||
|
|
,9
|
||
|
|
) object_type_sort_order
|
||
|
|
, null savtime
|
||
|
|
, s.last_analyzed ANALYZETIME
|
||
|
|
, ROUND((s.leaf_blocks * p.value) /1024/1024 ) SizeMB
|
||
|
|
, s.num_rows
|
||
|
|
, s.sample_size
|
||
|
|
, s.distinct_keys
|
||
|
|
, s.blevel
|
||
|
|
, s.clustering_factor
|
||
|
|
, s.leaf_blocks
|
||
|
|
, s.avg_leaf_blocks_per_key
|
||
|
|
, s.avg_data_blocks_per_key
|
||
|
|
FROM dba_ind_statistics s
|
||
|
|
, v$system_parameter p
|
||
|
|
WHERE p.name = 'db_block_size'
|
||
|
|
AND s.owner LIKE upper('&&owner') ESCAPE '\'
|
||
|
|
AND s.index_name LIKE upper('&&object_name') ESCAPE '\'
|
||
|
|
AND NVL(s.partition_name,'%') LIKE upper('&&subobject_name') ESCAPE '\'
|
||
|
|
AND NVL(DECODE(s.object_type
|
||
|
|
,'PARTITION','INDEX PARTITION'
|
||
|
|
,'SUBPARTITION','INDEX SUBPARTITION'
|
||
|
|
,s.object_type),'%') LIKE upper('&&object_type') ESCAPE '\'
|
||
|
|
) a
|
||
|
|
ORDER BY a.object_name
|
||
|
|
, a.object_type_sort_order
|
||
|
|
, a.savtime NULLS LAST
|
||
|
|
--, a.ANALYZETIME
|
||
|
|
/
|
||
|
|
|