Re: Gathering Statistics / preventing timeouts

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 10 Nov 2006 04:15:59 -0800
Message-ID: <1163160959.217355.87980@h48g2000cwc.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

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 Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Nov 10 2006 - 06:15:59 CST