Re: Monitoring automatic analysis of stale tables

Rick Denoire wrote:
>
> Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote:
>
> >On Fri, 04 Apr 2003 23:53:48 +0200, Rick Denoire
> ><100.17706_at_germanynet.de> wrote:
>
> >>I have put a script as a cron job that runs every 4 hours looking at
> >>stale tables and tables with empty statistics to be analyzed.
>
> >There should be no need for that, or you must have very volatile
> >tables.
>
> Yes, they are very volatile. It is a kind of "war" between developers
> and me (the Admin). They very often truncate tables, refresh
> materialized views, delete large numbers of records in a batch job,
> without even considering the consequences. Some of them never heard
> about "statistical data".
>
> >Running a job that frequently usually ends up in havoc.
>
> I would appreciate if you elaborate on potential causes for problems,
> based on your experience. (My experience is small, measured in time).
>
> >In a relatively normal situation analysis once per week should be
> >sufficient.
>
> That is what I have done until recent times. But then, everything was
> analyzed. On this machine (Sun E3500, four 400 MHz SparcII CPUs,
> Oracle 8.1.7, Solaris 2.7) it takes almost the whole weekend. The DB
> is about 180 GB in size (sum of all sizes of datafiles including RBS
> and TEMP).
>
> >Other than that various dbms_stats routines can return the tables
> >analyzed in a pl/sql record. I don't have the documentation ready, so
> >you probably should look that up yourself.
>
> Yeah, I went after that, but could not find the piece of info. But now
> I am sure that it is possible to track what Oracle actually picks for
> analysis. I will have to search again.
>
> Bye
> Rick Denoire

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm#1003995

has an objlist parameter which will be populated with 'List of objects found to be stale or empty'

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"