Charles Hooper wrote:
> 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;
> /
>
Thanks a lot! Esp. for the tips how to make the script behave better.
cheers,
Martin
Received on Mon Nov 13 2006 - 11:43:03 CST
