Home > Performance Tuning, SQLTuning > Dynamic Sampling: Use,Levels,10g-11g behavior,

Dynamic Sampling: Use,Levels,10g-11g behavior,

We all know that Oracle Optimizer use object statistics to determine the cost of accessing that object. What if, if that object has missed statistics or the columns or the

Hence Oracle introduced Dynamic sampling in 9i and has changed significantly through 11g.


Dynamic sampling augments missing or insufficient optimizer statistics. Using dynamic sampling the optimizer can improve plans by making better estimates for predicate selectivity. Dynamic sampling can supplement statistics such as table block counts, applicable index block counts, table cardinalities (estimated number of rows), and relevant join column statistics.

To make use of it:-

Dynamic sampling is enabled in the database by default (level 2). You can disable the feature by setting the initialization parameter OPTIMIZER_DYNAMIC_SAMPLING=0.

Dynamic Sampling Levels:-

The dynamic sampling level controls both when dynamic sampling is triggered, and the size of the sample that the optimizer uses to gather the statistics. You can set the dynamic sampling level using either the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter or a SQL statement hint.


Level Description Blocks Sampled
0 Disable Dynamic Sampling 0
1 Perform Dynamic Sampling only if
- A table has missing statistics
- The table is involved in a join (in the same quer block)
- There is no index on this table
- This table has more blocks below the high water mark than will be sample
2 Perform Dynamic Sampling for all tables with missing statistics 64
3 Perform Dynamic Sampling for all tables that:
- Meet level 2 criteria
- – Or use an expression that leads the optimizer to guess (assume default hard-coded selectivity) for example a predicate like SUBSTR(COL, 1, 10) = ‘BLA’
64 (32)
4 Perform Dynamic Sampling for all tables that:
- Meet level 3 criteria
- Or have more than one predicate applied to it (complex predicate / possibly correlated column values)
64 (32)
5 The same as level 4 64
6 The same as level 4 128
7 The same as level 4 256
8 The same as level 4 1024
9 The same as level 4 4096
10 The same as level 4 4294967295


Different ways to set Dynamic Sampling?

Source:- http://jonathanlewis.wordpress.com/2010/02/23/dynamic-sampling/

You can set the parameter optimizer_dynamic_sampling to a level between 0 (no sample) and 10 (“sample” whole table).

You can use the dynamic_sampling() hint in two different ways in an SQL statement.

  • If you use the hint in the “cursor-level” form: /*+ dynamic_sampling ({level}) */ e.g. /*+ dynamic_sampling(4) */, this is equivalent to setting the parameter optimizer_dynamic_sampling to that level for the duration of that query.
  • If you use the hint in the “table-level” form /*+ dynamic_sampling({alias} {level}) */ e.g. /*+ dynamic_sampling(ord 4) */ you are directing Oracle to sample a specific table unconditionally. In this form, the sample size for levels 1 to 9 is 2level * “basic sample size” (which is set to 32 blocks by default by parameter _optimizer_dyn_smp_blks.) You can have multiple hints of this form in a single query, one for each table you want sampled.

How to know whether the statement is using Dynamic sampling technique:-

(” – dynamic sampling used for this statement”). in explain plan note.

10g and 11 Behavior

In 10g, the dynamic sampling level will be based on the parameter setting for all types of statements

In 11g, the level of dynamic sampling will be decided by optimizer automatically. This will be useful when you have a complex queries with parallelism enabled and you dont have idea about the statistics of the same where you cannot control it with any other feature like cardinality feedback etc,  But in some cases it may gives you undesired results

I have read somewhere, additional levels of dynamic sampling is possible in 11g, i.e above 10, I will post that reference when I able to catch that.

The above all is for my reference post of all about dynamic sampling from the different sources. hope this helps.


Permalink to Plan stability through Upgrade to 11G-Why is my plan changed–Auto Adjusted Dynamic




About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

BigData - Oracle-info.com

Sureshgandhi's Blog : Its time to learn BigData

Oracle Alchemist

Steve Karam's Oracle Blog


The King's .... "Joy of Life"..

the dba diary !!!

Naga's Blog : Oracle DBA experiences


Sureshgandhi's Blog: It's all about Oracle..Just Learn,Apply & Share.


All about Oracle.......

All about Oracle.......

DB Optimizer

database performance, SQL tuning and data visualizatoin


The thoughts of Jason Arneil

All Things Oracle

All about Oracle.......

Dion Cho - Oracle Performance Storyteller

We are natural born scientists

All about Oracle.......

All about Oracle.......

All about Oracle.......

Miladin Modrakovic's Blog: Oraclue

Oracle internals, debugging and undocumented features

Coskan's Approach to Oracle

What I learned about Oracle

The Arup Nanda Blog

All about Oracle.......


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

All about Oracle.......

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

All about Oracle.......

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Iordan Iotzov's DBA blog

The opinions here may not necessarily reflect that of my employers - both past and present. The comments left by the reviewers are theirs alone and may not reflect my opinion. None of the advice is warranted to be free of errors and omissions. Please use at your own risk and after thorough testing in your environment.

Doug's Oracle Blog

All about Oracle.......

Oracle related stuff

All about Oracle.......

All about Oracle.......

Charles Hooper's Oracle Notes

Miscellaneous Random Oracle Topics: Stop, Think, ... Understand

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle E-Business Suite Technology

All about Oracle.......

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

Oracle DBA - Tips and Techniques

Knowledge Is Power - Share IT ....

Guenadi N Jilevski's Oracle BLOG


Laurent Schneider

All about Oracle.......

Kerry Osborne's Oracle Blog

All about Oracle.......

Hemant's Oracle DBA Blog

All about Oracle.......

Oracle Enterprise Manager

All about Oracle.......

Structured Data

Thoughts on: Big Data, Hadoop, Databases, Platform, Performance & Scalability

Oracle Scratchpad

Just another Oracle weblog

Karl Arao's Blog

Just another weblog about Oracle,Linux,Troubleshooting,Performance,etc..etc..

The Data Warehouse Insider

All about Oracle.......

Online Apps DBA

One Stop Shop for Apps DBA's

All about Oracle.......

Microsoft SQL Server

All about Oracle.......


Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: