Mac De Oracle: 実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 21 / No.56 / INLIST ITERATOR と Sub Query と STATISTICS COLLECTOR

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

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

最近のトラックバック

[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を表示する

当サイトについて

プライバシーポリシー

■プライバシーポリシー

■個人情報の利用目的

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

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

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

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

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

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

　当サイトでは、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プライバシー規約をご覧ください。

■免責事項

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

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

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

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

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 21 / No.56 / INLIST ITERATOR と Sub Query と STATISTICS COLLECTOR Tweet

Previously on Mac De Oracle...

Day 20は, DISTINCT Eliminationを取り上げました. 古くから実装されているElimination機能ですが, 知ってる方いましたかね？w　古すぎでしょうかw. とはいえ, この恩恵を得ている方も実は多いかもしれませんよ. クソデカクエリー追いきれてないかもしれないですし.

では, Day 21の窓を開けましょう！

久々に, 実行計画は, SQL文のレントゲン写真だ！　っぽく, 実行計画に現れるOperationを楽しんで診ていきましょう！

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

SCOTT@orclpdb1> select banner from v$version;

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

今日のテーマはIN条件とサブクエリー. IN条件といえば, 昔, 悩ませ過ぎは及ばざるがごとし #1という記事を書いてました. （そのころはまだIT業界に居なかった？　方も多そうですが）
IN条件, 索引を使って少量のデータをアクセスするには, 以下の実行計画にあるように, 　INLIST ITERATORで繰り返しアクセスするは問題ないわけですが, 大量にあるとかなり性能面で影響が出ます. （悩ませ過ぎは及ばざるがごとし #1などは, ハードパース時間に影響がでたケースです）

SCOTT@orclpdb1> @day21
  1  SELECT *
  2  FROM
  3    emp
  4  WHERE
  5*   empno IN (7369,7499)

     EMPNO ENAME                          JOB                                MGR HIREDATE        SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 80-12-17        800                    20
      7499 ALLEN                          SALESMAN                          7698 81-02-20       1600        300         30

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

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |    78 |     2   (0)| 00:00:01 |
|   1 |  IINLIST ITERATOR            |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    78 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("EMPNO"=7369 OR "EMPNO"=7499)

性能問題も多かった影響なのか？！, Oracle Database 9i R2頃以降, IN条件が, 結合に書き換えられるように（私の感覚でしかないのですが, 最近は, より積極的に書き換えが行われる傾向があるように感じます）なっています.
以下のようにサブクエリーを利用しているケースが典型例ですね. IN条件だけでなく, EXISTS演算子や, スカラー副問合せなどもこの書き換えの対象です. この書き換えは, Subquery Unnestingと呼ばれています. ご存知の方も多いですよね. 今日の主役はそれでなく, INLIST ITERATOR　の方ですが, これ両方話さないとOperationの向き不向きが見えないのでw

  1  SELECT
  2    *
  3  FROM
  4    emp
  5  WHERE
  6    deptno IN (
  7      SELECT
  8        deptno
  9      FROM
 10        dept
 11      WHERE
 12        dname IN ('SALES','ACCOUNT')
 13*   )

     EMPNO ENAME                          JOB                                MGR HIREDATE        SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
      7499 ALLEN                          SALESMAN                          7698 81-02-20       1600        300         30
      7521 WARD                           SALESMAN                          7698 81-02-22       1250        500         30
      7654 MARTIN                         SALESMAN                          7698 81-09-28       1250       1400         30
      7698 BLAKE                          MANAGER                           7839 81-05-01       2850                    30
      7844 TURNER                         SALESMAN                          7698 81-09-08       1500          0         30
      7900 JAMES                          CLERK                             7698 81-12-03        950                    30

6行が選択されました. 

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

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     8 |   416 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |     8 |   416 |     5   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |        |     8 |   416 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPT   |     2 |    26 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IX_EMP |     4 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |     4 |   156 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("DNAME"='ACCOUNT' OR "DNAME"='SALES')
   4 - access("DEPTNO"="DEPTNO")

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

前述した実行計画の Noteに, - this is an adaptive plan　と気になる情報が出ています. これ, 実行計画上は, Subquery Unnestingされて, Nested Loop Join　(NLJ) になっていますが, 駆動表のヒット件数に応じて, それ以外の結合メソッドに
へ切り替わる可能性があることを示しています. これは, adaptive planと呼ばれている機能です.
実際, NLJなのかそれ以外なのかをみる方法は, SQL Monitor, Actual Planを利用する方法と, 以下のように, Adaptive Planを表示させ, どちらで動作したのかを確認する方法があります.

ここで登場するのが, Adaptive Planの鍵になる, STATISTICS COLLECTOR というOperationです. ここで駆動表の件数をみつつ, これは！　HJ向きのと判断すれば, NLJ から HJ へ切り替えることになります.
今回は, NLJ のままですね. ()

  1* SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  cuxwr51s6gs61, child number 0
-------------------------------------
SELECT   * FROM   emp WHERE   deptno IN (     SELECT       deptno
FROM       dept     WHERE       dname IN ('SALES','ACCOUNT')   )

Plan hash value: 4207756064

------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |        |       |       |     5 (100)|          |
|- *  1 |  HASH JOIN                    |        |     8 |   416 |     5   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                |        |     8 |   416 |     5   (0)| 00:00:01 |
|     3 |    NESTED LOOPS               |        |     8 |   416 |     5   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR      |        |       |       |            |          |
|  *  5 |      TABLE ACCESS FULL        | DEPT   |     2 |    26 |     3   (0)| 00:00:01 |
|  *  6 |     INDEX RANGE SCAN          | IX_EMP |     4 |       |     0   (0)|          |
|     7 |    TABLE ACCESS BY INDEX ROWID| EMP    |     4 |   156 |     1   (0)| 00:00:01 |
|-    8 |   TABLE ACCESS FULL           | EMP    |     4 |   156 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - access("DEPTNO"="DEPTNO")
   5 - filter(("DNAME"='ACCOUNT' OR "DNAME"='SALES'))
   6 - access("DEPTNO"="DEPTNO")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

残り, 4日, 最終日はクリスマスで, 日曜日じゃないか！今年は.
大きめのネタをぶん投げて, おまけブログでまとめる感じにするか. 悩みどころだ. それとも軽めのネタで最後まで通すか.

明日も担当は, 私ですよ. （全部俺アドベントカレンダー, 来年はどうしようw　なんの苦行だという感じw　でも, それ楽しんでる俺は...w）

では, また　:)

投稿日時 2022年12月21日 (水) 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年）９年ぶりに正式リリースされたのでいろいろあそんでみたくなった！

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

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

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 21 / No.56 / INLIST ITERATOR と Sub Query と STATISTICS COLLECTOR Tweet

コメント

コメントを書く

Mac De Oracle

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

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

実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 21 / No.56 / INLIST ITERATOR と Sub Query と STATISTICS COLLECTOR Tweet

コメント

コメントを書く

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 21 / No.56 / INLIST ITERATOR と Sub Query と STATISTICS COLLECTOR Tweet