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');
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.
