RE: segment monitoring, stats, histograms

Hi

>I'm working on 9.2.0.4 and considering the 'automated statistics=20
>gatherin=3D g' approach. This involves turning on monitoring for any =
and=20
>all tables =3D that need to ever have stats updated, then periodically=20
>running dbms_stat=3D s in gather_stale mode.
>
>How is this working for people? =20

It works good in many cases. But, as any good rule, there are = exceptions... therefore you have to test it.

>Does monitoring impact DML operations, and if so, how much?

Negligible, at least, I never see a case where it wasn't...

>Does this approach make any kind of intelligent decisions about sample=20
>si=3D zes and block sampling?
>
>When histograms are present, does this approach always/never/sometimes=20
>re=3D generate the histogram with the correct number of buckets?

Monitoring and gather_stale have no impact on the gathering. As with = "normal" gathering you have to specify the parameters.

>Does it seem to reliably choose the correct tables to analyze?

It simple gathers statistics on table where at least 10% of the rows = have been modified. Notice, however, that all DMLs that are executed on = the table are counted. Independently of the fact that the rows have = really been modified or the transactions committed.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l