Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Gathering Statistics / preventing timeouts

Re: Gathering Statistics / preventing timeouts

From: sybrandb <sybrandb_at_gmail.com>
Date: 10 Nov 2006 01:55:41 -0800
Message-ID: <1163152541.185367.322650@k70g2000cwa.googlegroups.com>

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 DBA
Received on Fri Nov 10 2006 - 03:55:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US