RE: collecting statistics on table those have bulk inserts everyday..
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Apr 2004 10:31:20 -0700
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: