Navinth Bakmeedeniya, April 21, 2017 - 6:55 am UTC
Hi Maria,
Thanks for your reply.
I'm not still very clear how to distinguish these two types of Dynamic Sampling.
For instance, I had a problem of heavy dynamic sampling operations triggered by SQL Plan Directives due to carnality estimates & I applied the below solution in my database.
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2237208.1 According to your categorization, this solution should address both types of dynamic sampling operations as this problematic dynamic sampling (mentioned in the document)
is triggered by SQL Plan Directives which part of Oracle 11g/12c Optimizer Adaptive Feature Dynamic Statistics & the cause is
cardinality misestimates due to insufficient statistics. Hence, I think applying this should disable both types of Dynamic Sampling?
On the other hand, can we have any Dynamic Sampling operations in Oracle 12c due to cardinality misestimates
which are not triggered via SQL Plan Directives?
Thanks & Best Regards,
Navinth
April 24, 2017 - 6:10 pm UTC
Hi Navinth,
Yes, it's still possible to have dynamic sampling kick in even if you have disabled the creation and use of SQL Plan Directives.
By disabling SQL Plan Directives you will reduced the number of occurrences the dynamic sampling statements with the hint DS_SVC. However, if you run complex SQL statements using parallel execution you may still see some dynamic sampling statements with the hint DS_SVC.
You may also see some dynamic sampling statements with the hint OPT_DYN_SAMP if regular statistics are not sufficient to get good quality cardinality estimates or when statistics are missing altogether.
To return the behavior of dynamic sampling to what it was in Oracle Database 11g Release 1, you can set the parameter optimizer_dynamic_sampling to 2.
You can prevent the use of dynamic sampling all together by setting the parameter optimizer_dynamic_sampling to 0, although this is not normally recommended.
Navinth Bakmeedeniya, April 25, 2017 - 4:16 am UTC
Hello Maria,
I have couple of questions.
1. When I have disabled the the SQL Plan Directives (using the above mentioned solution) & I'm not using parallel execution, can I still have DS_SVC queries? Hope not.
2. Dynamic Sampling queries generated by SQL Plan Directives always have DS_SVC comment but not OPT_DYN_SAMP comment. Is this undestanding correct?
3. When table statistics are present & SQL Plan Directives are disabled, Optimizer still can clearly identify the existing statistics are sufficient enough or not to get good quality cardinality estimates. If existing statistics are not good enough, it is still possible for the optimizer to trigger dynamic sampling statements at its own (without the help of SQL Plan Directives). Is this undestanding correct?
4. As a summary, Optimizer Dynamic Sampling seems triggered mailny due to two reasons, - regular table statistics are not present
- cardinality misestimates
We understand the fact that if we set optimizer_dynamic_sampling=0, we would lose dynamic sampling caused by both the above reasons i.e. no dynamic sampling at all. Out target is to completely eliminate dynamic sampling operations triggered by cardinality misestimates. We need to have the dynamic sampling triggered by the absence of table statistics. Is there any configuration that we can achive this?
Many Thanks,
Navinth
May 05, 2017 - 1:53 am UTC
Hi Navinth,
It's very unlikely you will see dynamic sampling queries containing the DS_SVC comment if you have disabled SQL Plan Directives and you do not using parallel execution.
Yes, all dynamic sampling queries trigged by SQL Plan Directives should use the DS_SVC comment.
Yes, it's still possible that the optimizer will trigger dynamic sampling even if the tables have statistics and you have disabled SQL Plan Directives. However, it's less likely.
This behavior is controlled by the initialization parameter optimizer_dynamic_sampling. In pervious version (before 12c), the parameter optimizer_dynamic_sampling had a default value of 2, which means dynamic_sampling would only kick in statistics were missing on one of the tables used in the query.
If you wish to return to this behavior, dynamic sampling triggered only by the absence of table statistics, you simply have to explicitly set the parameter optimizer_dynamic_sampling to 2.
More information on this type of dynamic sampling and what the different levels mean is available on the Optimizer Blog
https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer Thanks,
Maria
on explicitly setting the values
Rajeshwaran, April 28, 2017 - 8:18 am UTC
....
If you wish to return to this behavior, dynamic sampling triggered only by the absence of table statistics,
you simply have to explicitly set the parameter optimizer_dynamic_sampling to 2.
....
So, why to set it explicitly ? the default value of this parameter is set to 2 in 12.2.
demo@ORA12C> show parameter optimizer_dynamic
NAME TYPE VALUE
------------------------------------ ----------- --------
optimizer_dynamic_sampling integer 2
demo@ORA12C>
<quote>
Out target is to completely eliminate dynamic sampling operations triggered by cardinality misestimates.
</quote>
If i am not getting it wrong, the question here is how to eliminate dynamic sampling for cardinality misestimates and have them kick-in only incase of regular statistics missing.
is that possible to set dynamic sampling this way?
April 29, 2017 - 12:41 am UTC
Hi Rajesh,
In Oracle Database 12.1 adaptive statistics are enabled by default, which is why Navinth was seeing internal dynamic sampling SQL statements with the comment DS_SVC.
Even though optimizer_dynamic_sampling is shown 2 if it's not explicitly set to 2 in the init.ora file, adaptive statistics can kick if:
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)
A SQL Plan Directive exists
In Oracle Database 12.2 adaptive statistics have been disabled by default and so you are unlikely to see dynamic sampling queries with the DS_SVC.
This issue is described in detail on the Optimizer blog
https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the It's my understanding that Navinth is trying to prevent adaptive statistics from ever kicking in, in a 12.1 database.
In order to this he will need to apply patches for bugs 21171382 and 22652097 and set the underscore parameters _optimizer_dsdir_usage_control and _sql_plan_directive_mgmt_control as well as some fix controls. All of this is explained in detail in the MOS note called Recommendations for Adaptive Features in Oracle Database 12c Release 1 (12.1) (Doc ID 2187449.1).
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2187449.1 Thanks,
Maria
Navinth Bakmeedeniya, April 28, 2017 - 10:39 am UTC
Hi Maria,
We do already have the setting optimizer_dynamic_sampling = 2.
Other settings we have:
- _optimizer_dsdir_usage_control=0 -- disable use of directives by the optimizer
- _sql_plan_directive_mgmt_control=0 -- disable creation of directives
- all the relevant tables have regular statistics
- no parallel execution queries
But even though it is not very common, we still can see OPT_DYN_SAMP statements executed with this setting. Is there a way we can stop these OPT_DYN_SAMP statements?
Thanks & Best Regards,
Navinth
April 29, 2017 - 12:53 am UTC
Hi Navinth,
If you have explicitly set optimizer_dynamic_sampling to 2 in the initialization file and disabled adaptive statistics using the steps outlined in the MOS note called "Recommendations for Adaptive Features in Oracle Database 12c Release 1 (12.1)" (Doc ID 2187449.1).
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2187449.1 then dynamic sampling will only occur if you are missing statistics on one or more of the tables used in you query.
Is it possible your application or end-users uses global temporary tables or some other staging or intermediate tables that do not have statistics on then, which would explain the use of dynamic sampling?
If you don't believe this is case or you are not sure then you will need to look at the actual dynamic sampling query to determine which table is trying them to kick in.
Thanks,
Maria
Navinth Bakmeedeniya, April 28, 2017 - 10:46 am UTC
Hi Maria,
In addition to my above reply, Rajeshwaran's comment is correct.
My target is here is to eliminate dynamic sampling for cardinality misestimates and have them kick-in only in case of regular statistics missing.
Regards,
Navinth
April 29, 2017 - 12:59 am UTC
Hi Navinth,
In order to eliminate dynamic sampling from kicking in if your queries have cardinality misestimates and only have it kick-in if you are missing regular statistics then you need to do the following:
1. Explicitly set optimizer_dynamic_sampling=2
2. Follow all of the steps in the MOS note called Recommendations for Adaptive Features in Oracle Database 12c Release 1 (12.1) (Doc ID 2187449.1).
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2187449.1 to disable adaptive statistics.
Thanks,
Maria
Navinth Bakmeedeniya, April 29, 2017 - 11:10 am UTC
Hi Maria,
Many thanks for mentioning the document. It was very helpful.
So If I summarize our discussion:
To disable only SQL Plan Directives:
- optimizer_dynamic_sampling=2
- _optimizer_dsdir_usage_control=0 -- disable use of directives by the optimizer
- _sql_plan_directive_mgmt_control=0 -- disable creation of directives
To disable dynamic sampling for all cardinality misestimates (including disabling SQL Plan Directives) and have them kick-in only in case of regular statistics missing:
Oracle Database 12c Release 1
---------------------------
Option1: optimizer_dynamic_sampling=2 & optimizer_adaptive_features=FALSE
this would disable adaptive plans as well in addition to disabling adaptive statistics
Option2: optimizer_dynamic_sampling=2 & apply the patch 22652097
this would split the parameter optimizer_adaptive_features into two, optimizer_adaptive_plans and optimizer_adaptive_statistics and then disables adaptive statistics.
Oracle Database 12c Release 2
---------------------------
optimizer_dynamic_sampling=2. Patch 22652097 is already included & adaptive statistics is disabled by default. In addition, options at table level (AUTO_STAT_EXTENSIONS) to set manually if we want to enable this again.
Hope my understanding is correct.
Thanks & Best Regards,
Navinth
July 06, 2017 - 1:41 am UTC
Hi Navinth,
Yes, your summary is correct.
Thanks,
Maria
Navinth Bakmeedeniya, May 05, 2017 - 9:38 am UTC
Hi Maria,
According to the document 2187449.1, SQL Plan Directives is a part of Adaptive Statistics. Since patch 22652097 disables Adaptive Statistics completely, do we still need to configure the two hidden parameters, _optimizer_dsdir_usage_control & _sql_plan_directive_mgmt_control to disable SQL Plan Directives when patch 22652097 is installed?
Also please confirm my understanding is correct mentioned in my last note on April 29, 2017?
Thanks & Best Regards,
Navinth
July 06, 2017 - 1:51 am UTC
Hi Navinth,
If you apply patch 22652097 Adaptive Statistics will be disabled completely and therefore you should not need to set the underscore parameters.
Thanks,
Maria
Navinth Bakmeedeniya, July 06, 2017 - 4:36 am UTC
Hello Maria,
Many thanks for your explanation on this.
It was very helpful.
Thanks & Best Regards,
Navinth
Sunil, March 30, 2020 - 8:00 pm UTC
Thanks a lot for detailed explanation on this topic.
Hi Maria,
I have a follow up question . In one of our 12.2 DB , we have below optimizer set but even after that I do see below query in DB .
Is below SQL ( DS_SVC for non parallel queries ) can execute during CREATION of SQL Plan Directive as well ? Usage of SQL Plan Directive is already disabled.
optimizer_adaptive_plans = False , optimizer_adaptive_statistics = FALSE
_sql_plan_directive_mgmt_contr = 67 ( this will cause SQ Plan directive creation) , _optimizer_dsdir_usage_control=0 ( Usage is disabled)
SQL :
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel */
Thanks,
Sunil
March 31, 2020 - 6:04 am UTC
I've asked around internally.... We can't see a reason *why* you would be encountering those queries, but the general response from everyone I asked was:
You're on 12.2 now....why not remove those underscore parameters entirely? The closer you are to defaults, the more likely you can get good service from Support .