Following on from * an OTN posting about dynamic sampling* difficulties I had planned to write a blog post about the difference between “not sampling when hinted” and “ignoring the sample” – but

*got there before me.*

**Mohamed Houri**It’s just worth highlighing a little detail that is often overlooked, though: there are * two versions of the dynamic_sampling() hint*, the cursor level and the table level, and the number of blocks sampled at a particular level is dependent on which version you are using. Level 4 at the cursor level, for example, will sample 64 blocks if and only if a certain condition is met, but at the table level it will sample 256 blocks unconditionally.

So try to be a little more specific when you say *“I told the optimizer to use dynamic sampling …”*, it’s either:

*“I told the optimizer to use cursor level dynamic sampling at level X …”*

or

*“I told the optimizer to use table level dynamic sampling at level Y for table A and …”*

Note – apart from the changes to dynamic sampling that allow for a level 11, there’s also a change introduced (I think) in 10g for the * sample()* clause applied to the table during sampling – it’s the addition of a

*clause which ensures that when you repeat the same level you generate the same set of random rows.*

**seed()**### Addendum

Here’s a little code I wrote some time ago to check the effect of the two options at different levels. I started by creating a (nologging) table from the first 50,000 rows of all_objects, then doubled it up a few times to 400,000 rows total, and ensured that there were no stats on the table. Then executed in turn each variant of the following anonymous pl/sql block (note that I have the execute privilege on the dbms_system package):

declare m_ct number; begin execute immediate 'alter session set events ''10053 trace name context forever'''; for i in 1..10 loop sys.dbms_system.ksdwrt(1,'============='); sys.dbms_system.ksdwrt(1,'Level ' || i); sys.dbms_system.ksdwrt(1,'============='); execute immediate 'select /*+ dynamic_sampling(' || i || ') */ count(*) from t1 ' || -- 'select /*+ dynamic_sampling(t1 ' || i || ') */ count(*) from t1 ' || 'where owner = ''SYS'' and object_type = ''SYNONYM''' into m_ct; end loop; end; /

Obviously I could examine the resulting trace file to pick out bits of each optimisation, but for a quick check a simple * grep* for

*“sample block cnt”*is almost all I need to do – with the following (slightly decorated) results from 11.2.0.4:

Table level =========== Level 1 max. sample block cnt. : 32 sample block cnt. : 31 max. sample block cnt. : 64 sample block cnt. : 63 max. sample block cnt. : 128 sample block cnt. : 127 max. sample block cnt. : 256 sample block cnt. : 255 max. sample block cnt. : 512 sample block cnt. : 511 max. sample block cnt. : 1024 sample block cnt. : 1023 max. sample block cnt. : 2048 sample block cnt. : 2047 max. sample block cnt. : 4096 sample block cnt. : 4095 max. sample block cnt. : 8192 sample block cnt. : 8191 Level 10 max. sample block cnt. : 4294967295 sample block cnt. : 11565 Cursor level ============ No sampling at level 1 Level 2 max. sample block cnt. : 64 sample block cnt. : 63 max. sample block cnt. : 64 sample block cnt. : 63 max. sample block cnt. : 64 sample block cnt. : 63 max. sample block cnt. : 64 sample block cnt. : 63 max. sample block cnt. : 128 sample block cnt. : 127 max. sample block cnt. : 256 sample block cnt. : 255 max. sample block cnt. : 1024 sample block cnt. : 1023 max. sample block cnt. : 4096 sample block cnt. : 4095 Level 10 max. sample block cnt. : 4294967295 sample block cnt. : 11565

You’ll notice that the cursor level example didn’t do any sampling at level 1. Although the manual doesn’t quite make it clear, sampling will only occur if three conditions are met:

- The table has no statistics
- The table has no indexes
- The table is involved in a join so that a sample could affect the join order and method

If only the first two conditions are met then the execution path will be a full tablescan whatever the sample looks like and the number of rows returned has no further impact as far as the optimizer is concerned – hence the third requirement (which doesn’t get mentioned explicitly in the manuals). If you do have a query that meets all three requirements then the sample size is 32 (31) blocks.