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...
