Dynamic sampling NOT used for this statement?

So I’m still working on this data warehousing project and performance is not quite optimal. There are all kinds of things to be investigated. One thing I want to find, is the reason why a lot of execution plans show this message:

   - dynamic sampling used for this statement (level=4)

Dynamic sampling is usually done when statistics are not available or unreliable. So that’s not a good sign. But try as I might, I could not find on what particular table, partition or index statistics were lacking. I’ve searched the Oracle Support site and Google, but could not find information about what could be wrong.

Eventually I found a site that showed how event 10053 traces (trace of the Oracle optimizer) can indicate what object has unreliable statistics. You would find rows like these in your trace file:

*** 2014-04-06 16:02:33.145
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 4).
** Dynamic sampling updated index stats.: PK_ORDERS, blocks=45881
** Dynamic sampling updated table stats.: blocks=93712

So I remembered I had generated event 10053 trace files for the execution plan of an important query about a month ago. So I went looking for “** Dynamic sampling initial checks returning TRUE” messages.

Well I found nothing. All I found was “** Dynamic sampling initial checks returning FALSE” all the time. In the end I only found this:

Dynamic sampling level auto-adjusted from 4 to 4

Maybe I’m missing something, but it really looks like “Dynamic sampling used” messages in execution plans are not always out about wrong statistics.


About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning and tagged , , , , , . Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s