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.
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 220.127.116.11 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 Dynamic Statistics. Dynamic Statistics is different from the pre-12c traditional dynamic sampling, in the following aspects.
– Dynamic Statistics could kick in even when optimizer_dynamic_sampling is set to 2.
– Especially for parallel queries on large tables.
– Dynamic Statistics kicks in when optimizer_dynamic_sampling is set to 11.
– Dynamic Statistics 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 Dynamic Statistics 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 Dynamic Statistics.
Setting OPTIMIZER_ADAPTIVE_FEATURES=FALSE does NOT turn off Dynamic Statistics.
You can set Optimizer_Dynamic_Sampling = 0 to turn Dynamic Statistics 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 Dynamic Statistics off, if you so desire.
Dynamic Statistics 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 Dynamic Statistics 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 Dynamic Statistics . All it does it prevent the Dynamic Statistics from using the results cache.
The following Mos note’s and a presentation from Trivadis, have a lot of great information in this regard.