Tuesday, January 20, 2009

Automate schema statistics gathering on 9i

First enable monitoring on your schema
exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('SCHEMA',TRUE);

Then for first time only take statistics on all objects.
exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA',estimate_percent => 5,cascade => TRUE);

After first time you can use the gather stale option to take statistics only for stale objects.

exec dbms_stats.gather_schema_stats(ownname => 'SCHEMA',estimate_percent => 5,cascade => TRUE,options => 'GATHER STALE');

No comments:

Post a Comment