Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 
HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck
 

Donald K. Burleson

Oracle Tips

Enabling Oracle Dynamic Sampling


The optimizer_dynamic_sampling initialization parameter controls the number of blocks read by the dynamic sampling query. The parameter can be set to a value from 0 to 10. In 10g, the default for this parameter is set to '2', automatically enabling dynamic sampling. Beware that the optimizer_features_enable parameter will turns off dynamic sampling if it is set to a version earlier than 9.2.0.

A value of 0 means dynamic sampling will not be performed. Increasing the value of the parameter results in more aggressive dynamic sampling, in terms of both the type of tables sampled (analyzed or un-analyzed) and the amount of I/O spent on sampling.

It is recommended that you set optimizer_dynamic_sampling = 4 or use the dynamic_sampling (4) hint. Always test your SQL in a test environment before implementing it in production and be careful setting optimizer_dynamic_sampling to a value greater than 4 because there is overhead associated with run-time statistics sampling that may outweigh any performance gains from faster execution time.

 

 

”call
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book
 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.