Re: Monitoring automatic analysis of stale tables

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 Received on Sat Apr 05 2003 - 06:27:18 CST