Monday, January 6, 2014

Adaptive Query Optimization – Will the real plan please stand up!



A new and powerful feature of 12c is Adaptive Query Optimization.  Now the optimizer really is taking a “hands on” approach to optimization.  In the past the optimizer came up with a plan and “tossed it over the fence” to the run time engine and dusted its hands of that plan and move on to the next.  (Not exactly true, even in 11 there was some interaction but nothing like what we see in 12.) 

In 12 there is a much more active role by the optimizer as to what is going to happen and does happen to the plan.  There are two main parts to this technique Adaptive Plans and Adaptive Statistics.  I’m going to show with a pretty simple query we use in our classes to show a little bit of each.

The query I used is this:

select /*+ gather_plan_statistics*/ b.object_name, b.object_type, a.username
  from allusers_tab a, big_tab b
  where a.username = b.owner 
  and b.object_type = 'PROCEDURE'
   and a.username not in ('SYS','SYSTEM');

BIG_TAB is a copy of ALL_OBJECTS and has about 2.9 million rows.  ALLUSERS_TAB is a copy of (you guessed it) ALL_USERS, and has 43 rows in it.  There are current stats on each table, no histograms. There is an index on BIG_TAB.OBJECT_TYPE and ALLUSERS_TAB.USERNAME. 

 Now I do an explain plan on tha query then run it twice.  I’m using DBMS_XPLAN to show the plan.  The first plan is with DBMS_XPLAN.DISPLAY() then for the two runs I use DISPLAY_CURSOR(FORMAT=>'+ADAPTIVE').  The new format switch of ‘+ADAPTIVE’ shows adaptive information about the cursor if there is any.  

Here is the plan from the explain plan, using the following select:

select * from table(dbms_xplan.display());
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 | 71204 |  3476K|  3367   (1)| 00:00:01 |
|*  1 |  HASH JOIN                           |                 | 71204 |  3476K|  3367   (1)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                    | USERNAME_PK     |    35 |   350 |     1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TAB         | 73182 |  2858K|  3365   (1)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                  | BIG_OBJTYPE_IDX | 73182 |       |   205   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   1 - access("A"."USERNAME"="B"."OWNER")
   2 - filter("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM')
   3 - filter("B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM')
   4 - access("B"."OBJECT_TYPE"='PROCEDURE')

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

The explain plan shows the DEFAULT plan, it does tell us that it’s an adaptive plan.  But we don’t know what the other steps are at this point.  Interestingly if you do a 10053 trace you do see all the steps in the plan but it’s less then clear in the 10053 output what is the default plan and what is the alternate plan. 

Now I run the query once and this is the plan I see after it runs, using the following select:

 select * from table(dbms_xplan.display_cursor(format=>'+adaptive'));

----------------------------------------------------------------------------------------------------------
|   Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                     |                 |       |       |  3367 (100)|          |
|  *  1 |  HASH JOIN                           |                 | 71204 |  3476K|  3367   (1)| 00:00:01 |
|-    2 |   NESTED LOOPS                       |                 |       |       |            |          |
|-    3 |    NESTED LOOPS                      |                 | 71204 |  3476K|  3367   (1)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR             |                 |       |       |            |          |
|  *  5 |      INDEX FULL SCAN                 | USERNAME_PK     |    35 |   350 |     1   (0)| 00:00:01 |
|- *  6 |     INDEX RANGE SCAN                 | BIG_OBJTYPE_IDX | 73182 |       |   205   (0)| 00:00:01 |
|- *  7 |    TABLE ACCESS BY INDEX ROWID       | BIG_TAB         |  2033 | 81320 |  3365   (1)| 00:00:01 |
|  *  8 |   TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TAB         | 73182 |  2858K|  3365   (1)| 00:00:01 |
|  *  9 |    INDEX RANGE SCAN                  | BIG_OBJTYPE_IDX | 73182 |       |   205   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   1 - access("A"."USERNAME"="B"."OWNER")
   5 - filter(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))
   6 - access("B"."OBJECT_TYPE"='PROCEDURE')
   7 - filter(("A"."USERNAME"="B"."OWNER" AND "B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM'))
   8 - filter(("B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM'))
   9 - access("B"."OBJECT_TYPE"='PROCEDURE')

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

Now we see the “whole enchilada”.  We can see that there is a set of alternate steps created to do the join that is a double nested loop, but that part of the plan is inactive for this run. It used the default plan to get the results.  Also notice step #4 “STATISTICS COLLECTOR”, this is an obvious point where the optimizer is taking a more active role in how the plan really runs.  Unlike in the past where it just came up with a plan and said to the run time engine “do this and don’t tell me about it”, it’s now in effect saying “hey while this runs, let me know what happened so I can adjust if needed.”  

Now let’s see what happens when we run it one more time.

--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |       |       |   480 (100)|          |
|   1 |  NESTED LOOPS                        |                 |  1664 | 83200 |   480   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TAB         |  1664 | 66560 |   479   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | BIG_OBJTYPE_IDX |  6336 |       |   205   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN                  | USERNAME_PK     |     1 |    10 |     0   (0)|          |
--------------------------------------------------------------------------------------------------------

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

   2 - filter(("B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM'))
   3 - access("B"."OBJECT_TYPE"='PROCEDURE')
   4 - access("A"."USERNAME"="B"."OWNER")
       filter(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))

Note
-----
   - statistics feedback used for this statement

Here we can see that the final plan is really totally different from the two the optimizer came up with during its initial parse!   The plan it goes with after a run and getting the statistics of the run is a good old single nested loop, but with the big table on the outside.  (This isn’t new, the optimizers been doing this for a while, I think 10.2 or so.) 

Of interest on the last run also is that this is no longer an adaptive plan, it is done.  This is it.  It does tell us that this used statistics feedback to come up with this plan which is pretty cool. 

What can we take away from this?  One thing for sure is that an explain plan is even less likely to be the plan that is really used.  And that we should run the query at least twice before we can say with some sort of certainty that we now have the plan that will really is used.  

BTW -  A little note here if you're doing a test like this on your own.  If you're using SQLPlus (as all really DBAs do) then make sure you turn off SERVEROUTPUT, if you don't then the plans you'll see when using the select * from table(dbms_xplan.display_cursor(format=>'+adaptive')) type queries will be for the command used by SERVEROUTPUT and not your query.