12c SQL tuning: adaptive execution plans

articles: 

The concept of an adaptive plan is that execution of a statement can start with one plan, and (during execution) switch to another. A pretty amazing capability. No need to run the statement to completion and try again: correct it in flight.

The mechanism relies on embedding statistic collectors at critical points in the execution plan, that will allow Oracle to compare the actual row counts with the estimates. If these counters cross certain thresholds, Oracle will switch to an alternative plan, during execution, that is better suited to reality.
Now, why is this ever necessary? Because the CBO was wrong in its estimates of cardinality. Never forget: SQL tuning is all about the cardinality (to quote our colleague Kevin Meade). If the cardinality estimates are wrong, your queries will not perform well. Adaptive plans are a corrective measure that can get you out of trouble.
How and when does the adaptive plan mechanism get used in a 12c instance? It is enabled during the hard parse, and used during execution. During parse, many pans are computed. The CBO selects what it believes to be the best plan, based on the information available at parse time. Nothing new so far. What is new is that if the CBO realizes that the chosen plan could be inappropriate if the cardinality estimates are wrong, it will store alternative plans with the cursor, and calculate exactly what cardinalities would make an alternative plan a better choice. Then, as the statement executes, if the embedded counters for actual cardinality cross those limits, it will switch plan. Doing this requires buffering of rows at the critical points, so that the switch can be made without any need for running the statement to completion or re-starting it.
Two last points: the plans must have the same starting point, otherwise a switch is not possible. Also, in the current release only join method and parallel query distribution method can be adjusted. I think there will be more changes possible in future releases, but these are pretty good already.
Here's a simple example. Use EXPLAIN PLAN to see how Oracle is going to execute a query:

orclz>
orclz> explain plan for select ename,dname from emp natural join dept;

Explained.

orclz> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    22 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    22 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP     |     1 |     9 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - this is an adaptive plan

21 rows selected.

orclz>

An indexed nested loop join: scan EMP, and for each row do an index search and then in the outer loop retrieve the column from DEPT for projection. Reasonable, if (as the cardinality estimate shows) there is only one row in EMP. But what if there were a million rows in EMP? That would mean a million index searches. Seriously bad: a horrific number of single block reads. In that case, a hash join based on scans and multiblock reads would be a far superior solution. The CBO has realized that the choice of a nested loop is critically dependent on the accuracy of the estimate, and it is telling us
Quote:
this is an adaptive plan

Now I'll run the statement, and see what actually happens:
orclz>
orclz> select /*+ gather_plan_statistics */ ename,dname from emp natural join dept;

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
KING       ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
ADAMS      RESEARCH
SCOTT      RESEARCH
JONES      RESEARCH
SMITH      RESEARCH
JAMES      SALES
TURNER     SALES
BLAKE      SALES
MARTIN     SALES
WARD       SALES
ALLEN      SALES

14 rows selected.

orclz>

Disaster! Oracle expected one row, but got fourteen!! What actually happened? Take a look:
orclz>
orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dzm9ctqgkmnpn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename,dname from emp natural join
dept

Plan hash value: 1123238657

--------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |
|*  1 |  HASH JOIN         |      |      1 |      1 |     14 |00:00:00.01 |
|   2 |   TABLE ACCESS FULL| EMP  |      1 |      1 |     14 |00:00:00.01 |
|   3 |   TABLE ACCESS FULL| DEPT |      1 |      1 |      4 |00:00:00.01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - this is an adaptive plan


25 rows selected.

orclz>

Hey, magic! Oracle switched from nested loop join to hash join. Brilliant. Why? Because the CBO inserted a counter into this first operation, the scan of EMP. And when it started the scan, it read the first row and then, to its horror, realized that there was in fact another row. And another. So it changed its mind, and decided to do a hash join instead of a nested loop. Somewhere between one row and fourteen rows, there is a tipping point where the plan with a hash join becomes more efficient than the plan with a loop, and the rows from EMP will have been buffered up to that point, delaying the choice until the correct choice could be made.
If you are curious, you can prove that was what happened by looking at a new column in V$SQL:
orclz> select sql_text,is_resolved_adaptive_plan from v$sql where sql_id='dzm9ctqgkmnpn';

SQL_TEXT                                                                         I
-------------------------------------------------------------------------------- -
select /*+ gather_plan_statistics */ ename,dname from emp natural join dept      Y

orclz>
This is the proof that the plan was adapted during execution.
To conclude, is this really a solution? Yes, but not ideal. It is a technique for covering up mistakes. Probably mistakes made by you, the DBA: you didn't give the optimizer adequate statistics. But it is certainly better than the other techniques often used such as hints or profiles. I hope everyone knows that hints should be avoided wherever possible, but profiles aren't much better. If you have a thousand statements, you need a thousand profiles. A 12c alternative is to use SQL Directives, which apply to a table rather than a statement. So one directive might fix a thousand queries.
But you are still covering up mistakes, which it would be better not to make in the first place. The real solution must be to get the cardinality estimates right in the first place: quite possibly, with the new 12c facilities for automation of extended statistics and improved algorithms for dynamic sampling.
More about them later....
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Comments

Hi John,

I have a doubt. The execution plan might change from NL to HJ or vice versa as a result of adaptive plan or automatic reoptimization. What is the criteria used to decide which option will be exercised?

Regards
Anju Garg

You have misunderstood the concept. An adaptive plan is changed during runtime. Re-optimization can occur only after running the statement to completion, when attempting to run it again. So re-optimization is useless in an environment where queries take a long time to run or are run only once.

I think I have not expressed my question clearly.
Suppose, there is a query whose performance will improve if join method changes from NL to HJ. In such a case, it is desirable that the plan adapts itself during first execution itself rather than waiting for the SQL to be executed again. In which scenario will the optimizer optimize it during subsequent execution?

Regards
Anju Garg

@Anju,

The concept is that the execution plan changes at run-time. So, where is the question of waiting for the SQL to complete execution? If the optimizer finds a reason to change the plan, it would certainly do it, irrespective of first run or subsequent runs.

Great article and great explanation et demonstration