Tuesday, February 1, 2011

Incremental statistics

You should gather statistics on table level, not on partition level ("partname" should not be used). Oracle itself will determine which partitions are modified and updates global statistics scanning only modified partitions. 

In order to gether incremental statistics on a table :
1-) Set incremental statistic pref for the table
exec dbms_stats.set_table_prefs('MYSCHEMA','MYTABLE','INCREMENTAL' ,'TRUE') ;
2-) Chek if it is set (should return TRUE)
select dbms_stats.get_prefs('INCREMENTAL','MYSCHEMA','MYTABLE') from dual;
4-) Also check if PUBLISH attibute is true. default is true
select dbms_stats.get_prefs('PUBLISH','MYSCHEMA','MYTABLE') from dual;
3-) Gather statistics on a partition using GRANULARITY set to AUTO and AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT

Here is a query to check column level incremental statistics is enabled or not:


select o.name, c.name, decode(bitand(h.spare2,8),8 ,'yes','no') incremental
from sys.hist_head$ h,sys.obj$ o, sys.col$ c
where h.obj#=o.obj# 
and o.obj# = c.obj#
and h.intcol#=c.intcol#
and o.name='TDM_KOBI_NBSM_DAILY'
and o.subname is null;



For official documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats.htm (13.3.1.3 Statistics on Partitioned Objects)

Setting Global/Database prefs:
1-) In order to set database stat gathering preferences excluding oracle system tables:
exec DBMS_STATS.SET_DATABASE_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE 254');
2-) In order to set database stat gathering preferences globally:
exec DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE 254');
to see preference value:
select dbms_stats.get_prefs('METHOD_OPT') from dual;

Preferences:
AUTOSTATS_TARGET
CASCADE
DEGREE
ESTIMATE_PERCENT
METHOD_OPT
NO_INVALIDATE
GRANULARITY
PUBLISH
INCREMENTAL
STALE_PERCENT

No comments: