Re: Gathering Statistics / preventing timeouts

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 13 Nov 2006 05:36:14 -0800
Message-ID: <1163424974.680737.305440@e3g2000cwe.googlegroups.com>

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

> > > > > 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;

I have seen the "ORA-00054 resource busy and acquired with NOWAIT specified" error, which in 10g can be generated by a script that drops an table constraint and then immediately tries to create a new table constraint on the same table. I have also seen the error when performing an ad hoc UPDATE in one session without a commit, and trying to modify that same data in another session. If this error was encountered on a freshly built test database, it is possible that the database tables were still undergoing changes due to an import.

Looking over the above script, DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING is depreciated in 10g R2 - you are still using 9.2.x. The effects of this command may still be making changes when the DBMS_STATS.GATHER_SCHEMA_STATS is executed. You may only need to execute this command once (not confirmed).

You are gathering system statistics for 12 hours, and you are planning to do this once a week. I suggest that you reconsider. System statistics should be somewhat stable - collect them once when the system is under normal load, and then don't collect them again until a change is made to the hardware of the system. On my system: SELECT
  *
FROM
  SYS.AUX_STATS$;

SNAME          PNAME       PVAL1  PVAL2
SYSSTATS_INFO  STATUS             COMPLETED
SYSSTATS_INFO  DSTART             04-24-2006 14:35
SYSSTATS_INFO  DSTOP              04-24-2006 14:55
SYSSTATS_INFO  FLAGS          0
SYSSTATS_MAIN  CPUSPEEDNW  1077.922078
SYSSTATS_MAIN  IOSEEKTIM     10
SYSSTATS_MAIN  IOTFRSPEED  4096
SYSSTATS_MAIN  SREADTIM	      2.947
SYSSTATS_MAIN  MREADTIM	      7.349
SYSSTATS_MAIN  CPUSPEED	   1411
SYSSTATS_MAIN  MBRC           8
SYSSTATS_MAIN  MAXTHR     15360

SYSSTATS_MAIN SLAVETHR You might be able to tell that I collected system statistics once, on April 24, 2006 for 20 minutes. I wouldn't expect the CPU speed to change much, nor the amount of time to read a block from the hard drive, so I have not collected system statistics since that time.

In your script, "dbms_stats.gather_schema_stats(ownname => 'FIP', options => 'GATHER
 AUTO')" does not appear to be gathering index statistics, which would require the cascade option (unless this is covered by the GATHER AUTO option).

If this schema is reasonably small, try replacing your entire script with this:
begin
dbms_stats.gather_schema_stats(ownname=> 'FIP', CASCADE=> true); end;
/

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Nov 13 2006 - 07:36:14 CST