DROP SHADOW CTRL


GaragebandとiMovieで



検索

  • Google
フォト

Twitter

2024年12月
日 月 火 水 木 金 土
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

気まぐれ写真館

  • spider lily - 白と赤の彼岸花（曼珠沙華）

カテゴリー

Oracle

Apple

その他

  • ブログ de ノベルティ
    ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告



RSS

ブログ:ココログ

当サイトについて

プライバシーポリシー
■プライバシーポリシー

■個人情報の利用目的

　当ブログでは、メールでのお問い合わせ、ブログへのコメントの際、名前（ハンドルネーム）等の個人情報をご登録いただく場合がございます。これらの個人情報は質問に対する回答などでご連絡する場合や、スパムコメント抑止のために利用させていただくものであり、個人情報をご提供いただく際の目的以外では利用いたしません。

■個人情報の第三者への開示

　当サイトでは、個人情報は適切に管理し、以下に該当する場合を除いて第三者に開示することはありません。

  • 本人のご了解がある場合
  • 法令等への協力のため、開示が必要となる場合

個人情報の開示、訂正、追加、削除、利用停止については、ご本人からの個人データの開示、訂正、追加、削除、利用停止のご希望の場合には、ご本人であることを確認させていただいた上で速やかに対応させていただきます。

■アクセス解析ツールについて

　当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。このトラフィックデータは匿名で収集されており、個人を特定するものではありません。この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。

  • 目的：アクセス数を把握するため
  • 送信先：Google LCC
  • Googleは受け取った情報をどのように利用するか：Googleの規約に従います
  • 送信される情報：以下４点(R), ユーザー数、セッションの統計情報、おおよその位置情報、ブラウザとデバイスの情報

■広告の配信について

当サイトはGoogleとAmazonの広告を配信しています。

Googleについて

Google及びGoogleのパートナー（第三者配信事業者）の提供する広告を設置しております。その広告配信にはCookieを使用し、当サイトやその他のサイトへの過去のアクセス情報に基づいて広告を配信します。

Google が広告 Cookie を使用することにより、当サイトや他のサイトにアクセスした際の情報に基づいて、Google やそのパートナーが適切な広告を表示しています。

お客様はGoogleアカウントの広告設定ページ（https://adssettings.google.com/u/0/authenticated）で、パーソナライズ広告を無効にできます。また aboutads.info のページにアクセスして頂き、パーソナライズ広告掲載に使用される第三者配信事業者のCookieを無効にできます。

その他、Googleの広告におけるCookieの取り扱い詳細については、Googleのポリシーと規約ページ（https://policies.google.com/technologies/ads）をご覧ください。

Amazonについて

AmazonはアソシエイトリンクのURLに含まれるパラメータを通じて利用者が直前に閲覧したアソシエイトサイトに関する情報などを収集し、これをアソシエイトパートナーに対する紹介料の算定、報告及び支払いに使用したり、アソシエイトパートナーによる複数のウェブサイトや商品戦略毎のパフォーマンスの分析に供したりしています。

  • アソシエイトパートナーの利用目的：閲覧の傾向や履歴の分析のため広告効果の分析のため
  • 外部送信プログラム名：Amazonアソシエイトプログラム
  • 送信先の会社名：アマゾンジャパン合同会社
  • 送信する情報の内容：閲覧した内容についての情報送信先の利用目的：自社の広告宣伝を目的とした広告情報の掲載のため、ウェブサイトやアプリの監視のため

Amazonによる個人に関する情報の取得及び取扱いに関する方針につきましてはAmazon.co.jpプライバシー規約をご覧ください。

■免責事項

　当サイトからリンクやバナーなどによって他のサイトに移動された場合、移動先サイトで提供される情報、サービス等について一切の責任を負いません。当サイトのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、誤情報が入り込んだり、情報が古くなっていることもございます。当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

■プライバシーポリシーの変更について

　当サイトは、個人情報に関して適用される日本の法令を遵守するとともに、本ポリシーの内容を適宜見直しその改善に努めます。
修正された最新のプライバシーポリシーは常に本ページにて開示されます。

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

2014年12月 9日 (火) / Author : Hiroshi Sekiguchi.

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

このエントリは 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


以下のような２表を用意しました。他のネタに作った表なのですごーく適当ですが、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の有効範囲が広めになる傾向があります。しかし、このデータを登録する前に統計情報を取得（２件の時に）したため、実データと統計情報は大きく乖離しています。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の実行計画。ほうほうって感じです。

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

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

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

ID=４の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さんです。お楽しみに〜。

投稿日時 2014年12月 9日 (火) 00時05分 Oracle, JPOUG, Advent Calendar | |

トラックバック


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

コメント

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分

コメントを書く