Re: db file scattered read

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 07 Aug 2004 00:49:04 -0600

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@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: