« SQLチューニング総合診療所 ケースファイルX / db tech showcase tokyo 2014 | トップページ | 机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! »

2014年12月 9日 (火)

私のチューニング、アダプティブなオプティマイザにも、今のところ勝てそうな気がするぞ〜〜〜っ。と思った師走のある日。

このエントリは JPOUG Advent Calendar 2014 9日目のエントリです。 昨日のエントリは wmo6hashさんの Goodbye, Patch Set. さよなら、PSR。でした。

アダプティブなオプティマイザ関連のアドベントカレンダーネタに、ついカッとなったところで、アダプティブな結合を試してみようと、一杯の珈琲を飲み、気持ちを落ち着けたアカウントがこちらになりますw


環境は以下の通り。

Oracle Linux Server release 6.6
Linux 3.8.13-44.1.3.el6uek.x86_64 #2 SMP Wed Oct 15 19:53:10 PDT 2014 GNU/Linux


BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0


以下のような2表を用意しました。他のネタに作った表なのですごーく適当ですが、ID列は単一列の主キー制約があります。その他に索引はありません。

SCOTT> desc maybe_driving_tab
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
ALTID NUMBER
DATA VARCHAR2(2000)
STATUS NOT NULL NUMBER(2)

SCOTT> desc maybe_inner_tab
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
ALTID NUMBER
DATA VARCHAR2(2000)
STATUS NOT NULL NUMBER(2)


そして、これまた適当にデータを登録して統計を取っちゃいます。

SCOTT> truncate table maybe_driving_tab;
SCOTT> truncate table maybe_inner_tab;
SCOTT> insert into maybe_driving_tab values(1,1,1,1);
SCOTT> insert into maybe_driving_tab values(2,1,1,1);
SCOTT> insert into maybe_inner_tab select * from maybe_driving_tab;
SCOTT> commit;
SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_DRIVING_TAB',cascade=>true,no_invalidate=>false,);
SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_INNER_TAB',cascade=>true,no_invalidate=>false);

この時点で代表的な統計情報を見てみると、こんな感じです。

SCOTT> select table_name,index_name,num_rows,distinct_keys,clustering_factor from user_indexes where table_name like 'MAYBE%'

TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ------------- -----------------
MAYBE_DRIVING_TAB PK_MAYBE_DRIVING_TAB 2 2 1
MAYBE_INNER_TAB PK_MAYBE_INNER_TAB 2 2 1

今回、主役となるid列は、連番で登録します。このタイプはクラスタリングファクタも低くなりindex range scanの有効範囲が広めになる傾向があります。しかし、このデータを登録する前に統計情報を取得(2件の時に)したため、実データと統計情報は大きく乖離しています。Oracle Database 12c 12.1.0.2.0 のオプティマイザにちょっとした意地悪をしています。

