@@header /* * * Author : Vishal Gupta * Purpose : Display table/index where rowcount has changed by more then specified threshold * Parameters : 1 - OWNER (% - wildchar, \ - escape char) * 2 - Object Name (% - wildchar, \ - escape char) * 3 - Partition Name (% - wildchar, \ - escape char) * 4 - Object Type (% - wildchar, \ - escape char) * 5 - Row Count Percent Threshold (Default Value 10) * 6 - Where Clause * * * Revision History: * =================== * Date Author Description * --------- ------------ ----------------------------------------- * 03-Jul-15 Vishal Gupta Created * */ /************************************ * INPUT PARAMETERS ************************************/ UNDEFINE owner UNDEFINE object_name UNDEFINE subobject_name UNDEFINE object_type UNDEFINE diff_rowcnt_percent UNDEFINE WHERECLAUSE DEFINE owner="&&1" DEFINE object_name="&&2" DEFINE subobject_name="&&3" DEFINE object_type="&&4" DEFINE diff_rowcnt_percent="&&5" DEFINE WHERECLAUSE="&&6" 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 COLUMN _diff_rowcnt_percent NEW_VALUE diff_rowcnt_percent 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" , DECODE('&&diff_rowcnt_percent','','10','&&diff_rowcnt_percent') "_diff_rowcnt_percent" FROM DUAL ; set term on PROMPT PROMPT *********************************************************************** PROMPT * S T A T I S T I C S C H A N G E PROMPT * PROMPT * Input Parameters PROMPT * - Object Owner = '&&owner' PROMPT * - Object Name = '&&object_name' PROMPT * - SubObject Name = '&&subobject_name' PROMPT * - Object Type = '&&object_type' PROMPT * - Diff Row % = '&&diff_rowcnt_percent' PROMPT * - Where Clause = '&&WHERECLAUSE' PROMPT *********************************************************************** COLUMN object_name HEADING "ObjectName" FORMAT a60 COLUMN subobject_name HEADING "SubObjectName" FORMAT a30 COLUMN object_type HEADING "Object|Type" FORMAT a18 COLUMN ANALYZETIME HEADING "AnalyzeTime" FORMAT a18 COLUMN prev_ANALYZETIME HEADING "Prev|AnalyzeTime" FORMAT a18 COLUMN rowcnt HEADING "RowCount" FORMAT 999,999,999,999 COLUMN prev_rowcnt HEADING "Prev|RowCount" FORMAT 999,999,999,999 COLUMN diff_rowcnt HEADING "Diff|RowCount" FORMAT 999,999,999,999 COLUMN diff_rowcnt_percent HEADING "Diff|Row|(%)" FORMAT 990.99 COLUMN SizeMB HEADING "Size|(MB)" FORMAT 9,999,999 COLUMN prev_SizeMB HEADING "Prev|Size|(MB)" FORMAT 9,999,999 COLUMN diff_SizeMB HEADING "Diff|Size|(MB)" FORMAT 9,999,999 WITH last_stat as ( select /*+ NO_MERGE MATERIALIZE */ obj# , max(analyzetime) analyzetime from sys.wri$_optstat_tab_history GROUP BY obj# ) , stats as ( SELECT /*+ NO_MERGE MATERIALIZE */ obj# , analyzetime prev_analyzetime , rowcnt prev_rowcnt , blkcnt prev_blkcnt FROM sys.wri$_optstat_tab_history h ) select o.owner || '.' || o.object_name || NVL2(o.subobject_name,':' || o.subobject_name, '') object_name , o.object_type object_type , ROUND((NVL(tsp.num_rows,NVL(tp.num_rows,t.num_rows)) - s.prev_rowcnt)/s.prev_rowcnt * 100, 2) diff_rowcnt_percent , NVL(tsp.num_rows,NVL(tp.num_rows,t.num_rows)) - s.prev_rowcnt diff_rowcnt , ((NVL(tsp.blocks,NVL(tp.blocks,t.blocks)) - s.prev_blkcnt) * tbs.block_size) /1024/1024 diff_SizeMB , TO_CHAR(NVL(tsp.last_analyzed,NVL(tp.last_analyzed,t.last_analyzed)),'DD-MON-YY HH24:MI:SS') analyzetime , NVL(tsp.num_rows,NVL(tp.num_rows,t.num_rows)) rowcnt , (NVL(tsp.blocks,NVL(tp.blocks,t.blocks)) * tbs.block_size) /1024/1024 SizeMB , TO_CHAR(s.prev_analyzetime,'DD-MON-YY HH24:MI:SS') prev_analyzetime , s.prev_rowcnt prev_rowcnt , (s.prev_blkcnt * tbs.block_size) /1024/1024 prev_SizeMB FROM last_stat ls JOIN stats s ON ls.obj# = s.obj# AND s.prev_analyzetime = ls.analyzetime JOIN dba_objects o ON ls.obj# = o.object_id JOIN dba_tables t ON t.owner = o.owner AND t.table_name = o.object_name AND o.object_type = 'TABLE' LEFT OUTER JOIN dba_tab_partitions tp ON tp.table_owner = o.owner AND tp.table_name = o.object_name AND tp.partition_name = o.subobject_name AND o.object_type = 'TABLE PARTITION' LEFT OUTER JOIN dba_tab_subpartitions tsp ON tsp.table_owner = o.owner AND tsp.table_name = o.object_name AND tsp.subpartition_name = o.subobject_name AND o.object_type = 'TABLE SUBPARTITION' JOIN dba_tablespaces tbs ON tbs.tablespace_name = NVL(tsp.tablespace_name,NVL(tp.tablespace_name,t.tablespace_name)) WHERE 1=1 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 '\' AND ABS(ROUND((NVL(tsp.num_rows,NVL(tp.num_rows,t.num_rows)) - s.prev_rowcnt)/s.prev_rowcnt * 100, 2)) >= &&diff_rowcnt_percent AND NVL(tsp.num_rows,NVL(tp.num_rows,t.num_rows)) < s.prev_rowcnt &&WHERECLAUSE UNION ALL SELECT * FROM ( WITH last_stat as ( select /*+ NO_MERGE MATERIALIZE */ obj# , max(analyzetime) analyzetime from sys.wri$_optstat_ind_history GROUP BY obj# ) , stats as ( SELECT /*+ NO_MERGE MATERIALIZE */ obj# , analyzetime prev_analyzetime , rowcnt prev_rowcnt , leafcnt prev_leafcnt FROM sys.wri$_optstat_ind_history h ) select o.owner || '.' || o.object_name || NVL2(o.subobject_name,':' || o.subobject_name, '') object_name , o.object_type object_type , ROUND((NVL(isp.num_rows,NVL(ip.num_rows,i.num_rows)) - s.prev_rowcnt)/s.prev_rowcnt * 100, 2) diff_rowcnt_percent , NVL(isp.num_rows,NVL(ip.num_rows,i.num_rows)) - s.prev_rowcnt diff_rowcnt , ((NVL(isp.leaf_blocks,NVL(ip.leaf_blocks,i.leaf_blocks)) - s.prev_leafcnt) * tbs.block_size) /1024/1024 diff_SizeMB , TO_CHAR(NVL(isp.last_analyzed,NVL(ip.last_analyzed,i.last_analyzed)),'DD-MON-YY HH24:MI:SS') analyzetime , NVL(isp.num_rows,NVL(ip.num_rows,i.num_rows)) rowcnt , (NVL(isp.leaf_blocks,NVL(ip.leaf_blocks,i.leaf_blocks)) * tbs.block_size) /1024/1024 SizeMB , TO_CHAR(s.prev_analyzetime,'DD-MON-YY HH24:MI:SS') prev_analyzetime , s.prev_rowcnt prev_rowcnt , (s.prev_leafcnt * tbs.block_size) /1024/1024 prev_SizeMB FROM last_stat ls JOIN stats s ON ls.obj# = s.obj# AND s.prev_analyzetime = ls.analyzetime JOIN dba_objects o ON ls.obj# = o.object_id JOIN dba_indexes i ON i.owner = o.owner AND i.index_name = o.object_name AND o.object_type = 'INDEX' LEFT OUTER JOIN dba_ind_partitions ip ON ip.index_owner = o.owner AND ip.index_name = o.object_name AND ip.partition_name = o.subobject_name AND o.object_type = 'INDEX PARTITION' LEFT OUTER JOIN dba_ind_subpartitions isp ON isp.index_owner = o.owner AND isp.index_name = o.object_name AND isp.subpartition_name = o.subobject_name AND o.object_type = 'INDEX SUBPARTITION' JOIN dba_tablespaces tbs ON tbs.tablespace_name = NVL(isp.tablespace_name,NVL(ip.tablespace_name,i.tablespace_name)) WHERE 1=1 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 '\' AND ABS(ROUND((NVL(isp.num_rows,NVL(ip.num_rows,i.num_rows)) - s.prev_rowcnt)/s.prev_rowcnt * 100, 2)) >= &&diff_rowcnt_percent AND NVL(isp.num_rows,NVL(ip.num_rows,i.num_rows)) < s.prev_rowcnt &&WHERECLAUSE ) ORDER BY diff_rowcnt_percent asc , diff_rowcnt asc ; @@footer