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