107 lines
4.2 KiB
SQL
107 lines
4.2 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Statistics of a table (including part, sub-par, ind, ind-part, ind-subpart)
|
|
* Parameters : 1 - OWNER (% - wildcard, \ - escape char, default is '%')
|
|
* 2 - Table Name (% - wildcard, \ - escape char, default is '%')
|
|
* 3 - Column Name (% - wildcard, \ - escape char, default is '%')
|
|
* 4 - days (% - wildcard, \ - escape char, default is '7')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 29-Dec-16 Vishal Gupta Change order by clause
|
|
* 13-Feb-14 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
UNDEFINE column_name
|
|
UNDEFINE days
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE column_name="&&3"
|
|
DEFINE days="&&4"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _column_name NEW_VALUE column_name NOPRINT
|
|
COLUMN _days NEW_VALUE days 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('&&column_name','','%',UPPER('&&column_name')) "_column_name"
|
|
, DECODE('&&days','','7',UPPER('&&days')) "_days"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * C O L U M N S T A T I S T I C S H I S T O R Y
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Table Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
PROMPT * - Column Name = '&&column_name'
|
|
PROMPT * - Days Ago = '&&days'
|
|
PROMPT ***********************************************************************
|
|
|
|
COLUMN object_name HEADING "TableName" FORMAT a45
|
|
COLUMN Object_type HEADING "Object|Type" FORMAT a8
|
|
COLUMN column_name HEADING "ColumnName" FORMAT a25
|
|
COLUMN global_stats HEADING "G|l|o|b|a|l" FORMAT a1 TRUNCATE
|
|
COLUMN last_analyzed HEADING "LastAnalyzed" FORMAT a18
|
|
COLUMN avgcln HEADING "Avg|Col|Len" FORMAT 99999
|
|
COLUMN distcnt HEADING "NumDistinct" FORMAT 9,999,999,999
|
|
COLUMN null_cnt HEADING "NumNulls" FORMAT 9,999,999,999
|
|
COLUMN density HEADING "Density" FORMAT 9.999999999999999
|
|
COLUMN lowval HEADING "Low Value" FORMAT a16
|
|
COLUMN hival HEADING "High Value" FORMAT a16
|
|
|
|
SELECT c.owner || '.' || c.table_name|| NVL2(o.subobject_name,':' ||o.subobject_name,'') object_name
|
|
, c.column_name
|
|
, TO_CHAR(s.savtime,'DD-MON-YY HH24:Mi:SS') last_analyzed
|
|
, s.avgcln
|
|
, s.null_cnt
|
|
, s.distcnt
|
|
, s.density
|
|
--, s.lowval
|
|
--, s.hival
|
|
--, s.minimum
|
|
--, s.maximum
|
|
FROM dba_tab_columns c
|
|
JOIN dba_objects o ON c.owner = o.owner AND c.table_name = o.object_name
|
|
JOIN sys.wri$_optstat_histhead_history s ON s.obj# = o.object_id AND s.intcol# = c.column_id
|
|
WHERE c.owner LIKE '&&owner' ESCAPE '\'
|
|
AND c.table_name LIKE '&&table_name' ESCAPE '\'
|
|
AND c.column_name LIKE '&&column_name' ESCAPE '\'
|
|
AND s.savtime > sysdate - &&days
|
|
ORDER BY object_name
|
|
, c.column_name
|
|
, s.savtime desc nulls last
|
|
/
|
|
|
|
|
|
|
|
|
|
@@footer
|