12c 新機能 - 適応計画(adaptive execution plan)とは
適応計画とは一言でいうと「SQL実行時に実データ内容から実行計画をより適切と思われるものに即時修正する」という機能です。 同じような機能として11gの新機能でカーディナリティフィードバック(12c以降「自動再最適化」という名称に変わった)という 同じく実データを元に適切な実行計画を立て直すという機能がありましたが、この機能では実行計画が修正されるのは2回目以降のSQL実行時という制限がありました。 これに対して適応計画は最初のSQL実行時に実行計画を補正することができるため1度しか実行されないSQLでも性能改善する可能性があります。※本ページの内容は検証中のため誤記等を含む可能性があります。
適応計画の機能を使うためには
本機能の有効化、無効化は初期化パラメータOPTIMIZER_ADAPTIVE_REPORTING_ONLYで制御されておりデフォルト値はFALSE(機能有効)になっているため注意が必要です。適応計画の注意点
・explain plan やautotraceで表示される実行計画はデフォルトプランのため適応計画が働き実行計画が変動した場合表示と異なる実行計画で動作している可能性があります。 バインドピーク機能と同様に実際に選択された実行計画を確認する場合は実行したのち、V$SQL_PLANやdbms_xplan.display_cursor()で確認する必要があります。
・結合順序の変更ができない等変化できる実行計画には制限がある。(結合方法が変化するのは以下の検証で確認)
・(未確認情報)適応計画の動作条件として「統計情報と実データにかい離が発生している可能性がある」という内部条件が存在している可能性がある(統計を最新にした場合適応計画が動作しなくなる)
・(未確認情報)外部結合、カウントストップキーの条件あり(rownum=1等)、ヒント句で実行計画が固定化されているといったSQLに対しては適応計画は動作しないように見える。
適応計画の動作を確認する
以下の内容で動作することを12.1.0.1の環境で確認しています。
create table a (col1 number,col2 char(500));
create table b (col1 number,col2 char(500));
declare
j number;
begin
for i in 1..1000 loop
j := mod(abs(dbms_random.random()),1000);
insert into a values(j,j);
insert into b values(j,j);
end loop;
commit;
end;
/
--1000件の時点で統計を取得する
exec dbms_stats.gather_table_stats(null,'A');
exec dbms_stats.gather_table_stats(null,'B');
create index a_ix01 on a(col1);
create index b_ix01 on b(col1);
declare
j number;
begin
for i in 1001..500000 loop
j := mod(abs(dbms_random.random()),500000);
insert into a values(j,j);
insert into b values(j,j);
end loop;
commit;
end;
/
--a表の半分のデータをcol1=1にする(統計と実データを乖離させ、誤った実行計画を選択させる)
declare
j number;
begin
for i in 1..500000 loop
insert into a values(1,1);
end loop;
commit;
end;
/
--explainで実行計画を確認
explain plan for
select max(a2),max(b2) from (select /*+ no_merge */ a.col2 a2,b.col2 b2 from a,b where a.col1=b.col1 and a.col2 ='1');
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3827374307
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1004 | 24 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 1004 | | |
| 2 | VIEW | | 1 | 1004 | 24 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | | | | |★デフォルトプランはNESTED LOOPSを選択
| 4 | NESTED LOOPS | | 1 | 1012 | 24 (0)| 00:00:01 |★
|* 5 | TABLE ACCESS FULL | A | 1 | 506 | 22 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | B_IX01 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| B | 1 | 506 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("A"."COL2"='1')
6 - access("A"."COL1"="B"."COL1")
Note
-----
- this is an adaptive plan --★適応計画が動作した
--実際に実行
select max(a2),max(b2) from (select /*+ no_merge */ a.col2 a2,b.col2 b2 from a,b where a.col1=b.col1 and a.col2 ='1');
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID d6r45272kr1xk, child number 1
-------------------------------------
select max(a2),max(b2) from (select /*+ no_merge */ a.col2 a2,b.col2 b2
from a,b where a.col1=b.col1 and a.col2 ='1')
Plan hash value: 197503337
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1004 | | |
| 2 | VIEW | | 1 | 1004 | 24 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 1012 | 24 (0)| 00:00:01 |★実際に実行した場合はHASH結合に変化
|* 4 | TABLE ACCESS FULL| A | 1 | 506 | 22 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 1 | 506 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."COL1"="B"."COL1")
4 - filter("A"."COL2"='1')
Note
-----
- this is an adaptive plan
--v$sql_planで実行計画を表示
col operation format a50
select lpad(' ',depth) ||operation || ' ' || OPTIONS operation,OBJECT_NAME from v$sql_plan where sql_id = 'd6r45272kr1xk';
OPERATION OBJECT_NAME
-------------------------------------------------- ------------------------------
SELECT STATEMENT
SORT AGGREGATE
VIEW
HASH JOIN
NESTED LOOPS
NESTED LOOPS
STATISTICS COLLECTOR ★実データをサンプリングしたことを示していると思われる
TABLE ACCESS FULL A 実行計画がdbms_xplanによる表示と異なっている
INDEX RANGE SCAN B_IX01
TABLE ACCESS BY INDEX ROWID B
TABLE ACCESS FULL B
11行が選択されました。
折りたたみ
展開