131 lines
5.5 KiB
SQL
131 lines
5.5 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 ***************************************************
|
|
PROMPT * Table Modifications
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
PROMPT * - Partition Name = '&&partition_name'
|
|
PROMPT * - SubPartition Name = '&&subpartition_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT ***************************************************
|
|
PROMPT
|
|
|
|
COLUMN table_owner HEADING "Owner" FORMAT a20
|
|
COLUMN table_name HEADING "TableName" FORMAT a60
|
|
COLUMN partition_name HEADING "Partition" FORMAT a25
|
|
COLUMN subpartition_name HEADING "Sub-Parition" FORMAT a25
|
|
COLUMN last_analyzed HEADING "LastAnalyzed" FORMAT a18
|
|
COLUMN last_modification HEADING "LastModification" FORMAT a18
|
|
COLUMN num_rows HEADING "NumberOfRows" FORMAT 999,999,999,999
|
|
COLUMN percent_modification HEADING "Modification|(%)" FORMAT 999,999,999.99
|
|
COLUMN modifications_total HEADING "Modification|Total|(#)" FORMAT 999,999,999,999
|
|
COLUMN inserts HEADING "Inserts|(#)" FORMAT 99,999,999
|
|
COLUMN updates HEADING "Updates|(#)" FORMAT 99,999,999
|
|
COLUMN deletes HEADING "Deletes|(#)" FORMAT 999,999,999,999
|
|
COLUMN truncated HEADING "Truncated" FORMAT a5
|
|
COLUMN drop_segments HEADING "Drop|Segments|(#)" FORMAT 999
|
|
|
|
|
|
|
|
SELECT
|
|
m.table_owner || '.' || m.table_name
|
|
|| NVL2(m.partition_name,':' || m.partition_name, '')
|
|
|| NVL2(m.subpartition_name,':' || m.subpartition_name, '')
|
|
table_name
|
|
--m.table_owner
|
|
--, m.table_name
|
|
--, m.partition_name
|
|
--, m.subpartition_name
|
|
, TO_CHAR(NVL(s.last_analyzed,NVL(p.last_analyzed,t.last_analyzed)),'DD-MON-YY HH24:MI:SS') last_analyzed
|
|
, TO_CHAR(m.timestamp,'DD-MON-YY HH24:MI:SS') last_modification
|
|
, NVL(s.num_rows,NVL(p.num_rows,t.num_rows)) num_rows
|
|
, ROUND((m.inserts + m.updates + m.deletes)
|
|
/ DECODE(NVL(s.num_rows,NVL(p.num_rows,NVL(t.num_rows,0)))
|
|
, 0 ,m.inserts + m.updates + m.deletes
|
|
, DECODE(NVL(s.num_rows,NVL(p.num_rows,NVL(t.num_rows,1)))
|
|
, 0 , 1
|
|
, NVL(s.num_rows,NVL(p.num_rows,NVL(t.num_rows,1)))
|
|
)
|
|
)
|
|
* 100
|
|
, 2) percent_modification
|
|
, m.inserts + m.updates + m.deletes modifications_total
|
|
, m.inserts
|
|
, m.updates
|
|
, m.deletes
|
|
, m.truncated
|
|
, m.drop_segments
|
|
FROM dba_tab_modifications m
|
|
LEFT OUTER JOIN dba_tables t ON t.owner = m.table_owner AND t.table_name = m.table_name
|
|
LEFT OUTER JOIN dba_tab_partitions p ON p.table_owner = m.table_owner AND p.table_name = m.table_name AND p.partition_name = m.partition_name
|
|
LEFT OUTER JOIN dba_tab_subpartitions s ON s.table_owner = m.table_owner AND s.table_name = m.table_name
|
|
AND s.partition_name = m.partition_name AND s.subpartition_name = m.subpartition_name
|
|
WHERE m.inserts + m.updates + m.deletes > 0
|
|
AND m.table_owner LIKE '&&owner'
|
|
AND m.table_name LIKE '&&table_name'
|
|
AND NVL(m.partition_name,'%') LIKE '&&partition_name' --ESCAPE '\'
|
|
AND NVL(m.subpartition_name,'%') LIKE '&&subpartition_name' --ESCAPE '\'
|
|
ORDER BY percent_modification desc nulls last
|
|
;
|
|
|
|
|
|
|
|
@@footer
|