Adaptive Query Optimization or Real Time Query Optimization #
After Parse transformation, Query will enter into optimizer . optimizer will generate different execution plans based on selectivity, cardinality and cost i.e stats available on sysaux tablespace. After generating better execution plan by optimizer, oracle server process executes the query on database and Oracle Server process start processing the query using execution plan delivered by optimizer and before execution. Here in 12c, Optimizer starts comparing the data sets depend on tables and type of SQL query and join made on base tables. Depend on volume of rows fetched in data set on base tables ( optimizer discovers cardinality mismatch ), Oracle waits to create and Delay in final Execution plan decision after parsing before execution and follow adaptive Query optimization. The new behavior of 12c optimizer helps to adjust execution plan on run-time adjustments using statistics collector. Optimizer is smart in 12c
In 10g by default it is set to 2, Dynamic sampling is enabled when table dons’t have stats on it.
But in 12c functionality remains same and when ever there is cardinality mismatch during execution of sql query optimizer use adaptive statistics and changes execution plan
set following parameters on database to enable adaptive query optimization #
optimizer_features_enable=12.1.0
optimizer_adaptive_reporting_only=true ( If this is set to true . Optimizer will not execute query optimization plan, it sticks with execution plan generated by cost based optimizer and but it will store information, how it can help if we enabled enable adaptive query optimization )
optimizer_dynamic_sampling=11 in 12c (This helps optimizer to go with dynamic sampling or not )
dynamic sampling refer following link
