Find tables with stale statistics
                                                 Last update (2008-02-29 10:22:08)
                                                                                                                   Date added (2008-02-29 10:20:42)

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;
Reviews
Categories
Filters
Search