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: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 10 Nov 2006 03:43:17 -0800
Message-ID: <1163158997.355762.298510@h48g2000cwc.googlegroups.com>


sybrandb wrote:
> 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)
>

*sigh*
So you have never seen any timeout/locking related error message when gathering statistics. How nice for you.
Reason I was being vague is that I was not able to reliably reproduce it.

So here comes more vague:

Has anyone else experienced
ORA-00054 resource busy and acquire with NOWAIT specified or
ORA-04021 timeout occurred while waiting to lock object while using one of

dbms_stats.gather_schema_stats
dbms_stats.export_system_stats
dbms_stats.alter_schema_tab_monitoring

cheers,
Martin Received on Fri Nov 10 2006 - 05:43:17 CST

Original text of this message

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