BEGIN
FOR i IN 3..200000 LOOP
INSERT INTO maybe_driving_tab VALUES(
i
,CEIL(DBMS_RANDOM.VALUE(1,400001))
,LPAD(i,500,'*')
,MOD(i,2))
;
INSERT INTO maybe_inner_tab VALUES(
i
,CEIL(DBMS_RANDOM.VALUE(1,400001))
,LPAD(i,500,'*')
,MOD(i,2))
;
IF MOD(i,100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/


統計取得後に、これだけ大量のデータを登録していれば....統計情報は失効しています

SCOTT> select table_name,num_rows,stale_stats from user_tab_statistics where table_name like 'MAYBE%';

TABLE_NAME NUM_ROWS STA
------------------------------ ---------- ---
MAYBE_DRIVING_TAB 2 YES
MAYBE_INNER_TAB 2 YES


統計情報上は2行ですが...実際は20万行あります (^^;; かなり意地の悪い状況にしてありますが、状況的になくもないと思います。

SCOTT> select count(1) from maybe_driving_tab;

COUNT(1)
----------
200000

SCOTT> select count(1) from maybe_inner_tab;

COUNT(1)
----------
200000


はい、準備はできました。ここからが本題ですよ〜〜っ。

次のようなSQL文を実行してみます。
AUTOTRACEのNote部分に、12cから登場したadaptive planが適用されていることが示されてます。(見逃さないでくださいよ。
adaptiveな機能が多くなり、リテラル値を使っていても実際の実行計画とは異なる実行計画が示されることが多くなってきてるんです。
dbms_xplan.display_cursor(format=>'ALLSTATS LAST')、SQLトレース、または、SQL監視機能を使って実際に選択されている実行計画の確認が重要になった、ということですからね!! これ大事ですよ。

SCOTT> r
1 SELECT
2 /* SQL101 */
3 /*+
4 MONITOR
5 */
6 *
7 FROM
8 maybe_driving_tab t1
9 INNER JOIN maybe_inner_tab t2
10 ON
11 t1.id = t2.id
12 WHERE
13* t1.id BETWEEN 1 and 30000

30000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=30000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=30000)

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


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
8573 consistent gets
2159 physical reads
0 redo size
31686000 bytes sent via SQL*Net to client
22541 bytes received via SQL*Net from client
2001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30000 rows processed

では、実際に実行された実行計画をSQL監視機能(オプションが必要な機能です)を使って見てみます。

11gまでは見たこともない不思議な光景になっていますよね。これがAdaptive Joinの実行計画。ほうほうって感じです。

この実行計画の中には2つの実行計画が混在しています。実際に実行された操作もあれば、実行されない操作も含まれています。

どの操作が実行されたかみるには、Execs列をみます。

ね、ね、ね。 
Nested Loop結合じゃなくて、Hash結合が実行されています。
はじめは、Nested Loop結合をやろうとしてるのわかりますか?

ID=4のStatistics Collectorとう操作で実行時の統計(このケースでは行数でしょうかね??? 想像ですが)をみて、Nested Loop結合を寸止めして(妥当な表現かあやしいが)、ハッシュ結合に切り替えた様子が見えますよね。。。。ちょいと感動したw

ただし、index range scanするには行数的に微妙な状況になりつつありそうなところだと思います。

======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 5 | +0 | 1 | 30000 | | | | | |
| 1 | HASH JOIN | | 2 | 4 | 5 | +0 | 1 | 30000 | | | 20M | | |
| 2 | NESTED LOOPS | | 2 | 4 | | | 1 | | | | | | |
| 3 | NESTED LOOPS | | 2 | 4 | | | 1 | | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 0 | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 1 | +0 | 1 | 30000 | | | | | |
| 6 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 1 | +0 | 1 | 30000 | | | | | |
| 7 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | | | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | | | | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_inner_tab | 1 | 1 | 5 | +0 | 1 | 30000 | 2108 | 16MB | | | |
| 10 | INDEX RANGE SCAN | PK_maybe_inner_tab | 1 | | 5 | +0 | 1 | 30000 | 51 | 408KB | | | |
======================================================================================================================================================================================


さらに、取得件数を倍にしてみました。

どうなるでしょう。

割合的には、30%の範囲検索です。(統計情報次第では全表走査+ハッシュ結合に切り替わることも無くはない状況ですが.....)

SELECT
/* SQL102 */
/*+
MONITOR
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 60000
/

60000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=60000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=60000)

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


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
17142 consistent gets
9230 physical reads
0 redo size
63391324 bytes sent via SQL*Net to client
44541 bytes received via SQL*Net from client
4001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
60000 rows processed


全体の30%の範囲検索でもギリギリ、Index range scanしているようです。 index rowid batchedとう操作が効いているためでしょうか。そのあとに、ハッシュ結合をしています。
index rowid batchedという不連続ブロックの一括読み込みを繰り返して、全体の30%程度のindex range scanを 1度のオペレーションで行っている箇所(ID=5や9)は興味深いですよね。
(クラスタリングファクタが低いと、実はtable full scanと大差なかったりして〜〜〜と思ったり、思わなかったり)

======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 9 | +0 | 1 | 60000 | | | | | |
| 1 | HASH JOIN | | 2 | 4 | 9 | +0 | 1 | 60000 | | | 36M | | |
| 2 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | |
| 3 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 60000 | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 1 | +0 | 1 | 60000 | 4615 | 36MB | | | |
| 6 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 1 | +0 | 1 | 60000 | | | | | |
| 7 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | | | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | | | | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_inner_tab | 1 | 1 | 9 | +0 | 1 | 60000 | 4615 | 36MB | | | |
| 10 | INDEX RANGE SCAN | PK_maybe_inner_tab | 1 | | 9 | +0 | 1 | 60000 | | | | | |
======================================================================================================================================================================================


次に、全体の40%程度の範囲検索にしてみます。
全表走査+ハッシュ結合が理想だと思うのですが、adaptive joinでhash結合になっているので、全表走査になりそうな予感はしますが...

SELECT
/* SQL103 */
/*+
MONITOR
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/

80000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=80000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=80000)

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


統計
----------------------------------------------------------
4 recursive calls
0 db block gets
22856 consistent gets
3393 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


ん〜〜〜〜〜っ。adaptive joinでhash結合にはなるもの全表走査にはなかなかなってくれません。むむむ。

==============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
==============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +0 | 1 | 80000 | | | | | | | | |
| 1 | HASH JOIN | | 2 | 4 | 12 | +0 | 1 | 80000 | 21 | 2MB | 21 | 2MB | 48M | 3M | | |
| 2 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | | | | |
| 3 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 80000 | | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 1 | +0 | 1 | 80000 | 1539 | 12MB | | | | | | |
| 6 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 1 | +0 | 1 | 80000 | | | | | | | | |
| 7 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | | | | | | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | | | | | | | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_inner_tab | 1 | 1 | 12 | +0 | 1 | 80000 | 1539 | 12MB | | | | | | |
| 10 | INDEX RANGE SCAN | PK_maybe_inner_tab | 1 | | 12 | +0 | 1 | 80000 | | | | | | | | |
==============================================================================================================================================================================================================


ちょっと、ここで寄り道してみます。
adaptive joinを無効にしたら、多分、一番だめな感じのNested Loop結合になるはずなので確認しておきましょう。
隠しパラメータを %adaptive%で検索して見つけたのがそのものズバリの隠しパラメータそ使つかいます。 :)

AUTOTRACEから adaptive planというNoteが消えましたねぇ。(^^

SELECT
/* SQL104 */
/*+
MONITOR
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/


80000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=80000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=80000)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
102603 consistent gets
12608 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


統計情報との乖離が大きいので、Nested Loop結合でindex range scanしてますねぇ〜。駆動表はtable access index rowid batchedになっていますが....
内部表は8万回もindex indexunique scanされてますね。

=============================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=============================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 11 | +0 | 1 | 80000 | | |
| 1 | NESTED LOOPS | | 2 | 4 | 11 | +0 | 1 | 80000 | | |
| 2 | NESTED LOOPS | | 2 | 4 | 11 | +0 | 1 | 80000 | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 11 | +0 | 1 | 80000 | | |
| 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 11 | +0 | 1 | 80000 | | |
| 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 11 | +0 | 80000 | 80000 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | 11 | +0 | 80000 | 80000 | | |
=============================================================================================================================================================

寄り道はここまでにして、
オプティマイザがやってくれないなら、ヒントでチューニングしちゃいましょ。
hash結合+全表走査になるように....

SELECT
/* SQL105 */
/*+
MONITOR
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
FULL(t1)
FULL(t2)
USE_HASH(t1 t2)
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/

80000行が選択されました。


いい感じになったような気がしますが、consistent getsは、index range scan + table access bby index rowid batchedの方が少ないんですねぇ〜。
実データのクラスタリングファクタが高かったら違う結果になるような気がします。
今回は試しませんが。

実行計画
----------------------------------------------------------
Plan hash value: 1719838364

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 44 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| maybe_driving_tab | 2 | 22 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| maybe_inner_tab | 2 | 22 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."ID">=1 AND "T1"."ID"<=80000)
3 - filter("T2"."ID">=1 AND "T2"."ID"<=80000)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
33390 consistent gets
30906 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


==============================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==============================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +0 | 1 | 80000 | | | | | |
| 1 | HASH JOIN | | 2 | 6 | 11 | +0 | 1 | 80000 | | | 50M | | |
| 2 | TABLE ACCESS FULL | maybe_driving_tab | 2 | 3 | 1 | +0 | 1 | 80000 | 252 | 121MB | | | |
| 3 | TABLE ACCESS FULL | maybe_inner_tab | 2 | 3 | 11 | +0 | 1 | 80000 | 251 | 121MB | | | |
==============================================================================================================================================================


統計との乖離があるのは確かなので統計情報を取り直してみます。(オプティマイザはどういった計画を導きだすか....)

SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_DRIVING_TAB',no_invalidate=>false,cascade=>true);
SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_INNER_TAB',no_invalidate=>false,cascade=>true);


実行!

SELECT
/* SQL108 */
/*+
MONITOR
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/

80000行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 1719838364

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80000 | 78M| | 12776 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 80000 | 78M| 40M| 12776 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| maybe_driving_tab | 80000 | 39M| | 4398 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| maybe_inner_tab | 80000 | 39M| | 4398 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."ID"<=80000 AND "T1"."ID">=1)
3 - filter("T2"."ID"<=80000 AND "T2"."ID">=1)

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


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
35859 consistent gets
15453 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


===========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===========================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +0 | 1 | 80000 | | | | | |
| 1 | HASH JOIN | | 80000 | 12776 | 11 | +0 | 1 | 80000 | | | 51M | | |
| 2 | NESTED LOOPS | | 80000 | 12776 | | | 1 | | | | | | |
| 3 | NESTED LOOPS | | | | | | 1 | | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 0 | | | | | |
| 5 | TABLE ACCESS FULL | maybe_driving_tab | 80000 | 4398 | 1 | +0 | 1 | 80000 | 252 | 121MB | | | |
| 6 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | | | | | | | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 4398 | | | | | | | | | |
| 8 | TABLE ACCESS FULL | maybe_inner_tab | 80000 | 4398 | 11 | +0 | 1 | 80000 | | | | | |
===========================================================================================================================================================================

うん、やっと、オプティマイザと私の意見が一致したようだw
おら、オプティマイザより先にこの結果想定してたんだ! アダプティブなオプティマイザにちょっとだけ勝ったw ぞ〜〜〜〜っ. (クレヨン シンちゃん風に...)

そして....
table access by index rowid batched って状況次第ではあるものの、意外にいいかも。と思ったのであった。 (^^/
....今後の為に、さらにネタを集めたほうがいいかもしれない...


最後に、adaptiveな機能が多くなっていますが、その基礎になるのはやはり、統計情報と、その向こうにあるデータです。
機能に目が移りがちですが忘れちゃいけないのが、これらです。


以上、役に立つような、立たないようなネタでした。

あ、一つ忘れてました〜〜っ。
optimizer_dynamic_samplingが効きそうな気もしますが、今回はデフォルト(2)だったので結果は未確認です。だれかやってみて〜〜:)


JPOUG Advent Calendar 2014
明日は、yoku0825さんです。お楽しみに〜。

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/60776677

この記事へのトラックバック一覧です: 私のチューニング、アダプティブなオプティマイザにも、今のところ勝てそうな気がするぞ〜〜〜っ。と思った師走のある日。:

コメント

adaptive planは結合方法をNested Loop結合からHash結合に変更しますが逆向きの変更はないですね。
これは駆動表からのフェッチ行数が予想より多い見込みとなった場合に、
不適切なNested Loop結合で致命傷になるのをHash結合にすることでベターな選択をさせたいと理解しています。
※元の実行計画改善していることを検証で確認しました。

しかしながら、adaptive planはアクセスパスや結合順序については変更しない仕様のため、
今回Hash結合でFull Scanしないのは正しい動作だと思います。そのためベストの実行計画とはなりません。


またご期待にこたえてやってみましたが、動的統計11では仕様か不具合かわかりませんが、
索引に関するサンプリングがうまく動いていないようで、Hash結合かつ索引使用という実行計画になりました。

統計情報を手動で破棄し、動的統計2で取得した場合はdiscus_hamburgさんの考えた実行計画となりました。
ちょっとサンプリングさせてあげて統計情報の精度をあげれば、
正しい判断が出来る可能性もあるということでしょうか。


なお、検証される方がいるかもしれないので、記載しておくと
× exec dbms_stats.gather_table_stats(ownname->'SCOTT',
○ exec dbms_stats.gather_table_stats(ownname =>'SCOTT',

× FOR i IN 1..200000 LOOP
○ FOR i IN 3..200000 LOOP


長文失礼しました。

投稿: 雄山 | 2014年12月 9日 (火) 09時51分

指摘部分修正しました。

optimizer_dynamic_sampling=11は一度試したのですが(ずるい
誰か他の人もやらないかな〜と思って書いてみました。。。期待していた人のリアクションがあると楽しいです。

ありがとうございました。

でも、雄山ってだれだろ〜〜〜〜w (わざとらしい 

投稿: discus | 2014年12月 9日 (火) 21時46分

興味深い記事をありがとうございます。

個人的には、Oracle11gのカーディナリティフィードバックといい、Oracle12cの自動再最適化・adaptive planといい、正直「余計なことしなくていいのに」という思いを禁じ得ないのですが、如何なんでしょうか。

投稿: rzpqyo | 2014年12月15日 (月) 20時22分

私は、adaptiveなplanなどオプティマイザは現時点ではいい試みをしているんじゃないかと思っています。ただ、まだまだやりたいことはできていないとは思います。
統計情報はある時点では正しくても、すぐに実態とは乖離しやすいのは事実ですし、乖離した部分をどのようにしてオプティマイザがキャッチアップして実データに沿った計画に最適化するかということは重要なテーマだと思っています。

adaptive planの動きを見ていると、結構いい方向に向かいそうな気はしています。
(バッチ処理を実行してていつもよりも遅い、リアルタイムSQL監視で実行計画を確認して実行計画が良くないことに気づき、手動でヒントをつけて再実行という流れに比べたら、adaptive joinって結構いいんじゃないか?、と個人的には思っています。今後の経過観察は必要だと思いますが...)

投稿: discus | 2015年1月 1日 (木) 12時16分

コメントを書く