Adaptive Query optimization, was a set of new capabilities, introduced in oracle 12c, to allow the optimizer to discover additional information regarding statistics and make run-time adjustments to execution plans to make them better. This is a major change in the optimizer behaviour from 11g.
I would recommend anyone who is planning an upgrade to 12c, that they make themselves familiar with the following white papers from oracle.
Understanding Optimizer Statistics with Oracle 12c
Best practices for gathering statistics with Oracle 12c
What to expect with the optimizer, with Oracle 12c.
In this article, i want to talk about some of the important concepts behind Dynamic Statistics, which is one of the components of Adaptive query optimizations.
In the section’s that are following, i show some commands to turn some of these features off. I want to be clear that I am not recommending that you turn anything off. I would prefer that customer’s adopt these new features,that are designed to improve the execution plans. Also keep in mind that the following are accurate (Afaik) on 22.214.171.124 as off the time of writing of this article, and are subject to change.
Dynamic statistics has two interesting effects, that DBA’s tend to notice initially.
– Ever so slightly, longer parse times for queries.
– Execution plan changes (Compared to what they had before upgrading) for the same query. (Sometimes unwelcome changes, especially for deployments that value stability more than performance gains).
Dynamic sampling was introduced by oracle in 9i Release 2 to improve the optimizer’s functioning. The amount of dynamic sampling done , and when it kicks in, is controlled by the parameter optimizer_dynamic_sampling. With 12c there is a new concept of Adaptive Dynamic sampling. Adaptive Dynamic Sampling is different from the pre-12c traditional dynamic sampling, in the following aspects.
– Adaptive Dynamic sampling could kick in even when optimizer_dynamic_sampling is set to 2.
– Especially for parallel queries on large tables.
– Adaptive Dynamic sampling kicks in when optimizer_dynamic_sampling is set to 11.
– Adaptive Dynamic sampling issues more queries than dynamic sampling used to do.
– The traditional dynamic sampling, used to issue, atmost, 1 query per table SQL.
– It is not uncommon to see 10’s of Adaptive Dynamic sampling queries being issued for a single SQL. Multiple dynamic sampling queries for the same table.(It dpends on the volume of data, number of indexed columns, complexity of the predicates etc).
– If you run a 10046 trace on the query, you will see a lot of additional queries in there that have the DS_SVC hint in them, which are the queries issued by the Adaptive Dynamic Sampling.
Setting OPTIMIZER_ADAPTIVE_FEATURES=FALSE does NOT turn off Adaptive Dynamic Sampling.
You can set Optimizer_Dynamic_Sampling = 0 to turn Adaptive Dynamic Sampling off. However this would be like throwing the baby out with the bath water. Setting Optimizer_Dynamic_Sampling=0 completely sets dynamic sampling off (Including the old style pre-12c dynamic sampling).
You can do an ALTER SESSION SET “_fix_control”=’7452863:0′; to turn just Adaptive Dynamic Sampling off, if you so desire.
Adaptive Dynamic Sampling also uses oracle Results Cache. If results cache is enabled in the database (usually by setting result_cache_max_size to a value > 0), then Adaptive dynamic sampling uses the results cache to store the results that it queries up. This is done so that, if there are multiple query parses that have to do dynamic sampling, and they are looking at the same data, the optimizer can just look that value up from the results cache (As opposed to having to query the tables again and again).
If you have a system, that has a lot of hard parses (Due to not using bind variables), you could pottentialy see latch free waits on “Results Cache: rc latch”. Please refer to Mos note 2002089.1, that suggest’s setting “_optimizer_ads_use_result_cache” = FALSE; to work around this. Keep in mind that setting this parameter does not prevent the optimizer from using Adaptive dynamic sampling. All it does it prevent the Adaptive dynamic sampling from using the results cache.
The following Mos note’s and a presentation from Trivadis, have a lot of great information in this regard.
Automatic Dynamic Statistics (Doc ID 2002108.1)
Different Level for Dynamic Statistics (Dynamic Sampling) used than the Level Specified (Doc ID 1102413.1)
High Waits for ‘cursor: pin S wait on X’ due to Dynamic Sampling Against Parallel Queries (Doc ID 2006145.1)