Re: Gathering Statistics / preventing timeouts

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 12 Nov 2006 23:58:36 -0800
Message-ID: <1163404716.596597.190800@f16g2000cwb.googlegroups.com>

Charles Hooper wrote:
> 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

>

> That is much more specific, and likely would have given Sybrandb
> something to work with.

>

> Quite possibly what happened is that a user is using a program that
> locks a row in the table or even the entire table for an update and
> then never releases the lock. This can happen in Microsoft Access
> during an edit, and in the data access COM objects that plug into
> various programs, which allow direct editing of table data. Try this
> SQL statement to see the locks:
> SELECT
> *
> FROM
> V$LOCKED_OBJECT;

>

Charles, thank you.
The strange thing is that I was operating on our test DB with my test schema and it seems highly unlikely that any other session operated on it during my tests. (read: I'm not 100% sure since I didn't double check - maybe there was a runaway job or something.)

The reason why I was so vague in my first post is that I was not able to reproduce the issue although it happened 4 or 5 times during trying out the stat gathering.

The strange thing is also that I tried as a test: sess1: Lock table xy
sess2: gather statistics for the schema of xy = dbms_stats.gather_schema_stats(ownname => 'FIP', options => 'GATHER');

And this did not give me any error.

So part of the reason I was posting was to see if there may be a general thing to look out for when statistic gathering fails (due to some locking/timeout issue).

thanks,
Martin Received on Mon Nov 13 2006 - 01:58:36 CST