Summary
Find tables with obviously stale statistics, based on size. For example for objects with size bigger than 20MB and statistics with percentage difference > +-30%. If the table realy has 1.000.000 rows end estimated statistics show 500.000 rows then this a candicate for this script.
SELECT
'execute dbms_stats.gather_table_stats(ownname=>' || '''' || user_name || '''' || ', tabname=>'
|| '''' || table_name || '''' || ' , estimate_percent=>10 ' ||', cascade=>true, degree=>8);' dbms_stats_sql,
analyzetime, ROUND((analyzed_size * :block_size)/(1024*1024),2) analyzed_mb, ROUND((current_size * :block_size)/(1024*1024),2) current_mb,
CHANGE || '%' CHANGE
FROM (SELECT /*+ ordered */
u.NAME user_name, o.NAME table_name, analyzetime,
1 + s.GROUPS + t.blkcnt + t.empcnt analyzed_size,
s.blocks current_size,
ROUND(100 * (s.blocks - 1 - s.GROUPS - t.blkcnt - t.empcnt) /
(1 + s.GROUPS + t.blkcnt + t.empcnt),2) CHANGE
FROM
sys.TS$ ts, sys.SEG$ s, sys.TAB$ t, sys.OBJ$ o, sys.USER$ u
WHERE
ts.bitmapped = 0 AND
s.ts# = ts.ts# AND
s.TYPE# = 5 AND
t.ts# = s.ts# AND
t.FILE# = s.FILE# AND
t.BLOCK# = s.BLOCK# AND
ABS(s.blocks - 1 - s.GROUPS - t.blkcnt - t.empcnt) > 4 AND
o.obj# = t.obj# AND
u.USER# = o.owner#
UNION ALL
SELECT /*+ ordered */
u.NAME user_name, o.NAME table_name, analyzetime,
1 + s.GROUPS + t.blkcnt + t.empcnt analyzed_size,
sys.dbms_space_admin.segment_number_blocks(
s.ts#, s.FILE#, s.BLOCK#, s.TYPE#, s.cachehint,
NVL(s.spare1,0), t.dataobj#, s.blocks
) current_size,
ROUND(100 * (sys.dbms_space_admin.segment_number_blocks(s.ts#, s.FILE#, s.BLOCK#, s.TYPE#,
s.cachehint, NVL(s.spare1,0), t.dataobj#, s.blocks) - 1 - s.GROUPS - t.blkcnt - t.empcnt) /
(1 + s.GROUPS + t.blkcnt + t.empcnt),2) CHANGE
FROM
sys.TS$ ts, sys.SEG$ s, sys.TAB$ t, sys.OBJ$ o, sys.USER$ u
WHERE
ts.bitmapped > 0 AND
s.ts# = ts.ts# AND
s.TYPE# = 5 AND
t.ts# = s.ts# AND
t.FILE# = s.FILE# AND
t.BLOCK# = s.BLOCK# AND
t.blkcnt IS NOT NULL AND
ABS(sys.dbms_space_admin.segment_number_blocks(
s.ts#, s.FILE#, s.BLOCK#, s.TYPE#, s.cachehint,
NVL(s.spare1,0), t.dataobj#, s.blocks
) - 1 - s.GROUPS - t.blkcnt - t.empcnt) > 4 AND
o.obj# = t.obj# AND
u.USER# = o.owner#
ORDER BY 4)
WHERE (CHANGE > 30 OR CHANGE < -30) --(difference between realy size and statistics +-30%)
AND ROUND((current_size * :block_size)/(1024*1024),2) > 20 --(object size bigger than 20MB)
AND user_name = 'SPOTLIGHT'
ORDER BY CHANGE DESC;
