Re: Gathering Statistics / preventing timeouts

From: sybrandb <sybrandb_at_gmail.com>
Date: 10 Nov 2006 09:38:40 -0800
Message-ID: <1163180320.755089.76740@m73g2000cwd.googlegroups.com>

Martin T. wrote:
> 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

The 'sigh' is fully unjustified!!
If you are not prepared to come up with details, in this case resulting in an obvious immediate answer, why are you rather wasting peoples time, instead of coming up with something suitable right away? Just FYI: Usenet is a *volunteer* business!!

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Fri Nov 10 2006 - 11:38:40 CST