Re: db file scattered read

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 06 Aug 2004 20:23:25 -0600
Message-Id: <6.1.0.6.2.20040806201038.02f021f8@pop.centrexcc.com>

I hope you're joking again.

Enabling "MONITORING" does not cause Oracle to automatically gather statistics. As Justin pointed out, that doesn't happen until Oracle 10. In 9 you still need to schedule a gather_database|schema_stats "gather stale" or "gather auto" job.

In theory, setting dynamic sampling to 4 should cause Oracle 9 to sample statistics at parse time. Do you have any experience that it actually works? I have tried on occasion to set dynamic sampling at the session level, but unless the tables in the sql do not have statistics (in which case you run the risk that the CBO is bypassed for the RBO if you have optimizer_mode=choose => note to self to test if dynamic_sampling > 1 changes that "rule"), I have the strong impression that the CBO in many cases does NOT sample statistics but simply uses the existing statistics. Unless you jack up the level to much higher than 4, and even then not always. I have to do more tests to be more certain.

At 07:43 PM 8/6/2004, you wrote:

>In 9i you sen set table to "MONITORING", and Oracle will collect statistics
>automatically. In 9i you can set dynamic sampling to 4 and Oracle will
>collect statistics during parse.
>
>--
>Mladen Gogala
>Oracle DBA

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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 Fri Aug 06 2004 - 21:20:17 CDT