RE: collecting statistics on table those have bulk inserts everyday..

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 12 Apr 2004 10:31:20 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FBF8@irvmbxw02>

Have you tried "alter table ... monitoring" and = dbms_stats.gather_schema_stats (... 'GATHER STALE' ...) (see Metalink = note Note:102334.1)

I seem to remember a discussion a few weeks ago on the list concerning = the practice of gathering statistics regularly. Some of the opinions = were:
- If you gather statistics on a regular basis, you will one day end up = with some execution plans that you will not like. Predictability of = execution plans is better than always trying to get the best execution = plan. (which seems to be the opinion in Metalink note 44961.1 ) - You should be able to gather statistics regularly, the CBO should make = better decisions with more recent information. Unless there are numerous = bugs in the CBO, gathering statistics regularly (e.g. once a week) is a = quick and easy way to implement it. If you are worried about ending up = with some bad execution plans, then export the statistics = (dbms_stats.export_schema_stats) before the analyze so that you can = restore them and have the CBO return to a "known" behaviour, or save = execution plans with stored outlines (Metalink note 67536.1)

I would incline to the second way of doing things, unless you have a lot = of time to spare to determine for each object the best time to analyze.

> -----Original Message-----
> Syed Jaffar Hussain

>=20

> We have some tables which have heavy insets everyday, can any=20
> one please hint me how often do I have to collect statistics=20
> on these tables? Normally, I collect statistics every=20
> alternative day. We are using Oracle 8i not 9i, I know there=20
> is a feature in 9i to collect statistics automatially for=20
> regular inserts tables. Unfortunately, we are still using oracle 8i.
=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Apr 12 2004 - 12:27:35 CDT