Re: db file scattered read

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sat, 07 Aug 2004 00:49:04 -0600
Message-Id: <6.1.0.6.2.20040807004115.02de8298@pop.centrexcc.com>

In my earlier note I reported that a test confirmed my suspicion that the CBO in many cases does either not invoke dynamic sampling, or disregards the results even when it does.

I did one more test and deleted the statistics of all 5 tables in my test query. With none of the tables in the query having statistics - and optimizer_mode = choose, the sql gets parsed by the RBO and the setting optimizer_dynamic_sampling=4 ignored altogether.

At 08:23 PM 8/6/2004, you wrote:

>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.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://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 Sat Aug 07 2004 - 01:46:39 CDT