Dynamic Sampling

Working on a query that selects from a Master Table and then outer joins three sub-queries to it based on a common column. While looking at the execution plan generated by the optimizer, I noticed this:

Note

-----

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

After some research on Dynamic Sampling (DS), I had noticed that I was using Parallel hints in my statement so I removed them expecting not to see DS being used. This made no difference. I also looked at our INIT setting - it was set to default (2):


NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
optimizer_dynamic_sampling           integer     2

Further research suggested missing Statistics and/or Histograms on the Tables used in my query. This was not the case either.

Recent Stats collected:

select table_name, LAST_ANALYZED from dba_tables where table_name in ('...')

No Locked Stats either:

select table_name, STATTYPE_LOCKED  from DBA_TAB_STATISTICS

where table_name in ('...')

So, I started to work backward and run my query with one table and add in each sub-query after checking the execution plans each time for DS.

Table 1 (Master) => no DS

Table 1 and sub-query => no DS

Table 1 and 2 sub-queries => no DS

Table 1 and 3 sub-queries => DS=5

So it appears that when I add the 3rd sub-query, DS kicks in. Originally I had thought that this could be caused by using the PIVOT command but I rewrote the sub-query as a simple SELECT statement just for testing. This made no difference - DS=5 was still being used.

One possible theory:

Perhaps in ‘auto’ mode the Optimizer just doesn’t feel that, based on my WHERE clause, that Stats are good enough and forces to augment the Stats with DS ?  When I join to this Table, I am (outer) joining on all Indexed columns. Perhaps that is a clue?

   and a.index_code = e.index_code(+)

   and a.fiscal_year = e.fiscal_year(+)

   and A.FISCAL_PERIOD = e.fiscal_period(+)

   and a.account = e.ACCT_CODE(+)

   AND A.FUND_TYPE_LEVEL_2 = E.FUND_TYPE_LEVEL_2(+)

To be continued...