Having discussed about how the execution plans (actual
plan) could be different from the explain plan (estimated plan) in the context
of bind
variables and cursor_sharing
parameter, we are going to see how Adaptive plans (the killer new feature of
12c database) will contribute to the difference in plans.
Adaptive
plans:
Starting with Oracle 12c, Adaptive
plans enables the optimizer to defer the final plan decision for a
statement until execution time.
The optimizer instruments its chosen plan
(default plan) with statistics collector
so that at runtime, it can detects its runtime cardinality misestimates – if
there is a significant difference then plan or the portions of it can be
automatically adapted to avoid sub-optimal performance during hard parsing.
By default explain plan command shows only the initial plan (default plan), while the V$SQL_Plan accessed using
DBMS_XPLAN.DISPLAY_CURSOR method reports the final plan.
demo@ORA12C>
create table stage as select * from all_objects;
Table created.
demo@ORA12C>
create table t1 as select * from stage;
Table created.
demo@ORA12C>
create table t2 as select * from stage;
Table created.
demo@ORA12C>
create table t3 as select * from stage;
Table created.
demo@ORA12C>
insert /*+ append */ into t1 select * from stage;
72304 rows
created.
demo@ORA12C>
insert /*+ append */ into t2 select * from stage;
72304 rows
created.
demo@ORA12C>
insert /*+ append */ into t3 select * from stage;
72304 rows
created.
demo@ORA12C>
commit;
Commit
complete.
demo@ORA12C>
begin
2
dbms_stats.gather_table_stats(user,'T1');
3
dbms_stats.gather_table_stats(user,'T2');
4
dbms_stats.gather_table_stats(user,'T3');
5 end;
6 /
PL/SQL
procedure successfully completed.
demo@ORA12C>
create index t1_idx on t1(object_id);
Index created.
demo@ORA12C>
create index t2_idx on t2(object_id);
Index created.
demo@ORA12C>
create index t3_idx on t3(object_id);
Index created.
demo@ORA12C>
demo@ORA12C>
update t1 set object_id = 77 where mod(object_id,100) = 0;
1444 rows updated.
demo@ORA12C>
update t2 set object_id = 77 where mod(object_id,100) = 0;
1444 rows
updated.
demo@ORA12C>
update t3 set object_id = 77 where mod(object_id,100) = 0;
1444 rows
updated.
demo@ORA12C>
set feedback on
demo@ORA12C>
explain plan for
2
select *
3 from
t1,t2,t3
4
where t1.object_id = t2.object_id + 1
5 and
t2.object_id = t3.object_id +1
6 and
t3.object_id = 77;
Explained.
demo@ORA12C>
@xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash
value: 251470998
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
8 | 3192 | 21
(0)|
| 1 |
NESTED LOOPS
| | 8 |
3192 | 21 (0)|
| 2 | NESTED
LOOPS | |
8 | 3192 | 21
(0)|
| 3 |
NESTED LOOPS
| | 4 |
1064 | 9 (0)|
| 4 |
TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
2 | 266 | 3
(0)|
|* 5 |
INDEX RANGE SCAN
| T3_IDX | 2 | |
1 (0)|
| 6 |
TABLE ACCESS BY INDEX ROWID BATCHED| T2 |
2 | 266 | 3
(0)|
|* 7 |
INDEX RANGE SCAN
| T2_IDX | 2 | |
1 (0)|
|* 8 |
INDEX RANGE SCAN
| T1_IDX | 2 | |
1 (0)|
| 9 |
TABLE ACCESS BY INDEX ROWID
| T1 | 2 |
266 | 3 (0)|
--------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
5 -
access("T3"."OBJECT_ID"=77)
7 -
access("T2"."OBJECT_ID"="T3"."OBJECT_ID"+1)
8 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID"+1)
Note
-----
- this is an adaptive plan
27 rows
selected.
demo@ORA12C>
@d:\script.sql
demo@ORA12C>
/*
demo@ORA12C>
set termout off
demo@ORA12C>
select *
demo@ORA12C>
from t1,t2,t3
demo@ORA12C>
where t1.object_id = t2.object_id + 1
demo@ORA12C>
and t2.object_id = t3.object_id +1
demo@ORA12C>
and t3.object_id = 77;
demo@ORA12C>
set termout on
demo@ORA12C>
*/
demo@ORA12C>
set echo off
demo@ORA12C>
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 53nhu426q03vy, child number 0
-------------------------------------
select * from
t1,t2,t3 where t1.object_id = t2.object_id + :"SYS_B_0"
and
t2.object_id = t3.object_id +:"SYS_B_1" and t3.object_id =
:"SYS_B_2"
Plan hash
value: 693695692
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | | 5784 |
|* 1 |
HASH JOIN
| | 1 |
8 | 5784 |
|* 2 |
HASH JOIN |
| 1 | 4 |
2892 |
| 3 |
TABLE ACCESS BY INDEX ROWID BATCHED| T3 |
1 | 2 | 1446 |
|* 4 |
INDEX RANGE SCAN
| T3_IDX | 1 | 2 |
1446 |
| 5 |
TABLE ACCESS FULL | T2
| 1 | 2 |
144K|
| 6 |
TABLE ACCESS FULL
| T1 | 1 |
2 | 144K|
-----------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID"+:SYS_B_0)
2 -
access("T2"."OBJECT_ID"="T3"."OBJECT_ID"+:SYS_B_1)
4 -
access("T3"."OBJECT_ID"=:SYS_B_2)
Note
-----
- this is an adaptive plan
31 rows
selected.
demo@ORA12C>
As you can see the initial plan come up with was
a Nested loop join, where the final plan was in fact a Hash join. So if you
only use the explain plan command you would never know the completely different
join method was used.
So, my advice for tuning is to use V$SQL_PLAN
when reviewing the execution plan for a SQL statement, as it will only show the
plan actually used by the statement.
No comments:
Post a Comment