Gathering Statistics / preventing timeouts

Hello.

Our (small) DBs (O 9i2) are running without regular statistics gathering at the moment and since some tests have shown that we can get a performance increase if we keep them up to date we are planning to run a weekly job that does some gathering.

One thing I have seen while testing is that I sometimes get a timeout error from the gathering procedure. (On a busy table.) Is there any way to prevent this? We don't really care if the statistic gathering takes 5 minutes (should be realistic for us) or if it takes 30 minutes because the gathering has to wait a bit.

I'm planning to run a job like this once a week: begin

• Session setting to ensure working procedures: execute immediate 'alter session set NLS_NUMERIC_CHARACTERS=''.,''';
• Start gathering system stats for next 12 hours: dbms_stats.gather_system_stats('interval', 12*60);
• Enable monitoring: DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING('FIP', TRUE);
• Gather schema statistics: dbms_stats.gather_schema_stats(ownname => 'FIP', options => 'GATHER AUTO'); end;

Any comments appreciated. Bash away :-)

cheers,
Martin Received on Fri Nov 10 2006 - 03:31:33 CST