Mac De Oracle: 実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

オンラインショッピング

2023年2月
日	月	火	水	木	金	土
			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

最近のトラックバック

[memo]メモを取りつつ楽しめるようなスタイル (wmo6hash::blog)
ダーリンはGoogleで軽食なり。 (今日のゴハン)
�� プログラムは読む時間の方が長い - 遥か彼方の彼方から (�� プログラムは読む時間の方が長い - 遥か彼方の彼方から)
今流行のケブンリッジ関数・・・しってました！？ (あにずむ)
poken (drmarのメモ)
Photo exhibition "Natural Faces of AIDS Orphans"　写真展「エイズ孤児の素顔」@JICA地球ひろば　広尾 (メガ放談)
創る心とおもてなし―第1回 Webエンジニア料理対決に参加する (Kwappa開発室)
創る心とおもてなし～第一回webエンジニア料理対決に参加した (Mac De Oracle)
徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (14) (キムラデービーブログ)
Oracle de XMLDB #18 (discus)

Oracle

OTN US

Apple

その他

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

Googleの広告

RSS

RSSを表示する

当サイトについて

プライバシーポリシー

プライバシーポリシー

「個人情報の保護に関する法律」に基づき、「個人情報保護の基本方針（プライバシーポリシー）」を
定め個人情報の適切な管理・保護に努めることを表明します。

個人情報保護の基本方針（プライバシーポリシー）

1.　個人情報の利用目的をできるだけ特定し、特定された利用目的の達成に必要な範囲を超えて利用
　　することはありません。
2.　個人情報を偽りその他不正な手段による取得しません。取得したときは、本人に速やかに利用目的
　　を通知又は公表します。
3.　個人情報を利用目的の範囲内で正確かつ最新の内容に保つように努めます。
4.　個人情報の漏洩や滅失を防ぐために、必要かつ適切な安全管理措置を講じます。
5.　あらかじめ本人の同意を得ないで第三者に個人情報を提供することはありません。
　　ただし、法令に基づく場合、人の生命、身体又は財産の保護に必要な場合、公衆衛生・児童の健全育成に
　　特に必要な場合、国等に協力する場合は除きます。

2022年12月18日 (日) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination Tweet

Previously on Mac De Oracle...
Day 17は, order by Elimination にフォーカスをあてました. チューニングの現場で気づいたのが最初だったと思いますが, SQL文を見ていて, これ無駄なソートだなぁなんて思いながら実行計画という名のレントゲンをみていたら, おお！　NOSORTとかではなく, ORDER BY自体が消されてる！　賢い！　と.

では, Day 18のお題, Join Eliminationです. (登場したのは10gR2 ぐらいのはずですが, 間違ってたらツッコミ歓迎)
この排除系書き換えも, 実行計画という名のレントゲンシリーズでは, まだ紹介していなかったので, 今回は, ヒントで無効化する例も含め, 軽めの内容でw, 診ていきたいと思います. （参照整合性制約アレルギーネタを思い出すw）

いつもと同じように 21c で確認します.

SCOTT@orclpdb1> select banner from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

対象となる表には外部参照整合制約があることが前提です.

customers表を結合していますが, 参照整合性制約でcustomers表に存在する顧客しか注文できないという制約があります. つまり, この参照整合性制約があるため, INNER JOINやEXISTSを利用して存在チェックする必要はないということを意味しています.
Join EliminationによりSQLが書き換えられ結合が排除されていることがわかります. 実行計画には, ORDER_PKをIndex Only Scanしているだけで, customers表やcustomersの索引を結合しているOperationは含まれていない！！！
結合しないので, 結合のコスト及び, customers表やcustomersの索引へのアクセスコストが削減されています. 実行統計からは, Pysical Readや, Buffer Getsの低下という形で現れてきます.

とはいえ, 参照整合性制約アレルギーのお持ちの方も多く, 一生目にすることのない方々も, 残念ながら多いのも事実です.
参照整合性制約アレルギーが発症してしまうと, 一生付き合っていくことになちゃいますからね（大抵の場合）

