Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Gathering Statistics / preventing timeouts
On Nov 10, 10:31 am, "Martin T." <bilbothebagginsb..._at_freenet.de>
wrote:
> 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
Quoting
> 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?
This would start with you providing a crystal ball to interpret 'a timeout', being a deliberately vague message, assuming other people are experiencing the same 'timeouts', whatever they may be. At least I don't experience 'timeouts' from 'the gathering procedure' (you have various in your code, haven't you)
-- Sybrand Bakker Senior Oracle DBAReceived on Fri Nov 10 2006 - 03:55:41 CST