RE: Impact of auto-adjustment of Dynamic Sampling : Note 1102413.1
Date: Fri, 26 Sep 2014 13:46:36 +0000
Message-ID: <C1FB7BA65B13C542B2CB1CE5DB8F74AF16BDDE54_at_NC2PWEX501.us.ad.lfg.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.**
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 26 2014 - 15:46:36 CEST