Oracle 12c - adaptive query optimization

2 min read

Sep 30, 2015 12:00:00 AM

Scenario: A user complains that a batch job that is critical for the month end business processing is taking more time than expected. As a DBA you quickly check and identify the sql statement that is running, compare the current execution plan with the previous plans and come to the conclusion that it has picked up a wrong plan. Instead of a Hash Join, it is doing a Nested Loop or did not pick the parallel threads and instead does a single threaded full table scan. At this juncture, you do not have control to change the execution plan unless you cancel the job, fix it and rerun it. A simple analogy is like getting stuck in traffic and cannot do anything about it, even though you know that there are better and faster alternative routes to reach your destination. Luckily, the above scenario was the case with 11g, though with 12c a new feature called - Adaptive Query Optimization was introduced and this feature helps the optimizer adjust the plans based on the real time data. 12c Optimizer:- With Oracle 11g, an optimizer decides an optimal execution plan for a query based on the conditions in the query, statistical information of the underlying objects and initialization parameters that influence the optimizer. With 12c, a new adaptive approach to query optimization is introduced by adjusting execution plans based on information collected during run time. This new approach is extremely helpful when the existing statistics are not sufficient to generate an optimal plan. There are two aspects in Adaptive Query Optimization:

Improving the initial execution of a query during runtime.
Adaptive statistics, that provide additional information for subsequent executions.

I will be focusing on the first aspect in this article. The two features that optimizer adjusts based on the real time data are: Hash Join vs Nested Loops - Optimizer may pick nested loop for a join operation based on the existing table statistics information, but during run time, if it realizes that more data is being processed, it will switch to Hash Join. This adaptive optimizer feature not only changes the plan on run time, but stores the adaptive statistics in the database. This additional information will be useful for future executions. Parallel Distribution - Hybrid Hash - When a SQL statement is executed in parallel mode, optimizer decides whether to perform certain operations like sorts, joins and aggregations in parallel or as a single threaded operation based on the statistics. With the new adaptive feature, optimizer differs this decision till run time and based on the run time data it decides whether to pick the parallel mode or not. Good resource about this new feature can be found here and here. Check some of our other Oracle 12c blogs Discover more about our Oracle expertise.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Oracle 12c - adaptive query optimization

Share this

Share this

More resources

Performance problems with Dynamic Statistics in Oracle 12c

Exadata Join Offloading in Action

ORA-10173: Dynamic sampling time-out error

Ready to unlock value from your data?