RE: Impact of auto-adjustment of Dynamic Sampling : Note 1102413.1
- From: "Deas, Scott" <Scott.Deas@xxxxxxx>
- To: "Hemant-K.Chitale@xxxxxx" <Hemant-K.Chitale@xxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Sep 2014 13:46:36 +0000
Hermant,
According to Maria Colgan, setting optimizer_dynamic_sampling to 0 will disable
it
(https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer).
Since you have good statistics on your tables, could you try disabling dynamic
sampling at the session or query level?
Session:
alter session set optimizer_dynamic_sampling=0;
Query:
SQL> select /*+ dynamic_sampling (my_table 0) */ * from my_table;
Thanks,
Scott
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Chitale, Hemant K
Sent: Friday, September 26, 2014 4:47 AM
To: ORACLE-L
Subject: FW: Impact of auto-adjustment of Dynamic Sampling : Note 1102413.1
This is on 11.2.0.3 with Parallel Query / DML.
Hemant K Chitale
_____________________________________________
From: Chitale, Hemant K
Sent: Friday, September 26, 2014 4:45 PM
To: ORACLE-L
Subject: Impact of auto-adjustment of Dynamic Sampling : Note 1102413.1
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 ?
Hemant K Chitale
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html.
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
Other related posts:
