Re: In 12.1.0.2 ADS due to SPD will be always at level 11
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: yasser8@xxxxxxxxx
- Date: Mon, 7 Dec 2015 16:05:29 +0700
On Fri, Dec 4, 2015 at 10:40 PM, Yasser Khan <yasser8@xxxxxxxxx> wrote:
I read at many places that in version 12.1.0.2 Adaptive Dynamic Sampling
done due to Sql Plan Directive will be always at level 11, but
dbms_xplan.display_cursor doesn't refelct this truth.
So I thought of proving it by tracing 10053 along with RDBMS.SQL_DS
tracing. But I am not able to interpret the tracing result to prove that it
does dynamic sampling at level 11 due to SPD.
This is actually documented:
https://docs.oracle.com/cloud/latest/db121/REFRN/refrn10140.htm#REFRN10140
I quote:
"There are cases where the optimizer will automatically decide to use 11,
for example:
- The query will run in parallel.
- The query was executed before and its history is available (from the
cursor cache, Automatic Workload Repository, or the SQL Management Base)."
AFAIK it only does change it to 11 on its own if optimizer_dynamic_sampling
is left at the default value. I haven't tested this thoroughly yet, though.
From what I've seen it definitely does not always use level 11.
Stefan
Other related posts:
