Re: In 12.1.0.2 ADS due to SPD will be always at level 11

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Mon, 7 Dec 2015 16:05:29 +0700
Message-ID: <CAP50yQ99djfqCVU0-oagt++r=xggmjAB3EGMWPu7bBhgzinXRg_at_mail.gmail.com>

On Fri, Dec 4, 2015 at 10:40 PM, Yasser Khan <yasser8_at_gmail.com> 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

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

Received on Mon Dec 07 2015 - 10:05:29 CET