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:
Post a Comment