Re: Impact of auto-adjustment of Dynamic Sampling : Note 1102413.1

the first question I would ask is, how do you determine that the Dynamic Sampling negatively impacts your Parallel DML operations?

Comparing the plans with and without Dynamic Sampling - the plan with Dynamic Sampling should show a different plan that is affected by worse cardinality estimates than the plan without Dynamic Sampling leading to longer execution times.

Another possibility would be the recursive Dynamic Sampling query itself takes significant time. There are still bugs lurking around in 11.2.0.3 / 11.2.0.4 where the DS query uses a bad choice in sampling size and takes literally minutes / hours to complete, typically when partitioning is getting used.

Typically the Dynamic Sampling cardinality estimate adjustments are in the right ballpark and often much better than the estimates based on statistics. The auto adjustment should only be triggered if the query includes predicates that would trigger Dynamic Sampling on level 3 or 4, so either complex expressions that lead to guesses based on available statistics (no suitable extended statistics) and/or more than one column on the same table (potentially correlated column values without suitable column group statistics).

Having said that you can disable this specific feature on different levels using the Fix Control for the bug 7452863, like:

Statement level: /*+ OPT_PARAM('_fix_control' '7452863:0') */

Session level: ALTER SESSION SET "_fix_control"='7452863:0'

System level: ALTER SYSTEM SET "_fix_control"='7452863:0'

See also the output of querying V$SYSTEM_FIX_CONTROL for bugno = 7452863

I had to disable this specific feature at one specific client where the (auto adjustment of) Dynamic Sampling regularly failed to produce reasonable cardinality estimates and actually led to worse plans, but at least in my experience this was an exception to the rule stated above.

Randolf

> The auto-adjusted dynamic sampling "explained" in Support Note 1102413.1
> seems to negatively impacting some of my Parallel DML operations. I do
> have updated statistics on the underlying table and yet Oracle is
> auto-adjusting the dynamic sampling (and reporting the higher level).
> This is happening for SQLs where I do NOT specify a dynamic sampling
> hint or session specification but leave the instance level unchanged at
> 2. Is there a method to disable this ?

--
http://www.freelists.org/webpage/oracle-l