先生！、全行アクセスしてるのに Nested Loop Join しちゃうんです！ （東京都 ITエンジニア 男性） - optimizer_mode は正しく設定しましょう！
ということで、（どういうことだw
（今日のねたはアドベントカレンダーのネタを考えていた時に思い出したネタです）
さて、本題。
Oracle DatabaseのSQLチューニングや実行計画の読み方を学び始めたとき、最初に学ぶ（多分）、コストベースオプティマイザが作り出す実行計画に大きく影響を与える初期化パラメータ。
そのパラメータは何か？、
みなさん、パッと思う浮かでしょうか？
_optim_peek_user_binds ってアンダースコアパラメータを思い浮かべた方は居ないはず！（と断言してしまったが、意外と結構居たりして..
それは絶対にないと信じwww、
今日は、 optimizer_mode 初期化パラメータのお話をしてみたいと思います。
冒頭で書いたようにに、 Oracle Database の実行計画やSQLチューニングを学び始めたときに、最初に習う、覚えるのは、このパラメータだったと。思う（私の記憶ではw 私の場合、その最初の頃が昔すぎて怪しい）
それが、
optimizer_modeパラメータです。
この類のパラメータがあるのは Oracle Database だけではないかと思います。
例えば、Hash Join/Merge Joinを無効にして、Nested Loop Joinだけにするなど複数のオプションを組み合わせて似たような挙動にすることはできるものは多いですが、Oracle Databaseのように単一パラメータで、緩めに制御できるのは他にはないと思います。
このパラメータ、むかーーーーーーーーーーーーーーーしからあって、現在の ALL_ROWS/FISRT_ROWS_N というオプションになる前はCHOOSEやRULEというオプションがありました。
これは、Oracle Database 10gリリース1(10.1) 10gR1でルールベースオプティマイザが非サポートとなったタイミングで廃止され、現在この初期化パラメータがサポートしているオプションは以下のようになっています。デフォルトは ALL_ROWS です。
Database / Oracle / Oracle Database / Release 19 / Database Reference / 1.248 OPTIMIZER_MODE https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_MODE.html
OPTIMIZER_MODE = { FIRST_ROWS_[1 | 10 | 100 | 1000] | FIRST_ROWS | ALL_ROWS }
このパラメータのオプションの特徴をざっくり解説してしまうと、
optimizer_mode = ALL_ROWSだとスループットを最大限にする実行計画が選択されます。（一般的にバッチだったり、分析系のSQLの実行計画に向いているのがこのモードで、デフォルトはこれです）
optimizer_mode = FIRST_ROWS_Nに設定した場合、N行の結果を如何に速く返すか、つまり、レスポンスタイムの良さを実現するための実行計画が選択されます。（一般的にはOLTP向きとされる実行計画になります。結合方法として Nested Loop Joinが選ばれやすくなります。全行じゃなくても最初の１行を早くクライアントへ返してあげられるような実行計画になりやすいですのがこのモードです）
ちなみに FIRST_ROWS は下位互換として残されているだけなので最近では使うことはないです。
ALL_ROWSがデフォルトなので、多くの場合、デフォルトのままで、OLTP系のSQLでは、index scanや、nested loop joinになるようにWHERE句を記述したり、ヒント等で制御したりしているケースが多いのではないでしょうか？
一方、optimizer_mode = first_rows_n に設定しておき, OLTP向きの実行計画をデフォルトで選択しやすい状況にしているケースもそれなりに見かけます（かなり少ないと思いますが、ちゃんと考えて設定しているという意味では、自分たちのシステムのワークロードで重要なのはどれだ！ 認識している証かもしれません。どちらにするかは方針次第ではあるのですが。）
と、ここまでが、ながーーーい前説ですw
今回のタイトル ”先生！、全行アクセスしてるのに、Nested Loop Joinしちゃうんです！”
もうお分かりですねw 今日のネタ。
今日の患者さん、 optimizer_mode = first_rows_1 となっている環境で、where句もない結合を伴うクエリーが、Nested Loop Joinで、全行読み込んでしまったことに悩んでいました。
optimizer_mode = first_rows_n という設定になっていることにも気づいてなかったようですね。 all_rows の感覚のままでいると戸惑うのも当然です。
チューニング前に、オプティマイザに影響する初期化パラメータを確認しておくことをお勧めします！！！
チューニングをお願いされた場合、該当する初期化パラメータの設定も一緒に提供してもらう。
セッションレベルで変更されている場合もあるので、それらの情報も提供してもらうことが大切ですよ。忘れないでくださいね。
それらパラメータ情報も、ERに運び込まれた患者SQLを救うには大切な情報なのです！
このケースの場合、治療は非常に簡単で、初期化パラメータ optimizer_mode はそのままで、 ALL_ROWS ヒントを該当SQLに埋め込むのが手っ取り早いと思います。セッションレベルで optimizer_mode = all_rowsにするのもありです。
強力なヒントではないので軽視されがちな初期化パラメータですが、実は、ひょんなことで、その実力に気付いたりするものですwwwwwwww
ただ、ざっくりとした実行計画の傾向を支持するものなので、追加のヒントで矯正したりする必要もあることは忘れないでください。
では、早速、その効力を確認することにします。 21cを利用します。古くても新しくても挙動は同じです、
また、ネタ的に面白いので一時表での挙動も含めています ：）
一時表は統計情報の持ち方等が永続表とは異なるので別の注意が必要です。（参考 津島博士のパフォーマンス講座 第35回 オプティマイザ統計の運用について（２））
統計情報に影響され難い例なので、知っておくと、どこかで役立つと思いますよ！ 多分。:)
まず、環境と今回の主役となる初期化パラメータの確認から。
optimizer_dynamic_sampling
optimizer_mode
をセッションレベルで制御。ヒント制御しても同じ。
（なお、optimizer_adaptive_plansが発動すると分かりにくくなるので無効化しておきます）
SCOTT@orclpdb1> select banner_full from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SCOTT@orclpdb1> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_mode string ALL_ROWS
SCOTT@orclpdb1> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_dynamic_sampling integer 2
SCOTT@orclpdb1> show parameter optimizer_adaptive_plans
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_adaptive_plans boolean TRUE
前提条件は以下の通り。
1. 表には大量のデータが登録されている。
2. 2表をINNNER JOINするが、WHERE句のないクエリー。
3. 統計の無い状態、無いが動的統計有効、それに統計のある状態、それぞれで検証
普通に考えれば、table full scan + Hash Joinが実行計画として選択されるケースですが、、、どうなりますか。ニヤニヤ。（想像できる結果なのでw
一時表での実行計画から確認してみます。
統計情報なし、動的統計取得なし、Adaptive plansも無効です。 データ量、SQL文ではWHERE句による絞り込み条件も無いため、全表走査＋ハッシュ結合となって欲しいケースですが、 first_row_1 と all_rows の違いは如何に。。。
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 0;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = first_rows_1;
セッションが変更されました。
SCOTT@orclpdb1> create global temporary table hoge_tmp (id number not null primary key, memo varchar2(100)) on commit preserve rows;
表が作成されました。
SCOTT@orclpdb1> create global temporary table hoge_tmp2 (id number not null primary key, memo varchar2(100)) on commit preserve rows;
表が作成されました。
SCOTT@orclpdb1> select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS
------------------------------ ----------
HOGE_TMP
HOGE_TMP2
SCOTT@orclpdb1> select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HOGE_TMP SYS_C0010733
HOGE_TMP2 SYS_C0010735
SCOTT@orclpdb1> select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS SCOPE
------------------------------ ---------- ---------------------
HOGE_TMP SHARED
HOGE_TMP2 SHARED
SCOTT@orclpdb1> select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS SCOPE
------------------------------ ------------------------------ ---------- ---------------------
HOGE_TMP SYS_C0010733 SHARED
HOGE_TMP2 SYS_C0010735 SHARED
SCOTT@orclpdb1> begin for i in 1..100000 loop insert into hoge_tmp values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
2 /
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> begin for i in 1..100000 loop insert into hoge_tmp2 values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
2 /
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS
------------------------------ ----------
HOGE_TMP
HOGE_TMP2
SCOTT@orclpdb1> select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HOGE_TMP SYS_C0010733
HOGE_TMP2 SYS_C0010735
SCOTT@orclpdb1> select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS SCOPE
------------------------------ ---------- ---------------------
HOGE_TMP SHARED
HOGE_TMP2 SHARED
SCOTT@orclpdb1> select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS SCOPE
------------------------------ ------------------------------ ---------- ---------------------
HOGE_TMP SYS_C0010733 SHARED
HOGE_TMP2 SYS_C0010735 SHARED
あらびっくり！（知ってたけどw
100,000行の結合がNested Loop Joinになっています。駆動表は Table full scan しちゃってます。って（WHERE句ないのでここは当然ですがw）
問題は、Nested Loop Joinになっている。INDEX UNIQUE SCANを 100,000回ぐるぐる繰り返しているということになります！ まじですw
一時表で統計情報もなくて、動的統計取得も無効されている影響だな！ そう思ったあなた。そういうケースもありますがw
WHERE句もないSQLでNested Loop Joinを選択してしまうのは危険ですよ。(昔は特殊な事情で、それでもこれで行くか〜というレアなこともなくはなかったですがw 最近はほぼないですからね)
（後半で、統計情報なんて関係ねぇってネタをご用意してありますので、長いですがお付き合いくださいw）
SCOTT@orclpdb1> -- 一時表（Global Temporary Table）のセッション固有統計なし
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 0;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = first_rows_1;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 4089392018
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | HOGE_TMP | 8168 | 518K| 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| HOGE_TMP2 | 1 | 65 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0010735 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
統計
----------------------------------------------------------
25 recursive calls
0 db block gets
119340 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73378 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
では、 all_rows にして再実行します。他の設定は同じです。
はい、見事に、 Table full scan + Hash Join の実行計画が選択されました！！！！
違いは、first_rows_1 であるか、 all_rows であるかだけです。それだけなんです。
SCOTT@orclpdb1> -- 一時表（Global Temporary Table）のセッション固有統計なし
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 0;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = all_rows;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 1628381653
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 1036K| 150 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 8168 | 1036K| 150 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL| HOGE_TMP | 8168 | 518K| 74 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| HOGE_TMP2 | 8168 | 518K| 74 (2)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
統計
----------------------------------------------------------
22 recursive calls
0 db block gets
9334 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73599 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
さらに深掘りしてみましょう。
統計情報の有無が影響しないことを確認してみましょう。一時表なので永続表とは異なる統計情報の持ち方になっていることをお忘れなく。でも、大丈夫ですよ。持ってますからw
SCOTT@orclpdb1> -- 一時表（Global Temporary Table）のセッション固有統計取得
SCOTT@orclpdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP',cascade=>true,no_invalidate=>false);
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP2',cascade=>true,no_invalidate=>false);
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS
------------------------------ ----------
HOGE_TMP
HOGE_TMP2
SCOTT@orclpdb1> select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HOGE_TMP SYS_C0010733
HOGE_TMP2 SYS_C0010735
SCOTT@orclpdb1> select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS SCOPE
------------------------------ ---------- ---------------------
HOGE_TMP SHARED
HOGE_TMP2 SHARED
HOGE_TMP2 100000 SESSION
HOGE_TMP 100000 SESSION
SCOTT@orclpdb1> select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS SCOPE
------------------------------ ------------------------------ ---------- ---------------------
HOGE_TMP SYS_C0010733 SHARED
HOGE_TMP2 SYS_C0010735 SHARED
HOGE_TMP SYS_C0010733 100000 SESSION
HOGE_TMP2 SYS_C0010735 100000 SESSION
一時表のセッション固有統計により駆動表の見積もり行数が、100K 担っている点に注目。大量にデータがヒットすることが、見えていながら、 first_roww_1 という１行目のレスポンスタイムを最短にするため、Nested Loop Joinが行われているます！
統計情報なんて、関係ねぇっ、って感じなのが確認できたので、実は、ほっとしていたりw...
SCOTT@orclpdb1> -- dyamic sampling off
SCOTT@orclpdb1> -- first_rows_1
SCOTT@orclpdb1> -- 一時表（Global Temporary Table）のセッション固有統計あり
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 0;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = first_rows_1;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 4089392018
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 424 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 424 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | HOGE_TMP | 100K| 10M| 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| HOGE_TMP2 | 1 | 106 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0010735 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
Note
-----
- Global temporary table session private statistics used
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
115889 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73599 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
確認するまでもないですが、 all_rows で他の条件は同一のケースも見てみましょう。
こちらは安定の、table full scan + hash joinのままですね。（予想通りですw）
SCOTT@orclpdb1> -- dyamic sampling off
SCOTT@orclpdb1> -- all_rows
SCOTT@orclpdb1> -- 一時表（Global Temporary Table）のセッション固有統計あり
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 0;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = all_rows;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 1628381653
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 20M| | 6154 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 100K| 20M| 11M| 6154 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL| HOGE_TMP | 100K| 10M| | 1110 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| HOGE_TMP2 | 100K| 10M| | 1110 (2)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
Note
-----
- Global temporary table session private statistics used
統計
----------------------------------------------------------
705 recursive calls
14 db block gets
10430 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73378 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
170 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
一時表でもう一つ確認しておきましょう。
一時表（Global Temporary Table）のセッション固有統計なしで、動的統計有効にした場合、 first_rows_1 / all_rows の実行計画はどうなるでしょうか。
一旦、セッションを終了して、一時表を空にします。
SCOTT@orclpdb1> exit
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0との接続が切断されました。
[oracle@localhost ~]$ sqlplus scott@orclpdb1
...略...
SCOTT@orclpdb1> begin for i in 1..100000 loop insert into hoge_tmp values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
2 /
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> begin for i in 1..100000 loop insert into hoge_tmp2 values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
2 /
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS
------------------------------ ----------
HOGE_TMP
HOGE_TMP2
SCOTT@orclpdb1> select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HOGE_TMP SYS_C0010733
HOGE_TMP2 SYS_C0010735
SCOTT@orclpdb1> select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS SCOPE
------------------------------ ---------- ---------------------
HOGE_TMP SHARED
HOGE_TMP2 SHARED
SCOTT@orclpdb1> select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS SCOPE
------------------------------ ------------------------------ ---------- ---------------------
HOGE_TMP SYS_C0010733 SHARED
HOGE_TMP2 SYS_C0010735 SHARED
お〜〜〜〜。変化しました〜、動的統計取得で挙動が変わりますね。。。。とはいえ、 Merg Joinです！
実行計画、最悪ですよね。
重いソート処理を回避するために、主キーをindex full scan（主キー順に読み込む）した後に、 Table Access by index rowid ですよ。みなさん！
次に、table access fullの後に、SORT JOIN してます。。consistent getsもこれまでで最も多いですね。どうせなら table full scan + hash join を選んで欲しかったw
とはいえ、optimizer_mode = first_rows_1にするぐらいだから、動的統計って無効化していることも多いので、有効にするまでは気が回らなそうな気もしますね。
いずれにしてもあまり良い設定の相性ではないのは街がないですね。このケースでは。動的統計のレベルによっても変化してより良い実行計画に変化するとは思いますが。（今回の目的ではないのでその確認まではしません）
SCOTT@orclpdb1> -- first_rows_1
SCOTT@orclpdb1> -- 一時表（Global Temporary Table）のセッション固有統計なし
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = first_rows_1;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 2;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 2412335391
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 569 (2)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 130 | 569 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| HOGE_TMP2 | 97069 | 6161K| 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | SYS_C0010735 | 2 | | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 95480 | 6060K| 566 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | HOGE_TMP | 95480 | 6060K| 566 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
filter("A"."ID"="B"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
23 recursive calls
0 db block gets
19853 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73378 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
同じ条件で、 all_rows に変えてみましょう。おそらくいい感じになるのではないでしょうか（これまで同様に）
SCOTT@orclpdb1> -- all_rows
SCOTT@orclpdb1> -- 一時表（Global Temporary Table）のセッション固有統計なし
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = all_rows;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 2;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 1628381653
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 95481 | 11M| | 4699 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 95481 | 11M| 7184K| 4699 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL| HOGE_TMP | 95480 | 6060K| | 1109 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| HOGE_TMP2 | 97069 | 6161K| | 1110 (2)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
13 recursive calls
0 db block gets
9426 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73599 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
SCOTT@orclpdb1> exit
最後に、永続表での実行計画も確認しておきます。
SCOTT@orclpdb1> drop table hoge_tmp purge;
表が削除されました。
SCOTT@orclpdb1> drop table hoge_tmp2 purge;
表が削除されました。
SCOTT@orclpdb1> create table hoge_tmp (id number not null primary key, memo varchar2(100));
表が作成されました。
SCOTT@orclpdb1> create table hoge_tmp2 (id number not null primary key, memo varchar2(100));
2 /
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> begin for i in 1..100000 loop insert into hoge_tmp2 values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
2 /
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS
------------------------------ ----------
HOGE_TMP
HOGE_TMP2
SCOTT@orclpdb1> select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HOGE_TMP SYS_C0010737
HOGE_TMP2 SYS_C0010739
SCOTT@orclpdb1> select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS SCOPE
------------------------------ ---------- ---------------------
HOGE_TMP SHARED
HOGE_TMP2 SHARED
SCOTT@orclpdb1> select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS SCOPE
------------------------------ ------------------------------ ---------- ---------------------
HOGE_TMP SYS_C0010737 SHARED
HOGE_TMP2 SYS_C0010739 SHARED
永続表のケースで、統計なし、動的統計オフで、first_rows_1の場合は、一時表と同様に駆動表を全表走査した上で、Nested Loop Joinしています。first_rows_1の影響をそのまま受けています。
SCOTT@orclpdb1> -- first_rows_1
SCOTT@orclpdb1> -- 統計なし
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 0;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = first_rows_1;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 4183149614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 130 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | HOGE_TMP | 82 | 5330 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0010739 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| HOGE_TMP2 | 1 | 65 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
統計
----------------------------------------------------------
84 recursive calls
23 db block gets
126963 consistent gets
231 physical reads
4336 redo size
2812231 bytes sent via SQL*Net to client
73378 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
他の条件は同じで、 all_rows に変更した場合同様に、全表走査＋ハッシュ結合（いいですねぇ。バッチ処理ならこれが一番良いですね。
SCOTT@orclpdb1> -- all_rows
SCOTT@orclpdb1> -- 統計なし
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 0;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = all_rows;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 1628381653
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 10660 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 82 | 10660 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| HOGE_TMP | 82 | 5330 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| HOGE_TMP2 | 82 | 5330 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
統計
----------------------------------------------------------
189 recursive calls
5 db block gets
9700 consistent gets
1 physical reads
184 redo size
2812231 bytes sent via SQL*Net to client
73599 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
永続表、統計情報なし、動的統計取得有効、first_rows_1。一時表同様の結果です。永続表と一時表による違いは無さそうですね。これはNLJより避けたいw
SCOTT@orclpdb1> -- first_rows_1
SCOTT@orclpdb1> -- 統計なし
SCOTT@orclpdb1> -- 動的統計有効
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 2;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = first_rows_1;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 1178023564
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 795 (2)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 130 | 795 (2)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| HOGE_TMP | 124K| 7911K| 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | SYS_C0010737 | 2 | | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 92574 | 5876K| 792 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | HOGE_TMP2 | 92574 | 5876K| 792 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
filter("A"."ID"="B"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
181 recursive calls
26 db block gets
16729 consistent gets
182 physical reads
140 redo size
2812231 bytes sent via SQL*Net to client
73599 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
24 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
永続表、統計情報なし、動的統計取得有効、all_rowsも、一時表のケースと同様。安定して、全表走査＋ハッシュ結合が行われています。 all_rows にするべきSQLですからね。
SCOTT@orclpdb1> -- all_rows
SCOTT@orclpdb1> -- 統計なし
SCOTT@orclpdb1> -- 動的統計有効
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 2;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = all_rows;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 3316548036
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92574 | 11M| | 5148 (2)| 00:00:01 |
|* 1 | HASH JOIN | | 92574 | 11M| 6968K| 5148 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL| HOGE_TMP2 | 92574 | 5876K| | 1173 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| HOGE_TMP | 124K| 7911K| | 1177 (2)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
32 recursive calls
0 db block gets
9438 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73378 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
いよいよ最後、動的統計取得を無効化して、静的統計による挙動を確認します。
SCOTT@orclpdb1> -- 統計取得
SCOTT@orclpdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP',cascade=>true,no_invalidate=>false);
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP2',cascade=>true,no_invalidate=>false);
PL/SQLプロシージャが正常に完了しました。
SCOTT@orclpdb1> select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS
------------------------------ ----------
HOGE_TMP 100000
HOGE_TMP2 100000
SCOTT@orclpdb1> select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HOGE_TMP SYS_C0010737 100000
HOGE_TMP2 SYS_C0010739 100000
SCOTT@orclpdb1> select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME NUM_ROWS SCOPE
------------------------------ ---------- ---------------------
HOGE_TMP 100000 SHARED
HOGE_TMP2 100000 SHARED
SCOTT@orclpdb1> select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
TABLE_NAME INDEX_NAME NUM_ROWS SCOPE
------------------------------ ------------------------------ ---------- ---------------------
HOGE_TMP SYS_C0010737 100000 SHARED
HOGE_TMP2 SYS_C0010739 100000 SHARED
統計情報あり、動的統計有効ですが、動作しないはずですね。統計情報は最新ですし。 first_rows_1では期待した結果（良いという意味ではないw）が得られています。
駆動表を全表走査してNested Loop Joinが行われています。動的統計取得の副作用で、Merge Joinになることもなかったようですね。
SCOTT@orclpdb1> -- 統計あり
SCOTT@orclpdb1> -- 動的統計有効
SCOTT@orclpdb1> alter session set optimizer_adaptive_plans = false;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_dynamic_sampling = 2;
セッションが変更されました。
SCOTT@orclpdb1> alter session set optimizer_mode = first_rows_1;
セッションが変更されました。
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
100000行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 4183149614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 424 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 424 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 424 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | HOGE_TMP | 100K| 10M| 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0010739 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| HOGE_TMP2 | 1 | 106 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
統計
----------------------------------------------------------
5 recursive calls
0 db block gets
126786 consistent gets
0 physical reads
0 redo size
2812231 bytes sent via SQL*Net to client
73599 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SCOTT@orclpdb1> set autot off
同一条件で、 all_rows の場合です。こちらも想定通り、全表走査＋ハッシュ結合になっています。
最後に、 optimizer_mode をチューニングのゴールに合わせて、バッチ、分析系、そして、OLTPと、
all_rows または、first_rows_n のいずれか正しく設定することも想定外の実行計画を防ぐことに役立つか、お分かりいただけたのではないでしょうか？
効果の薄い機能ではなく、重要な役目をもつ、 optimizer_mode、お忘れなく。ヒントでも使えます。状況に合わせて使い分けることをお勧めします:)
おまけ 昔、OTHER_XML列からOUTLINEを取り出すなんてネタ書いてましたが、しっかりと、optimizer_modeに対応するヒントが含まれています。
OTHER_XMLの中身 / Mac De Oracle / 2015年12月 4日 (金) https://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2015/12/other_xml-7f15.html
では、また。
良いお年をお迎えください。
Enjoy! SQL and Optimizer Features! ：）
今回利用したSQLなど
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = first_rows_1;
create global temporary table hoge_tmp (id number not null primary key, memo varchar2(100)) on commit preserve rows;
create global temporary table hoge_tmp2 (id number not null primary key, memo varchar2(100)) on commit preserve rows;
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
begin for i in 1..100000 loop insert into hoge_tmp values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
/
begin for i in 1..100000 loop insert into hoge_tmp2 values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
/
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
-- 一時表（Global Temporary Table）のセッション固有統計なし
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = first_rows_1;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- 一時表（Global Temporary Table）のセッション固有統計なし
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = all_rows;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- 一時表（Global Temporary Table）のセッション固有統計取得
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP',cascade=>true,no_invalidate=>false);
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP2',cascade=>true,no_invalidate=>false);
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
-- dyamic sampling off
-- first_rows_1
-- 一時表（Global Temporary Table）のセッション固有統計あり
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = first_rows_1;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- dyamic sampling off
-- all_rows
-- 一時表（Global Temporary Table）のセッション固有統計あり
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = all_rows;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
+++ 一時表（Global Temporary Table）のセッション固有統計なしで、動的統計有効 +++
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_mode = first_rows_1;
alter session set optimizer_dynamic_sampling = 2;
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
begin for i in 1..100000 loop insert into hoge_tmp values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
/
begin for i in 1..100000 loop insert into hoge_tmp2 values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
/
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
-- first_rows_1
-- 一時表（Global Temporary Table）のセッション固有統計なし
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_mode = first_rows_1;
alter session set optimizer_dynamic_sampling = 2;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- all_rows
-- 一時表（Global Temporary Table）のセッション固有統計なし
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_mode = all_rows;
alter session set optimizer_dynamic_sampling = 2;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
--パーマネント表でも同じ
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = first_rows_1;
create table hoge_tmp (id number not null primary key, memo varchar2(100));
create table hoge_tmp2 (id number not null primary key, memo varchar2(100));
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
begin for i in 1..100000 loop insert into hoge_tmp values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
/
begin for i in 1..100000 loop insert into hoge_tmp2 values(i,lpad('x',100,'x')); if mod(i,1000) = 0 then commit; end if; end loop; end;
/
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
-- first_rows_1
-- 統計なし
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = first_rows_1;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- all_rows
-- 統計なし
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 0;
alter session set optimizer_mode = all_rows;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- first_rows_1
-- 統計なし
-- 動的統計有効
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 2;
alter session set optimizer_mode = first_rows_1;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- all_rows
-- 統計なし
-- 動的統計有効
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 2;
alter session set optimizer_mode = all_rows;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- 統計取得
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP',cascade=>true,no_invalidate=>false);
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE_TMP2',cascade=>true,no_invalidate=>false);
select table_name,num_rows from user_tables where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows from user_indexes where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,num_rows,scope from user_tab_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
select table_name,index_name,num_rows,scope from user_ind_statistics where table_name in ('HOGE_TMP','HOGE_TMP2');
-- first_rows_1
-- 統計あり
-- 動的統計有効
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 2;
alter session set optimizer_mode = first_rows_1;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
-- all_rows
-- 統計あり
-- 動的統計有効
alter session set optimizer_adaptive_plans = false;
alter session set optimizer_dynamic_sampling = 2;
alter session set optimizer_mode = all_rows;
set autot trace exp stat
select * from hoge_tmp a inner join hoge_tmp2 b on a.id = b.id;
set autot off
最近のコメント