Day19の内容とDay18のネタを入れ替えたので, day19.sqlを実行しているところは気にしないでくださいw

OE@orclpdb1> @day19
  1  SELECT
  2    order_id
  3  FROM
  4    orders o
  5    INNER JOIN customers c
  6    ON
  7      o.customer_id = c.customer_id
  8  WHERE
  9*   order_id < 2400

  ORDER_ID
----------
      2354
      2355
      2356
      2357
      2358

...略...

      2396
      2397
      2398
      2399

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

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    46 |   184 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| ORDER_PK |    46 |   184 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("O"."ORDER_ID"<2400)

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1482  bytes sent via SQL*Net to client
         85  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         46  rows processed
/code>

NO_ELIMINATE_JOINヒントを利用し, Join Eliminationを抑止してみましょう.
NO_ELIMINATE_JOINは, 使うことがあるのか？　と思う方もいるかもしれませんが, 例えば, 不具合で7445とか, その他結果不正などに当たった時, かつ, 局所的対応で回避できそうなケースでは, 該当SQLにヒントを埋め込み, Join Eliminationの抑止で回避したりします.
どこで起きるかわからんので, インスタンスレベルで止めるケースもなくなないですが, そういう場合は, 隠しパラメータで無効化するのが一般的です. （ほぼ使わないと思いますが, ELIMINATE_JOIN　でJoin Eliminationを利用できます. インスタンスレベルで無効化している状態で, 特定のSQLだけはJoin Eliminationしたいという場合に使うぐらいですね. 滅多にないと思いますが）

  1  SELECT
  2    /*+
  3      NO_ELIMINATE_JOIN(c)
  4    */
  5    order_id
  6  FROM
  7    orders o
  8    INNER JOIN customers c
  9    ON
 10      o.customer_id = c.customer_id
 11  WHERE
 12*   order_id < 2400

  ORDER_ID
----------
      2354
      2355
      2356
      2357
      2358

...略...

      2392
      2393
      2394
      2395
      2396
      2397
      2398
      2399

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

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |    46 |   552 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |              |    46 |   552 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS       |    46 |   368 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | ORDER_PK     |    46 |       |     1   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN                  | CUSTOMERS_PK |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - filter("O"."CUSTOMER_ID">0)
   3 - access("O"."ORDER_ID"<2400)
   4 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1482  bytes sent via SQL*Net to client
         85  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         46  rows processed
/code>

アドベントカレンダー終わったら, もう, 今年も残りわずか....

ということで, 出口が見えつつある, アドベントカレンダー全部俺, 明日も, 俺が書きますw
では, また.

参考）
Join Elimination（結合の排除）と参照整合性制約　/ FAQ
db tech showcase Tokyo 2013 - A35 特濃JPOUG：潮溜まりでジャブジャブ, SQLチューニングの「参照整合性制約アレルギー」を参照のこと

投稿日時 2022年12月18日 (日) 00時00分 Oracle, チューニング, SQL, Oracle Database 11g, Oracle Database 11g Release 2, Oracle Database 12c release 1, パフォーマンス, Advent Calendar, Oracle Database 12c Release 2, Oracle Database 18c, Oracle Database 19c, Oracle Database 21c | 固定リンク | 0

Mac De Oracle

MacOS X で動作する Oracle　が正式にリリースされたのだ！ 1996年にリリースされた、Personal Oracle7 for MacOS以来、封印されていたMacOS版が、（2005年）９年ぶりに正式リリースされたのでいろいろあそんでみたくなった！

GaragebandとiMovieで

検索

Twitter

オンラインショッピング

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2022年12月18日 (日) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination Tweet

コメント

コメントを書く

Mac De Oracle

GaragebandとiMovieで

検索

Twitter

オンラインショッピング

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2022年12月18日 (日) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination Tweet

コメント

コメントを書く

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination Tweet