BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








Oracle 10g Dynamic sampling

Oracle Tips by Burleson Consulting

Enabling Dynamic Sampling

The main objective of dynamic sampling is to create more accurate selectivity and cardinality  estimates, which in turn helps the CBO generate faster execution plans. Dynamic sampling is normally used to estimate single-table predicate selectivity when collected statistics cannot be used or are likely to lead to significant errors in estimation. It is also used to estimate table cardinality for tables without statistics or for tables whose statistics are too out of date to trust.


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 zero to ten.  In 10g, the default for this parameter is set to two, automatically enabling dynamic sampling.  The optimizer_features_enable parameter will turn OFF dynamic sampling if it is set to a version earlier than 9.2.0.


A value of zero 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 unanalyzed, and the amount of I/O spent on sampling.


According to Oracle Corporation, Oracle will sample 32 random blocks "The number of blocks sampled is the default number of dynamic sampling blocks (32)."


There is also a new dynamic sampling hint, dynamic_sampling (tablename level), where tablename is the name of the table to be dynamically sampled and level is the same setting from zero to ten.  The default value for the level is two, which will only sample tables that have not been analyzed with dbms_stats.


select /*+ dynamic_sampling (customer 4) */

   customer_name, . . . .


The following are the level descriptions.  The higher the level, the deeper the sampling.  The sampling levels are cumulative and each level contains all of the sampling of the prior level:

§       Level 1: Samples tables that appear in join or subquery conditions that have no indexes and have more blocks than 32, the default for dynamic sampling.

§       Level 2 (default): Samples all unanalyzed tables that have more than 32 blocks.

§       Level 3: Samples tables using a single column that applies selectivity to the table being sampled.

§       Level 4: Samples tables using two or more columns that apply selectivity to the table being sampled.

§       Level 5: Doubles the dynamic sample size and samples 64 blocks on tables.

§       Level 6: Quadruples the dynamic sample size and samples 128 blocks on tables.

§       Level 7: Samples 256 blocks on tables.

§       Level 8: Samples 1,024 blocks on tables.

§       Level 9: Samples 4,096 blocks on tables.

§       Level 10: Samples all of the block in the tables.


Dynamic sampling is not for everyone!



When dynamic_sampling was first introduced in Oracle9i, it was used primarily for data warehouse system with complex queries.  Because it is enabled by default in Oracle10g, the DBA may want to turn off dynamic_sampling to remove unnecessary overhead if any of the following are true:

§       The system utilizes an online transaction processing (OLTP) database with small, single-table queries.

§       Queries are not frequently re-executed as determined by the executions column in v$sql  and executions_delta in dba_hist_sqlstat .

§       Multi-table joins have simple WHERE clause predicates with single-column values and no built-in or mathematical functions.

Dynamic sampling is ideal whenever a query is going to execute multiple times, because the sample time is small compared to the overall query execution time.


By sampling data from the table at runtime, Oracle10g can quickly evaluate complex WHERE clause predicates and determine the selectivity of each predicate, using this information to determine the optimal table join order.  The following section will introduce the Oracle SQL SAMPLE clause and show how it works.


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:



Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter