Oracle Database Technology Night #48　津島博士のパフォーマンス講座 - SQLチューニングは実行計画から

Oracle Technology Night #48 津島博士のパフォーマンス講座 SQLチューニングは実行計画から津島浩樹日本オラクル株式会社 COE本部
データベース・ソリューション部 2021年 9月30日

Safe harbor statement The following is intended to outline our
general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 2 Copyright © 2021, Oracle and/or its affiliates.

Program agenda Copyright © 2021, Oracle and/or its affiliates. 3
実行計画の要素実行計画とは実行計画の見方実行計画の比較実行計画の要素実行計画とは実行計画の見方実行計画の比較 2 3 4 1 2 3 4 1

チューニングが必要なSQLを特定し改善すること 1. チューニングが必要なSQLの特定 • AWR/ADDMやSTATSPACKなどからリソース消費の多いSQLを確認する 2. パフォーマンス低下の原因を調べる • AWR/ADDMやSTATSPACKなどの待機イベントを確認する 3.
原因がCPUであれば実行計画が原因の可能性がある • CPUでなければその待機イベントを削減するチューニングを行う • I/Oのときには実行計画の可能性もある（索引スキャンが全表スキャンになっているなど） SQLが特定できたらSQLチューニング・アドバイザで分析できるが、Tuning Packが必要なので使用できない場合もある • AWRやSTATSPACKのみしか情報がないなど • SQLチューニング・アドバイザで解決できない SQLのパフォーマンス分析やチューニングを行うには実行計画に関する知識も必要 SQLチューニングとは Copyright © 2021, Oracle and/or its affiliates. 4

• 実行計画は、SQL文の実行に必要な詳細なステップを示す • 各ステップは、行を生成したり消費したりするデータベース操作のセットとして表される • 操作の順序と実装は、問合せ変換と物理的最適化手法の組み合わせを使用して、
オプティマイザによって決定される • 表示は一般的に表形式で表示されますが、実際の計画はツリー型です実行計画とは Copyright © 2021, Oracle and/or its affiliates. 5 HASH JOIN TABLE ACCESS SALES 計画のツリー型 TABLE ACCESS PRODUCTS GROUP BY 計画の表形式 SELECT prod_category, avg(amount_sold) FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY prod_category;

SQL文が発行されるとどうなるか？実行計画とは Copyright © 2020, Oracle and/or its affiliates. 6
User Library Cache Shared SQL Area Shared Pool Cn C1 C2 … 3 Optimizer Oracle Database Code Generator 1 4 SQL Execution Syntax Check Semantic Check Shared Pool check 2 Parsing

オプティマイザの仕組み実行計画とは Copyright © 2020, Oracle and/or its affiliates. 7
Query Transformation 問合せテキストを書き換えて、より効率的に処理できるようにします Plan Generator 各SQLに対して、異なるアクセス・パスや結合タイプを使用した複数のプランが生成されます。各プランはコスト計算され、最小コストのプランが使用されます。 Cost Estimator コストは、操作の実行に使用されるCPU 時間とディスクI/Oの数の見積もりです Optimizer スキーマ定義、統計

なぜ実行計画を確認するのか最適な実行計画を作成しないときがあるから • SQLが最適でない（最適な実行計画にならないSQLである） • Query Transformationが動作する • 最適な索引が作成されていない •
オプティマイザ統計が古い（正しくない） • オプティマイザ統計にも限界がある実行計画とは Copyright © 2021, Oracle and/or its affiliates. 8 実行計画を見ると遅くなっている理由を特定できる • アクセス方法、結合方法、結合タイプ、結合順序、パラレル・データ分散方法などが最適でない

Explain plan for <SQL>（出力はDBMS_XPLAN.DISPLAY関数） • 実際にはSQLは実行されない（実際の実行計画と異なる場合がある） SQL*PlusのAUTOTRACEコマンド • set autotrace
traceonly explain以外は実際にSQLを実行 SQLトレース • SQLのトレースを取得 • Tkprofコマンドによりトレースファイルからプランを取得確認方法実行計画とは Copyright © 2021, Oracle and/or its affiliates. 9 • 再実行が必要 • plan_tableが必要 • 負荷が高い • 常に取得できない次の実行計画は正しく取得できない • bind peek機能 • 12cからの適応問合せ最適化機能など

確認方法（実行計画のキャッシュ機能） V$SQL及びV$SQL_PLAN（Oracle9iから） • 共有プールのSQLの実行計画をV$SQL_PLANビューを使用して検索 DBMS_XPLAN.DISPLAY_CURSOR関数（Oracle Database 10gから）<=第32回 • 実行時の統計も出力リアルタイムSQL監視（Oracle
Database 11gから） • 実行中の実行計画も出力（Tuning Packが必要） AWR（Oracle Database 10gから） / STATSPACK（Oracle9iから）からの出力 • レベルによってスナップショットにSQLの実行計画が含まれる • AWR（STATISTICS_LEVEL=TYPICAL） ⇒ $ORACLE_HOME/rdbms/admin/awrsqrpt.sql（第32回） • STATSPACK（スナップショットLEVELが6以上） ⇒ $ORACLE_HOME/rdbms/admin/sprepsql.sql（第4回） • SQL監視レポートの出力（Oracle Database 12cから）<=第67回実行計画とは 10 Copyright © 2021, Oracle and/or its affiliates.

確認方法（DBMS_XPLAN.DISPLAY_CURSOR関数）見積もり以外に実行時の統計も出力できる（STATISTICS_LEVEL=ALLまたはヒント）実行計画とは 11 SQL> SELECET /*+ GATHER_PLAN_STATISTICS */ …
; SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id=>'<sql_id>',format=>'typical allstats last')); 実行計画 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts| E-Rows|E-Bytes| Cost (%CPU)| E-Time | A-Rows| A-Time | ------------------------------------------------------------------------------------------------- ------------------------------------------- Buffers| OMem| 1Mem| Used-Mem| Used-Tmp| ------------------------------------------- ＜実行統計＞ Starts：実行された回数 Buffers：バッファのアクセス数 A-Rows：処理行数 A-Time：処理時間 Used-Mem：使用されたメモリサイズと処理方法（0:Optimal,1:1-pass,M:Multi-pass） Used-Tmp：使用されたTEMPサイズ省略すると最後のSQL Copyright © 2021, Oracle and/or its affiliates.

確認方法（リアルタイムSQL監視）実行中の実行計画を表示（デフォルトで5秒以上のSQL、MONITORヒントで強制的に）実行計画とは時間のかかっているSQLが自動的に監視されリストされる (経過時間等でソート可能) このSQL実行全体の統計実行計画のステップごとの統計など
ステップごとの待機イベント Copyright © 2021, Oracle and/or its affiliates. 12

実行計画の要素実行計画とは実行計画の見方実行計画の比較 2 3 4 1

実行計画の要素 Copyright © 2021, Oracle and/or its affiliates. 14 -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | 1 | 211 | 12 (9)| | 3 | NESTED LOOPS | | 1 | 185 | 11 (10)| |* 4 | HASH JOIN | | 1 | 155 | 10 (10)| | 5 | MERGE JOIN CARTESIAN | | 107 | 8774 | 6 (0)| |* 6 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 30 | 3 (0)| | 7 | BUFFER SORT | | 107 | 5564 | 3 (0)| | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5564 | 3 (0)| | 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7811 | 3 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 1 (0)| |* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| | 13 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 26 | 1 (0)| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."MANAGER_ID"="E"."EMPLOYEE_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."SALARY"+("E"."SALARY"+"E"."COMMISSION_PCT")>="E"."SALARY"+("E". "SALARY"+"E"."COMMISSION_PCT")) 6 - filter("D"."DEPARTMENT_NAME"='Sales') 10 - filter("D"."DEPARTMENT_NAME"='Sales') 11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("E"."JOB_ID"="J"."JOB_ID")

実行計画の要素（ID） Copyright © 2021, Oracle and/or its affiliates. 15 -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | 1 | 211 | 12 (9)| | 3 | NESTED LOOPS | | 1 | 185 | 11 (10)| |* 4 | HASH JOIN | | 1 | 155 | 10 (10)| | 5 | MERGE JOIN CARTESIAN | | 107 | 8774 | 6 (0)| |* 6 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 30 | 3 (0)| | 7 | BUFFER SORT | | 107 | 5564 | 3 (0)| | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5564 | 3 (0)| | 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7811 | 3 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 1 (0)| |* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| | 13 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 26 | 1 (0)| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."MANAGER_ID"="E"."EMPLOYEE_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."SALARY"+("E"."SALARY"+"E"."COMMISSION_PCT")>="E"."SALARY"+("E". "SALARY"+"E"."COMMISSION_PCT")) 6 - filter("D"."DEPARTMENT_NAME"='Sales') 10 - filter("D"."DEPARTMENT_NAME"='Sales') 11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("E"."JOB_ID"="J"."JOB_ID") Id列で各ステップの識別が容易に（*は、ステップが計画表の下に記載されている述語情報に基づいていることを示す）

実行計画の要素（OPERATION） Copyright © 2021, Oracle and/or its affiliates. 16 -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | 1 | 211 | 12 (9)| | 3 | NESTED LOOPS | | 1 | 185 | 11 (10)| |* 4 | HASH JOIN | | 1 | 155 | 10 (10)| | 5 | MERGE JOIN CARTESIAN | | 107 | 8774 | 6 (0)| |* 6 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 30 | 3 (0)| | 7 | BUFFER SORT | | 107 | 5564 | 3 (0)| | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5564 | 3 (0)| | 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7811 | 3 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 1 (0)| |* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| | 13 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 26 | 1 (0)| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."MANAGER_ID"="E"."EMPLOYEE_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."SALARY"+("E"."SALARY"+"E"."COMMISSION_PCT")>="E"."SALARY"+("E". "SALARY"+"E"."COMMISSION_PCT")) 6 - filter("D"."DEPARTMENT_NAME"='Sales') 10 - filter("D"."DEPARTMENT_NAME"='Sales') 11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("E"."JOB_ID"="J"."JOB_ID") Operation列には、以下を含む計画を実行するための実際の手順が表示される: • データ・アクセス方法 • 結合方法とタイプ • その他

アクセス方法（データの取得）アクセス方法説明 TABLE ACCESS [INMEMORY|STORAGE] FULL テーブルからすべての行を読み込み、where句の述語を満たさないものをフィルタリングします。索引なしやDOP設定の場合などに使用されます（INMEMORYはDBIM、STORAGE はExadataのとき）。
TABLE ACCESS BY INDEX ROWID ROWIDは、行を含むデータファイルとデータブロック、およびそのブロック内の行の場所を指定します。 ROWIDが索引またはwhere句で指定されている場合に使用されます。 INDEX UNIQUE SCAN 1つの行のみが返されます。 SQL文に単一の行のみがアクセスされることを保証する UNIQUEまたは主キー制約が含まれている場合に使用されます。 INDEX RANGE SCAN 隣接する索引エントリにアクセスし，ROWID値を返す非ユニーク索引では等価(=)を，ユニーク索引では範囲述語（<.>，betweenなど）を使用します。 INDEX SKIP SCAN 索引の先頭をスキップして残りを使用します。先頭の列に識別性の高い値(異なる値)が少なく、非先頭の列に識別性の高い値が多い場合に有効です。 INDEX FULL SCAN 索引のすべてのリーフブロックを処理しますが、最初のリーフブロックを見つけるのに必要なブランチブロックを処理します。必要なすべての列が索引にあり、order by句が索引構造と一致する場合や、ソート・マージ結合が行われる場合に使用されます。 INDEX [STORAGE] FAST FULL SCAN 必要なすべての列が索引内にある場合に、全表スキャンを置き換えるために索引内のすべてのブロックをスキャンします。マルチブロックIOを使用して並列化できます。 BITMAP INDEX RANGE SCAN, BITMAP CONVERSION TO ROWIDなどキー値にビットマップを使用し、各ビット位置をROWIDに変換するマッピング関数を使用します。WHERE句の複数の条件に対応する索引を効率的にマージ(AND, OR)できます。実行計画の要素（OPERATION） Copyright © 2021, Oracle and/or its affiliates. 17

結合方法と結合タイプ結合方法説明ネステッド・ループ結合 NESTED LOOPS 外側のテーブルのすべての行について、内側のテーブルのすべての行にアクセスします。データの小さなサブセットを結合するときに便利で、2番目のテーブルにアクセスする効率的な方法が索引検索です。ハッシュ結合
HASH JOIN 2つのテーブルのうち、小さい方のテーブルをスキャンし、得られた行を使ってメモリ上の結合キーに対応するハッシュテーブルを構築します。大きい方のテーブルをスキャンし、得られた行の結合カラムをハッシュ化し、その値を使ってハッシュテーブルを検索し、一致する行を見つけます。より大きなテーブルや、等価述語に有効です。ソート・マージ結合 2つのステップで構成されています (2つのテーブル間の結合条件が非等価の場合に有効) : 1. SORT JOIN operation: 両方の入力がジョインキーでソートされます。 2. MERGE JOIN operation: ソートされたリストはマージされます。実行計画の要素（OPERATION） Copyright © 2021, Oracle and/or its affiliates. 18 結合タイプ説明デカルト結合/直積結合 MERGE JOIN CARTESIAN あるデータソースのすべての行を、他のデータソースのすべての行と結合し、2つのセットの直積を作成します。テーブルが非常に小さい場合にのみ有効です。クエリに結合条件が指定されていない場合にのみ選択されます。外部結合 HASH JOIN OUTERなど結合条件を満たすすべての行を返します。また、(+)が付いていないテーブル（核のテーブル）の結合条件を満たさない行もすべて返します。

その他集計説明 HASH UNIQUE データの重複値を排除するためにハッシュ・アルゴリズム（同じデータを求める）を使用して行う（DISTINCTなど） HASH GROUP BY
GROUP BY集計のためにハッシュ・アルゴリズムを使用して行う SORT UNIQUE データの重複値を排除するためにソートを使用して行う（DISTINCTの後にORDER BYを行うなど） SORT GROUP BY GROUP BY集計のためにソートを使用して行う（GROUP BYの後にORDER BYを行うなど） SORT AGGREGATE COUNT、MIN、MAX、SUM、AVGなどの集計ファンクションの処理に対して行う（SELECT COUNT(*) FROM <表名> などで使用する） SORT ORDER BY ソートによってORDER BY処理を行う INLIST ITERATOR IN(同一列に対するOR)で指定された条件を満たす行を個別に検索して結果をまとめる操作 PX xxxx パラレル実行のデータ分散（PX SEND HASH, PX RECEIVEなど） PARTITION xxxx パーティション・プルーニング（PARTITION RANGE SINGLEなど）実行計画の要素（OPERATION） Copyright © 2021, Oracle and/or its affiliates. 19

----------------------------------------------------------------------------------- | Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | 1 | 211 | 12 (9)| | 3 | NESTED LOOPS | | 1 | 185 | 11 (10)| |* 4 | HASH JOIN | | 1 | 155 | 10 (10)| | 5 | MERGE JOIN CARTESIAN | | 107 | 8774 | 6 (0)| |* 6 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 30 | 3 (0)| | 7 | BUFFER SORT | | 107 | 5564 | 3 (0)| | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5564 | 3 (0)| | 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7811 | 3 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 1 (0)| |* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| | 13 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 26 | 1 (0)| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."MANAGER_ID"="E"."EMPLOYEE_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."SALARY"+("E"."SALARY"+"E"."COMMISSION_PCT")>="E"."SALARY"+("E". "SALARY"+"E"."COMMISSION_PCT")) 6 - filter("D"."DEPARTMENT_NAME"='Sales') 10 - filter("D"."DEPARTMENT_NAME"='Sales') 11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("E"."JOB_ID"="J"."JOB_ID") ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | 1 | 211 | 12 (9)| | 3 | NESTED LOOPS | | 1 | 185 | 11 (10)| |* 4 | HASH JOIN | | 1 | 155 | 10 (10)| | 5 | MERGE JOIN CARTESIAN | | 107 | 8774 | 6 (0)| |* 6 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 30 | 3 (0)| | 7 | BUFFER SORT | | 107 | 5564 | 3 (0)| | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5564 | 3 (0)| | 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7811 | 3 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 1 (0)| |* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| | 13 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 26 | 1 (0)| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."MANAGER_ID"="E"."EMPLOYEE_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."SALARY"+("E"."SALARY"+"E"."COMMISSION_PCT")>="E"."SALARY"+("E". "SALARY"+"E"."COMMISSION_PCT")) 6 - filter("D"."DEPARTMENT_NAME"='Sales') 10 - filter("D"."DEPARTMENT_NAME"='Sales') 11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("E"."JOB_ID"="J"."JOB_ID") 実行計画の要素（Rows） Copyright © 2021, Oracle and/or its affiliates. 20 テーブルに属するWHERE句の述語を適用したSELECT COUNT(*)を使用して、各テーブルからの正しいカーディナリティを決定します Rowsとは、述語が適用された後の各Operationに対する見積り行数 (カーディナリティ) です

見積り行数の確認 SELECT /*+ gather_plan_statistics */ p.prod_name, SUM(s.quantity_sold) FROM sales s,
products p WHERE s.prod_id =p.prod_id GROUP BY p.prod_name ; SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')); 実行計画の要素（Rows）見積り行数（E-Rows）と実際に返される行（A-Rows）を比較する Copyright © 2021, Oracle and/or its affiliates. 21 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 71 |00:00:00.57 | 1638 | | | | | 1 | HASH GROUP BY | | 1 | 71 | 71 |00:00:00.57 | 1638 | 799K| 799K| 3079K (0)| |* 2 | HASH JOIN | | 1 | 918K| 918K|00:00:00.85 | 1638 | 933K| 933K| 1279K (0)| | 3 | TABLE ACCESS STORAGE FULL | PRODUCTS | 1 | 72 | 72 |00:00:00.01 | 3 | | | | | 4 | PARTITION RANGE ALL | | 1 | 918K| 918K|00:00:00.37 | 1635 | | | | | 5 | TABLE ACCESS STORAGE FULL| SALES | 28 | 918K| 918K|00:00:00.20 | 1635 | | | | ------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts |
E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 71 |00:00:02.47 | 51 | | | | | 1 | PX COORDINATOR | | 1 | | 71 |00:00:02.47 | 51 | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 71 | 0 |00:00:00.01 | 0 | | | | | 3 | HASH GROUP BY | | 0 | 71 | 0 |00:00:00.01 | 0 | 858K| 858K| | | 4 | PX RECEIVE | | 0 | 71 | 0 |00:00:00.01 | 0 | | | | | 5 | PX SEND HASH | :TQ10001 | 0 | 71 | 0 |00:00:00.01 | 0 | | | | | 6 | HASH GROUP BY | | 0 | 71 | 0 |00:00:00.01 | 0 | 833K| 833K| 460K (0)| |* 7 | HASH JOIN | | 0 | 918K| 0 |00:00:00.01 | 0 | 1089K| 1089K| 1253K (0)| | 8 | PX RECEIVE | | 0 | 72 | 0 |00:00:00.01 | 0 | | | | | 9 | PX SEND BROADCAST | :TQ10000 | 0 | 72 | 0 |00:00:00.01 | 0 | | | | | 10 | PX BLOCK ITERATOR | | 0 | 72 | 0 |00:00:00.01 | 0 | | | | |* 11 | TABLE ACCESS STORAGE FULL| PRODUCTS | 0 | 72 | 0 |00:00:00.01 | 0 | | | | | 12 | PX BLOCK ITERATOR | | 0 | 918K| 0 |00:00:00.01 | 0 | | | | |* 13 | TABLE ACCESS STORAGE FULL | SALES | 0 | 918K| 0 |00:00:00.01 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------- SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')); パラレル実行の見積り行数の確認実行計画の要素（Rows）注: 最後に実行されたカーソル(QC)のみを表示しているため、A-Rows列の多くのデータがゼロになっています（全てのPX サーバーのカーソル情報を見るには、 ALLSTATS ALLを使用する必要があります） ※ 同じSQLが実行されているとそれもA-Rowsに加算される Copyright © 2021, Oracle and/or its affiliates. 22

実際に返された行と見積り行数を比較する最も簡単な方法 SQL監視を使用した見積り行数の確認実行計画の要素（Rows） Copyright © 2021, Oracle and/or its affiliates.
23

実行計画の要素（Bytes） Copyright © 2021, Oracle and/or its affiliates. 24 -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | 1 | 211 | 12 (9)| | 3 | NESTED LOOPS | | 1 | 185 | 11 (10)| |* 4 | HASH JOIN | | 1 | 155 | 10 (10)| | 5 | MERGE JOIN CARTESIAN | | 107 | 8774 | 6 (0)| |* 6 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 30 | 3 (0)| | 7 | BUFFER SORT | | 107 | 5564 | 3 (0)| | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5564 | 3 (0)| | 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7811 | 3 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 1 (0)| |* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| | 13 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 26 | 1 (0)| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."MANAGER_ID"="E"."EMPLOYEE_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."SALARY"+("E"."SALARY"+"E"."COMMISSION_PCT")>="E"."SALARY"+("E". "SALARY"+"E"."COMMISSION_PCT")) 6 - filter("D"."DEPARTMENT_NAME"='Sales') 10 - filter("D"."DEPARTMENT_NAME"='Sales') 11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("E"."JOB_ID"="J"."JOB_ID") 各Operationに対するバイト単位の見積りデータ・サイズ

実行計画の要素（Cost） Copyright © 2021, Oracle and/or its affiliates. 25 -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | 1 | NESTED LOOPS | | | | | | 2 | NESTED LOOPS | | 1 | 211 | 12 (9)| | 3 | NESTED LOOPS | | 1 | 185 | 11 (10)| |* 4 | HASH JOIN | | 1 | 155 | 10 (10)| | 5 | MERGE JOIN CARTESIAN | | 107 | 8774 | 6 (0)| |* 6 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 30 | 3 (0)| | 7 | BUFFER SORT | | 107 | 5564 | 3 (0)| | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5564 | 3 (0)| | 9 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7811 | 3 (0)| |* 10 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 1 (0)| |* 11 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| | 13 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 26 | 1 (0)| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."MANAGER_ID"="E"."EMPLOYEE_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") filter("E"."SALARY"+("E"."SALARY"+"E"."COMMISSION_PCT")>="E"."SALARY"+("E". "SALARY"+"E"."COMMISSION_PCT")) 6 - filter("D"."DEPARTMENT_NAME"='Sales') 10 - filter("D"."DEPARTMENT_NAME"='Sales') 11 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 12 - access("E"."JOB_ID"="J"."JOB_ID") Costは、計画で各Operationを実行するために使用される作業やリソース（CPUとIO）の単位を表します Costは、Oracleの内部数値であり、比較目的のみに使用する必要があります

練習問題（アクセス方法1）この問合せはどのような実行計画になると思いますか？ • 表productsには10,000行が含まれproduct_idに主キーがあります SELECT product_id, name FROM products WHERE
products_id IN (’P123’,’P456’,’P789’); 実行計画の要素 PRODUCTS PRODUCT_PK Copyright © 2021, Oracle and/or its affiliates. 26

練習問題（アクセス方法2）この問合せはどのような実行計画になると思いますか？ • 表productsには10,000行が含まれproduct_idに主キーがあります SELECT product_id, name FROM products WHERE
product_id BETWEEN 'AU' AND 'IE'; 実行計画の要素 PRODUCTS PRODUCT_PK Copyright © 2021, Oracle and/or its affiliates. 27

この問合せはどのような結合方法になると思いますか？ SELECT e.last_name, e.salary, d.department_name FROM hr.employees e, hr.departments d
WHERE d.departments_name IN ('Marketing‘,'Sales’) AND e.department_id = d.department_id; • Employeesには107行あります • Departmentsには27行あります • department_idはEmployeesとDepartments間の外部キー関係練習問題（結合方法）実行計画の要素 Copyright © 2021, Oracle and/or its affiliates. 28 （つまり索引がある）

注目する項目（性能劣化の原因）アクセス方法 • 索引スキャン、フル・スキャン、ビュー・アクセス結合方法 • ネステッド・ループ結合、ハッシュ結合、ソート・マージ結合結合タイプ
• 直積、外部結合、セミ結合、アンチ結合結合順序 • 少ないカーディナリティから結合する • SQLによって決まる場合もパーティション • パーティション・プルーニングパラレル実行 • データ分散のステップが増えるカーディナリティ • 述語を適用した行数 (Rows, E-Rows, A-Rows) 実行計画の解説 30 Copyright © 2021, Oracle and/or its affiliates. これで最適かを判断する

リーフ・ステップ(インデントの一番深いステップ)から実行して、結合（同一インデント）は上位に表示されたものが最初になる実行計画の解説 31 SQL> SELECT … FROM tab1,tab2,tab3 WHERE
tab1.c2=tab2.c2 AND tab1.c3=tab3.c3 AND tab1.c1<100 2 GROUP BY … ORDER BY … ; 実行計画 ---------------------------------------------------------------- | Id | Operation | Name | Rows | … | Cost (%CPU)| ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 271 (1)| | 1 | SORT GROUP BY | | | | | |* 2 | HASH JOIN | | | | | |* 3 | HASH JOIN | | | | | |* 4 | TABLE ACCESS FULL | TAB1 | 10 | | | | 5 | TABLE ACCESS FULL | TAB2 | 50 | | | | 6 | TABLE ACCESS FULL | TAB3 | 100 | | | (1) (2) ① (3) ② ③ | TABLE ACCESS BY INDEX ROWID| TAB1 | | INDEX RANGE SCAN | IX_TAB1 | 索引アクセス | VIEW | | | HASH GROUP BY | | | TABLE ACCESS FULL | TAB1 | ビュー・アクセスカーディナリティ結合 CPUコスト・モデル（CPUコストの割合） Copyright © 2021, Oracle and/or its affiliates.

後半には、述語の情報とNote部が出力される実行計画の解説 32 実行計画 -------------------------------------- | Id | Operation |
Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT GROUP BY | | |* 2 | HASH JOIN | | |* 3 | HASH JOIN | | |* 4 | TABLE ACCESS FULL | TAB1 | | 5 | TABLE ACCESS FULL | TAB2 | … Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TAB1"."C2"="TAB2"."C2") 3 – access("TAB1"."C3"="TAB3"."C3") 4 – filter("TAB1"."C1"<100) Note ----- - dynamic sampling used for this statement (level=2) 述語の情報（どのステップで述語が使用されているか） Note部（内部的に動作したものなど） Copyright © 2021, Oracle and/or its affiliates.

アクセス方法索引スキャン（シングル・ブロック・リード） • Index Range Scan（索引範囲スキャン） • Index Unique Scan（索引一意スキャン）
• Index Skip Scan（索引スキップ・スキャン） • 後方一致（中間一致）条件フル・スキャン • Table Full Scan（全表スキャン） • テーブルをマルチ・ブロック・リード • Index Full Scan（全索引スキャン） • シングル・ブロック・リード • Index Fast Full Scan（高速全索引スキャン） • リーフ・ノードをマルチ・ブロック・リード実行計画の解説 33 < 5 0 < 2 0 3 0 < 6 0 7 0 1 0 1 1 1 2 3 0 3 1 5 0 5 1 … 範囲スキャン/ 全索引スキャン … Copyright © 2021, Oracle and/or its affiliates. ルートブランチリーフ

アクセス方法（Index Range Scan / Index Unique Scan） Index Unique Scan
• 一意索引の等価条件 Index Range Scan • 非一意索引の等価条件 • 一意索引／非一意索引の範囲条件 • 12cからROWIDで表にBatchアクセス (第34回) 実行計画の解説 34 SQL> SELECT * FROM tab1 WHERE c1 < 10; 実行計画 ----------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | |* 2 | INDEX RANGE SCAN | IX_TAB1 | SQL> SELECT * FROM tab1 WHERE c1 = 10; 実行計画 ----------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | |* 2 | INDEX UNIQUE SCAN | IX_TAB1 | Copyright © 2021, Oracle and/or its affiliates. SQL> SELECT * FROM tab1 WHERE c1 < 10; 実行計画 ------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | |* 2 | INDEX RANGE SCAN | IX_TAB1 |

アクセス方法（Index Skip Scan）後方一致（中間一致）条件での索引スキャン（Oracle9iから）<=第9回 • 全表スキャンより効果的なとき（c1の個別値が少ないときなど）実行計画の解説 35 SQL> CREATE
INDEX ix2_tab1 ON tab1 (c1,c2); … SQL> SELECT * FROM tab1 WHERE c2 = 10; 実行計画 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | |* 2 | INDEX SKIP SCAN | IX2_TAB1 | c1 c2 1 ･･･ 1 1 1 ･･･ 10 2 2 ･･･ 2 2 1 ･･･ 10 2 3 ･･･ 3 1 ･･･ 10 2 3 ･･････テーブル索引 c1の値ごとにc2=10を行う c1=1 AND c2=10 c1=2 AND c2=10 c1=3 AND c2=10 ・・・ Copyright © 2021, Oracle and/or its affiliates.

アクセス方法（Index Full Scan）索引範囲スキャンはできないが（索引に対する条件はないが）ソート処理が必要なときなど実行計画の解説 36 SQL> CREATE INDEX
ix2_tab1 ON tab1 (c1,c2); … SQL> SELECT * FROM tab1 WHERE c2 > 10 ORDER BY c1; 実行計画 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | |* 2 | INDEX FULL SCAN | IX2_TAB1 | Copyright © 2021, Oracle and/or its affiliates.

アクセス方法（Index Fast Full Scan / Table Full Scan）テーブルをアクセスする必要がないとき •
テーブルより索引の方がサイズが小さい実行計画の解説 37 SQL> CREATE INDEX ix2_tab1 ON tab1 (c1,c2); … SQL> SELECT c1,c2 FROM tab1 WHERE c1 > 10; 実行計画 ----------------------------------------- | Id | Operation | Name | ----------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | INDEX FAST FULL SCAN| IX2_TAB1 | SQL> SELECT * FROM tab1 WHERE c1 > 10; 実行計画（全表スキャン） -------------------------------- | Id | Operation | Name | -------------------------------- | 0 | SELECT STATEMENT| | |* 1 | TABLE FULL SCAN| TAB1 | Copyright © 2021, Oracle and/or its affiliates.

アクセス方法（ビュー・アクセス）ビュー／インライン・ビューをマージしないとき（”VIEW”が出力される） • 内部的に作成されたときでも出力される実行計画の解説 38 SQL> SELECT … FROM
tab2,(SELECT c1,sum(c2) FROM tab1 GROUP BY c1) A WHERE A.c1 = tab2.c1 ; 実行計画 ----------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH JOIN | | xxx | | 2 | VIEW | | 50 | | 3 | HASH GROUP BY | | 50 | | 4 | TABLE ACCESS FULL| TAB1 | 100K| | 5 | TABLE ACCESS FULL | TAB2 | 100 | SQL> SELECT … FROM tab1,tab2 2 WHERE tab1.c1 = tab2.c1 GROUP BY c1 ; 実行計画（ビュー・マージすると） --------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | HASH JOIN | | xxx | | 3 | TABLE ACCESS FULL| TAB2 | 100 | | 4 | TABLE ACCESS FULL| TAB1 | 100K| Copyright © 2021, Oracle and/or its affiliates.

結合方法 Nested Loop Join（ネステッド・ループ結合） • 索引を使用する結合のためアクセス行数が多いと性能が悪くなる • 索引アクセスのI/Oを改善 • Multi
Join Key Pre-fetching（Oracle9iから） • Nested Loops Join Batching（Oracle Database 11gから） Hash Join（ハッシュ結合） • 索引を使用しない等価結合 Sort Merge Join（ソート・マージ結合） • 索引を使用しない等価結合以外など実行計画の解説 39 Copyright © 2021, Oracle and/or its affiliates.

ネステッド・ループ結合 • 外部表の各行に対して、内部表のすべての行にアクセスして結合する (データの小さなサブセットを結合する場合に有効）ハッシュ結合 • 小さい表をスキャンしてメモリ上に結
合キーでハッシュテーブルを構築後に、大きい表をスキャンしてハッシュ関数で結合する (等価結合) ソート・マージ結合 • 両方の表が結合キーでソートされ、ソートされたリストをマージする (非等価結合に有効) Copyright © 2020, Oracle and/or its affiliates. 40 結合キー C_2 C_1 0 3 1 1 4 4 8 5 7 7 9 3 2 8 C_1 C_2 1 5 4 2 3 0 9 3 2 8 6 0 7 7 3 3 2 1 9 5 tab1(外部表) tab2(内部表) ･･･索引が付いている方がより高速 0 3 結合キー C_2 C_1 1 1 0 3 9 3 4 4 8 5 7 7 2 8 C_1 C_2 1 5 2 8 2 1 3 0 3 3 4 2 6 0 7 7 9 3 9 5 tab1(外部表) tab2(内部表) 1 1 1 5 C_1 C_2 1 5 2 8 2 1 3 0 3 3 4 2 6 0 7 7 9 3 9 5 tab1(外部表) tab2(内部表) 1 1 1 5 ハッシュ関数結合方法実行計画の解説

結合方法（ネステッド・ループ結合）基本は内部表の索引を使用して結合する • 駆動表は絞り込み条件があるときに索引スキャン実行計画の解説 41 SQL> SELECT … FROM
tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ; 実行計画 --------------------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | NESTED LOOPS | | xxx | | 3 | TABLE ACCESS FULL | TAB2 | 100 | <- 駆動表 | 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | <- 内部表 |* 5 | INDEX RANGE SCAN | IX_TAB1 | xxx | Copyright © 2021, Oracle and/or its affiliates.

結合方法（ネステッド・ループ結合） Multi Join Key Pre-fetching（Oracle 9iから）<=第34回 • 索引レンジ・スキャンのデータ・ブロックの先読み実行計画の解説 42
SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ; 実行計画 -------------------------------------------------------- | Id | Operation | Name | Rows | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | 内部表をPer-fetchする（Id=5のROWIDから） | 3 | NESTED LOOPS | | xxx | | 4 | TABLE ACCESS FULL | TAB2 | 100 | 駆動表 |* 5 | INDEX RANGE SCAN | IX_TAB1 | xxx | 内部表（駆動表の1行に対し索引アクセス） Copyright © 2021, Oracle and/or its affiliates.

結合方法（ネステッド・ループ結合） Nested Loops Join Batching（Oracle Database 11gから）<=第34回 • 索引で結合後にROWIDを並べ替えてテーブルにアクセス（索引一意スキャンも可）実行計画の解説
43 SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ; 実行計画 --------------------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | NESTED LOOPS | | xxx | Nested Loops Join(2) | 3 | NESTED LOOPS | | xxx | Nested Loops Join(1) => 結果を駆動表(2) | 4 | TABLE ACCESS FULL | TAB2 | 100 | 駆動表(1) |* 5 | INDEX RANGE SCAN | IX_TAB1 | xxx | 内部表(1)（索引のみにアクセス） | 6 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | 内部表(2)（ここのI/Oを最適化） ROWIDを並べ替えて Copyright © 2021, Oracle and/or its affiliates.

結合方法（ネステッド・ループ結合）駆動表の行数だけ内部表のアクセスが実行されるので、内部表のStartsにアクセした回数（駆動表のA-Rows）、E-Rowsに1回の見積り行数が出力される（パラレル実行も同じ）<=第68回実行計画の解説 44 SQL> SELECT /*+ gather_plan_statistics */
* FROM tab01 t1,tab02 t2 WHERE t1.c1=t2.c1 AND t2.c2 < 1; ------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | | A-Rows | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 100 | | 1 | NESTED LOOPS | | 1 | 101 | | 100 | | 2 | NESTED LOOPS | | 1 | 101 | | 100 | |* 3 | TABLE ACCESS FULL | TAB02 | 1 | 101 | | 100 | |* 4 | INDEX UNIQUE SCAN | PK_TAB01 | 100 | 1 | | 100 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB01 | 100 | 1 | | 100 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - storage("T2"."C2"<1) filter("T2"."C2"<1) 4 - access("T1"."C1"="T2"."C1") E-Rows×Startsと A-Rowsを比較する Copyright © 2021, Oracle and/or its affiliates.

結合方法（ハッシュ結合）索引の代わりにメモリ上にハッシュ・テーブルを作成（最初にアクセスするテーブル） • 等価結合のみ（片方のテーブル/アクセスするデータが小さいと効果的） • 結合列以外で絞り込み条件があるときに索引スキャン • スター・スキーマはRight-deep Joinが効果的（SWAP_JOIN_INPUTSヒント）<=第46回実行計画の解説
45 SQL> SELECT … FROM tab1,tab2,tab3 2 WHERE tab1.c1=tab2.c1 AND tab1.c2=tab3.c2 AND tab2.c3=xxx AND tab3.c2=xxx GROUP BY … ; 実行計画（Left-deep Join） ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | |* 2 | HASH JOIN | | |* 3 | HASH JOIN | | |* 4 | TABLE ACCESS FULL| TAB2 | | 5 | TABLE ACCESS FULL| TAB1 | |* 6 | TABLE ACCESS FULL | TAB3 | Copyright © 2021, Oracle and/or its affiliates. 実行計画（Right-deep Join） ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | |* 2 | HASH JOIN | | |* 3 | TABLE ACCESS FULL | TAB3 | |* 4 | HASH JOIN | | |* 5 | TABLE ACCESS FULL| TAB2 | | 6 | TABLE ACCESS FULL| TAB1 | ハッシュ・テーブルを作成 Customer 2.25GB Supplier 168MB Lineorder 338GB Date_DiM 0.2MB Part 144MB

ハッシュ結合を順番に実施 (今までの実行計画) 複数のブルーム・フィルターを利用したファクト表検索実行計画の解説 46 ハッシュ・テーブル作成ハッシュ・テーブル
作成 Left-deep tree PART HASH JOIN LINEORDER HASH JOIN SUPPLIER HASH JOIN DATEDIM 1 2 3 4 1 2 3 Right-deep tree PART HASH JOIN LINEORDER HASH JOIN SUPPLIER HASH JOIN DATEDIM 2 3 1 2 3 ジョインフィルタ作成ジョインフィルタ作成 1 ジョインフィルタ作成 4 何故この機能が重要か? LINEORDERをマルチプルフィルタを利用して初期スキャをすることにより上位の実行計画で処理する行数を縮小する複数のブルームフィルターによるファクト表の高速検索ハッシュ・テーブル作成ハッシュ・テーブル作成ハッシュ・テーブル作成ハッシュ・テーブル作成 (FACT) ハッシュ結合（Left-deep Join vs. Right-deep Join）

結合方法（ソート・マージ結合）索引の代わりにソートして結合 • 等価結合以外など • 絞り込み条件があるときに索引スキャン実行計画の解説 47 SQL> SELECT
… FROM tab1,tab2 WHERE tab1.c1 > tab2.c1 GROUP BY … ; 実行計画 --------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | MERGE JOIN | | xxx | | 3 | SORT JOIN | | 100 | | 4 | TABLE ACCESS FULL| TAB2 | 100 | |* 5 | SORT JOIN | | 100K| | 6 | TABLE ACCESS FULL| TAB1 | 100K| Copyright © 2021, Oracle and/or its affiliates.

結合タイプ Cross Join（直積） • 結合条件がない（負荷が高いのでできるだけ使用しないように） Outer Join（外部結合） • OUTER JOIN
Semi Join（セミ結合） • EXISTS, IN Anti Join（アンチ結合） • NOT EXISTS, NOT IN このような特殊な結合タイプ (直積以外) は結合順が決まっている • Oracle Database 10gでハッシュ結合の結合順を改善 • HASH JOIN RIGHT xxxx 実行計画の解説 48 アンチ結合主問合せ副問合せセミ結合主問合せ副問合せ Copyright © 2021, Oracle and/or its affiliates.

結合タイプ（直積）できるだけ行わない • 結合条件がないので効率が悪い実行計画の解説 49 SQL> SELECT … FROM
tab1,tab2 GROUP BY … ; 実行計画 ---------------------------------------------- | Id | Operation | Name | Rows | ---------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | MERGE JOIN CARTESIAN| | | | 3 | TABLE ACCESS FULL | TAB2 | 100 | | 4 | BUFFER SORT | | 100K|←ソートする（メモリにバッファリングするため） | 5 | TABLE ACCESS FULL | TAB1 | 100K| Copyright © 2021, Oracle and/or its affiliates.

結合タイプ（外部結合）左外部結合、右外部結合 • Oracle Database 10gからハッシュ結合で核でないテーブルを先にアクセス可能に実行計画の解説 50 SQL> SELECT
… FROM tab1 LEFT OUTER JOIN tab2 USING (c1) GROUP BY … ; 実行計画 -------------------------------------------- | Id | Operation | Name | Rows | -------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | HASH JOIN OUTER | | | | 3 | TABLE ACCESS FULL| TAB1 | 100K| | 4 | TABLE ACCESS FULL| TAB2 | 100 | 実行計画（Oracle Database 10gから） ----------------------------------------------- | Id | Operation | Name | Rows | ----------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | HASH JOIN RIGHT OUTER| | | | 3 | TABLE ACCESS FULL | TAB2 | 100 | | 4 | TABLE ACCESS FULL | TAB1 | 100K| Copyright © 2021, Oracle and/or its affiliates.

結合タイプ（外部結合）完全外部結合 • Native Full Outer Join（Oracle Database 11gから）<=第34回 •
以前はFull Outer Joinを二つのブランチのUNION ALLで（Left Outer JoinとNOT EXISTS）実行計画の解説 51 SQL> SELECT * FROM tab1 A FULL OUTER JOIN tab2 B USING (c1) ; 実行計画 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | VIEW | VW_FOJ_0 | |* 2 | HASH JOIN FULL OUTER| | | 3 | TABLE ACCESS FULL | TAB1 | | 4 | TABLE ACCESS FULL | TAB2 | ＜以前は以下のSQLを実行＞ SQL> SELECT * 2 FROM tab1 LEFT OUTER JOIN tab2 USING (c1) 3 UINON ALL 4 SELECT * FROM tab2 B WHERE NOT EXISTS 5 (SELECT 0 FROM tab1 A WHERE A.c1 = B.c1) ; Copyright © 2021, Oracle and/or its affiliates.

結合タイプ（セミ結合） EXISTS、IN条件の副問合せ • Oracle Database 10gからセミ・ハッシュ結合で副問合せのテーブルを先にアクセス可能に実行計画の解説 52 SQL> SELECT
… FROM tab1 WHERE EXISTS (SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ; 実行計画 -------------------------------------------- | Id | Operation | Name | Rows | -------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH JOIN SEMI | | | | 2 | TABLE ACCESS FULL| TAB1 | 100K| | 3 | TABLE ACCESS FULL| TAB2 | 100 | 実行計画（Oracle Database 10gから） ---------------------------------------------- | Id | Operation | Name | Rows | ---------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH JOIN RIGHT SEMI| | | | 2 | TABLE ACCESS FULL | TAB2 | 100 | | 3 | TABLE ACCESS FULL | TAB1 | 100K| Copyright © 2021, Oracle and/or its affiliates. SELECT … FROM tab1, (SELECT DISTINCT c1 FROM tab2) t2 WHERE tab1.c1 = t2.c1 ;

結合タイプ（アンチ結合） NOT EXISTS、NOT IN条件の副問合せ • Oracle Database 10gからアンチ・ハッシュ結合で副問合せのテーブルを先にアクセス可能に実行計画の解説 53
SQL> SELECT … FROM tab1 WHERE NOT EXISTS (SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ; 実行計画 -------------------------------------------- | Id | Operation | Name | Rows | -------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH JOIN ANTI | | | | 2 | TABLE ACCESS FULL| TAB1 | 100K| | 3 | TABLE ACCESS FULL| TAB2 | 100 | 実行計画（Oracle Database 10gから） ---------------------------------------------- | Id | Operation | Name | Rows | ---------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | HASH JOIN RIGHT ANTI| | | | 2 | TABLE ACCESS FULL | TAB2 | 100 | | 3 | TABLE ACCESS FULL | TAB1 | 100K| Copyright © 2021, Oracle and/or its affiliates.

パーティションパーティション・プルーニング • 静的プルーニングと動的プルーニング • 静的プルーニングが優先される（プラン作成時に決定） • 動的プルーニング（実行時に決定）：バインド変数、データ型の暗黙変換など • AND
Pruning（静的＋動的）（11gR2から） • パーティションの開始と終了の項目が増える • Pstart：アクセス開始のパーティション（静的：数字、動的：KEY/KEY(xx)、AND Pruning：KEY(AP) ） • Pstop：アクセス終了のパーティション（静的：数字、動的：KEY/KEY(xx)、AND Pruning：KEY(AP) ）ジェイン・フィルター（ブルーム・フィルタリング） • 結合列のパーティション・プルーニング • パーティション化された明細表をマスタ表の条件で検索するなど • このSQLのときなどに’明細表.c1’でパーティション・プルーニングを行う • PstartとPstopはフィルター名になる実行計画の解説 54 SQL> SELECT … FROM 明細表 A, マスタ1 B WHERE A.c1 = B.c1 AND B.c2 = 'xxx'; Copyright © 2021, Oracle and/or its affiliates.

パーティション（パーティション・プルーニング）実行計画の解説 55 SQL> SELECT * FROM tab01 WHERE sdate
= TO_DATE('2012/08/01','YYYY/MM/DD'); 実行計画（静的パーティション・プルーニング） -----------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -----------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | | 1 | PARTITION RANGE SINGLE | | | 8 | 8 | |* 2 | TABLE ACCESS FULL | TAB01 | | 8 | 8 | 実行計画（動的パーティション・プルーニング） -----------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -----------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | | 1 | PARTITION RANGE SINGLE | | | KEY | KEY | |* 2 | TABLE ACCESS FULL | TAB01 | | KEY | KEY | • バインド変数 • データ型の暗黙変換 • など Copyright © 2021, Oracle and/or its affiliates. PARTITION RANGE ITERATOR PARTITION RANGE ALL パーティション・プルーニング

パーティション（ジョイン・フィルター／ブルーム・フィルタリング）結合時に効果的にフィルタリングする（結合列でフィルタリングする） • ymでフィルターされたtime_cd (結合列) のビットマップを作成する実行計画の解説 56 SQL> SELECT
* FROM sales_h S, time T 2 WHERE S.time_cd = T.time_cd AND T.ym = TO_DATE('201201','YYYYMM') ; 実行計画 -------------------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -------------------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | |* 1 | HASH JOIN | | | | | | 2 | PART JOIN FILTER CREATE | :BF0000 | | | | | 3 | PARTITION RANGE SINGLE | | | 1 | 1 | |* 4 | TABLE ACCESS FULL | time | | 1 | 1 | | 5 | PARTITION RANGE JOIN-FILTER | | |:BF0000|:BF0000| | 6 | TABLE ACCESS FULL | sales_h | |:BF0000|:BF0000| time_cdでフィルターを作成 Copyright © 2021, Oracle and/or its affiliates.

パーティション（パーティション・プルーニング）実行計画の解説 57 Copyright © 2021, Oracle and/or its affiliates.
プルーニング Operation Pstart/Pstop 備考プルーニングなし PARTITION xxxx ALL 1/最大番号静的 PARTITION xxxx SINGLE (1つのみ) PARTITION xxxx ITERATOR (2つ以上) 数字これが効果的なときデータ型の暗黙変換 KEY できるだけ発生させない (第22回) バインド変数 KEY ネステッド・ループ結合 PARTITION xxxx ITERATOR KEY 内部表に対して (第46回) INリスト条件 PARTITION xxxx INLIST KEY(I) 第46回 OR条件 PARTITION xxxx OR KEY(OR) 第46回複数列パーティション PARTITION xxxx MULTI-COLUMN KEY(MC) 先頭の列が条件にない (第22回) 副問合せ PARTITION xxxx SUBQUERY KEY(SQ) 第22回ジョイン・フィルター PARTITION xxxx JOIN-FILTER フィルター名ハッシュ結合のとき (第22回) AND Pruning PARTITION xxxx AND KEY(AP) 静的＋動的（11gR2から）(第46回)

パラレル実行（データ分散）スキャンのデータ分散（第20回、第39回） • ブロック単位（PX BLOCK ITERATOR） • パーティション単位（PX PARTITION RANGE
ALLなど） • データ分散しない（片方の読込みデータが少ないとき） • それぞれのPQプロセスで全てのデータスキャン以外でのデータ再分散（第20回、第39回） • PX SEND HASH / RANGE / BROADCAST / PARTITION • 基本はHASH（重複データが多いときに注意） • PX RECEIVE 実行計画の解説 58 PQ スキャン表 PQ PQ PQ 結合 PQ PQ PQ PQ QC PQ PQ PQ PQ OrderBy スキャン以外でのデータ再分散スキャンのデータ分散 Copyright © 2021, Oracle and/or its affiliates.

パラレル実行（スキャンのデータ分散） Block-based granules • ブロック単位で分割してアクセス • PX BLOCK ITERATOR Partition-based
granules • パーティション単位に分割してアクセス • パーティション・ワイズ結合など • PX PARTITION RANGE ALL • PX PARTITION HASH ALL • など実行計画の解説 59 PQ スキャン PQ PQ PQ QC P1 表 P2 P3 P4 PQ スキャン PQ PQ PQ QC 表 Copyright © 2021, Oracle and/or its affiliates.

パラレル実行（スキャン以外でのデータ再分散）操作内パラレル化のデータ分散（偏りが発生すると効果が低下する） • HASH（基本はこれを使用する） • 重複データが多いときに注意 • RANGE（ソートなど） • 偏りやすい
• BROADCAST（結合の片方が小さい） • 同じデータをすべてのプロセスに • PARTITION（パーティション・ワイズ結合） • パーティション分割（片方がパーティション表のとき） • NONE（データ分散しない／データ通信しない） • PX SEND xxxxとPX RECEIVEがない • PARTITIONやBROADCASTなどと使用 PQ_DISTRIBUTEヒントで調整実行計画の解説 60 PQ スキャン表操作間パラレル化操作内パラレル化 PQ PQ PQ 結合 PQ PQ PQ PQ QC PQ PQ PQ PQ OrderBy ① ② ③ PX SEND xxxx PX RECEIVE Copyright © 2021, Oracle and/or its affiliates.

パラレル実行実行計画の解説 61 SQL> SELECT * FROM tab01,tab02 WHERE tab01.c1
= tab02.c1 ORDER BY c0; 実行計画 ------------------------------------------------<途中省略>------------------------------- | Id | Operation | Name | | TQ |IN-OUT| PQ Distrib | ------------------------------------------------<途中省略>------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10003 | | Q1,03 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | | Q1,03 | PCWP | | | 4 | PX RECEIVE | | | Q1,03 | PCWP | | | 5 | PX SEND RANGE | :TQ10002 | | Q1,02 | P->P | RANGE | | 6 | HASH JOIN BUFFERED | | | Q1,02 | PCWP | | | 7 | PX RECEIVE | | | Q1,02 | PCWP | | | 8 | PX SEND HASH | :TQ10000 | | Q1,00 | P->P | HASH | | 9 | PX BLOCK ITERATOR | | | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| TAB01 | | Q1,00 | PCWP | | | 11 | PX RECEIVE | | | Q1,02 | PCWP | | | 12 | PX SEND HASH | :TQ10001 | | Q1,01 | P->P | HASH | | 13 | PX BLOCK ITERATOR | | | Q1,01 | PCWC | | | 14 | TABLE ACCESS FULL| TAB02 | | Q1,01 | PCWP | | 結合プロセス（データをチェックするので同じプロセス） PX PARTITION RANGE ALL スキャンのデータ分散 PX SEND PARTITION PX SEND BROADCAST データ再分散 Copyright © 2021, Oracle and/or its affiliates.

パラレル実行（BROADCAST分散とPARTITION分散）結合の片方をデータ分散しない（スキャンと結合が同じプロセス）実行計画の解説 62 スキャン＋結合 P2 P3 P4 PQ
スキャン PQ PQ PQ 結合 PQ PQ PQ PQ tab1 tab2 P1 PARTITION分割（パーシャル・パーティション・ワイズ結合）結合 tab1 BROADCAST分割スキャン PQ PQ PQ PQ tab2 PQ PQ PQ PQ スキャンと結合が同じプロセススキャン＋結合同じデータを全プロセスに tab2のパーティションに合わせるフル・パーティション・ワイズ結合は、どちらでもデータ分散しない（結合列が同じパーティションのとき） Copyright © 2021, Oracle and/or its affiliates.

パラレル実行（BROADCAST分散とPARTITION分散）実行計画の解説 63 SQL> SELECT COUNT(*) FROM (SELECT * FROM
t3,t1 WHERE t3.col1 = t1.col1); 実行計画 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | | TQ |IN-OUT| PQ Distrib | A-Rows | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | 1 | | 1 | SORT AGGREGATE | | 1 | 1 | | | | | 1 | | 2 | PX COORDINATOR | | 1 | | | | | | 2 | | 3 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 1 | | Q1,01 | P->S | QC (RAND) | 0 | | 4 | SORT AGGREGATE | | 2 | 1 | | Q1,01 | PCWP | | 2 | |* 5 | HASH JOIN | | 2 | 50000 | | Q1,01 | PCWP | | 100K| | 6 | PX RECEIVE | | 2 | 5 | | Q1,01 | PCWP | | 10 | | 7 | PX SEND BROADCAST | :TQ10000 | 0 | 5 | | Q1,00 | P->P | BROADCAST | 0 | | 8 | PX BLOCK ITERATOR | | 2 | 5 | | Q1,00 | PCWC | | 5 | |* 9 | TABLE ACCESS FULL| T1 | 1 | 5 | | Q1,00 | PCWP | | 5 | | 10 | PX BLOCK ITERATOR | | 2 | 100K| | Q1,01 | PCWC | | 100K| |* 11 | TABLE ACCESS FULL | T2 | 26 | 100K| | Q1,01 | PCWP | | 100K| スキャンだけ分散 (スキャンと結合のプロセスが同じ) 行数×パラレル度 PX SEND PARTITION データ再分散 PX PARTITION RANGE ALL スキャンのデータ分散

パラレル実行（データ分散なし: 12cから）片方の読込みデータが少ないときはBROADCASTも行わない • それぞれのプロセスで全てのデータをスキャンする（スキャンのデータ分散もしない）<=第56回実行計画の解説 64 SQL> SELECT COUNT(*)
FROM (SELECT * FROM t2,t1 WHERE t2.col1 = t1.col1); 実行計画 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | | TQ |IN-OUT| PQ Distrib | A-Rows | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | | | | 1 | | 1 | SORT AGGREGATE | | 1 | 1 | | | | | 1 | | 2 | PX COORDINATOR | | 1 | | | | | | 4 | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | | Q1,00 | P->S | QC (RAND) | 0 | | 4 | SORT AGGREGATE | | 4 | 1 | | Q1,00 | PCWP | | 4 | |* 5 | HASH JOIN | | 4 | 50000 | | Q1,00 | PCWP | | 100K| | 6 | TABLE ACCESS FULL | T2 | 4 | 5 | | Q1,00 | PCWP | | 20 | | 7 | PX BLOCK ITERATOR | | 3 | 100K| | Q1,00 | PCWC | | 100K| |* 8 | TABLE ACCESS FULL| T1 | 52 | 100K| | Q1,00 | PCWP | | 100K| パラレル度が4なので、4つのPQプロセスで5行リードしている（A-Rowsは20行になっている）すべて同一プロセス Copyright © 2021, Oracle and/or its affiliates. Smart Table Replicate 表T2 結合 +スキャン PX PX QC 表T1

※ '(SKEW)'の出力は、結合列にヒストグラムが存在するかPQ_SKEWヒントを使用 SQL> SELECT COUNT(*) FROM (SELECT * FROM t3,t1
WHERE t3.col1 = t1.col1); 実行計画 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | | TQ |IN-OUT| PQ Distrib | A-Rows | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | | | 1 | | 1 | SORT AGGREGATE | | 1 | 1 | | | | | 1 | | 2 | PX COORDINATOR | | 1 | | | | | | 2 | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 1 | | Q1,02 | P->S | QC (RAND) | 0 | | 4 | SORT AGGREGATE | | 2 | 1 | | Q1,02 | PCWP | | 2 | |* 5 | HASH JOIN | | 2 | 50000 | | Q1,02 | PCWP | | 100K| | 6 | PX RECEIVE | | 2 | 5 | | Q1,02 | PCWP | | 5 | | 7 | PX SEND HYBRID HASH | :TQ10000 | 0 | 5 | | Q1,00 | P->P | HYBRID HASH| 0 | | 8 | STATISTICS COLLECTOR | | 2 | | | Q1,00 | PCWC | | 5 | | 9 | PX BLOCK ITERATOR | | 2 | 5 | | Q1,00 | PCWC | | 5 | |* 10 | TABLE ACCESS FULL | T1 | 1 | 5 | | Q1,00 | PCWP | | 5 | | 11 | PX RECEIVE | | 2 | 100K| | Q1,02 | PCWP | | 100K| | 12 | PX SEND HYBRID HASH (SKEW)| :TQ10001 | 0 | 100K| | Q1,01 | P->P | HYBRID HASH| 0 | | 13 | PX BLOCK ITERATOR | | 2 | 100K| | Q1,01 | PCWC | | 100K| |* 14 | TABLE ACCESS FULL | T2 | 26 | 100K| | Q1,01 | PCWP | | 100K| パラレル実行（HYBRID HASH : 12cから）<=第39回実行計画の解説 65 重複データを分散したときに'(SKEW)’が出力される実行時に行数を求めてパラレル度によってBROADCAST / HASHを決定 BROADCASTだと行数×パラレル度 Copyright © 2021, Oracle and/or its affiliates.

パラレル実行（ジョイン・フィルター／ブルーム・フィルタリング）結合プロセスに渡すデータを結合列でフィルタリングする（ExadataとDBIMはシリアルでも実行）実行計画の解説 66 SQL> SELECT * FROM tab2,tab3 WHERE
tab2.c2 = tab3.c2 AND tab2.c1 < 200 ; 実行計画 ---------------------------------------------------------------------------- | Id | Operation | Name | | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | | Q1,02 | PCWP | | | 4 | JOIN FILTER CREATE | :BF0000 | | Q1,02 | PCWP | | | 5 | PX RECEIVE | | | Q1,02 | PCWP | | | 6 | PX SEND HASH | :TQ10000 | | Q1,00 | P->P | HASH | | 7 | PX BLOCK ITERATOR | | | Q1,00 | PCWC | | |* 8 | TABLE ACCESS FULL| TAB2 | | Q1,00 | PCWP | | | 9 | PX RECEIVE | | | Q1,02 | PCWP | | | 10 | PX SEND HASH | :TQ10001 | | Q1,01 | P->P | HASH | | 11 | JOIN FILTER USE | :BF0000 | | Q1,01 | PCWP | | | 12 | PX BLOCK ITERATOR | | | Q1,01 | PCWC | | |* 13 | TABLE ACCESS FULL| TAB3 | | Q1,01 | PCWP | | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TAB2"."C2"="TAB3"."C2") 8 - filter("TAB2"."C1"<200) 13 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"TAB3"."C2")) Tab2.c2でフィルターを作成 Copyright © 2021, Oracle and/or its affiliates. TAB3のこのデータを削減 PX スキャン表TAB2 PX PX PX 結合 PX PX QC 表TAB3 Exadata (ストレージサーバーにオフロードできる) DBIM (フィルターでSIMD命令が使用できる)

パラレル実行（DML）パラレルDML（DELETE/UPDATE/INSERT/MERGE）はデフォルトが有効ではない • 有効にするには以下が必要（Autonomous Databaseはデフォルトで有効） • ALTER SESSION [ENABLE PARALLEL
DML | FORCE PARALLEL DML PARALLEL <パラレル度>] • ENABLE_PARALLEL_DMLヒント（12cから）実行計画の解説 67 Copyright © 2021, Oracle and/or its affiliates. SQL> INSERT /*+ parallel(4) */ INTO t02 2 SELECT /*+ parallel(4) */ * FROM t01; 実行計画（パラレルDMLが無効） ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | INSERT STATEMENT | | | 1 | LOAD AS SELECT | T02 | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | | 4 | PX BLOCK ITERATOR | | |* 5 | TABLE ACCESS FULL| T01 | SQL> INSERT /*+ enable_parallel_dml parallel(4) */ INTO t02 2 SELECT /*+ parallel(4) */ * FROM t01; 実行計画（パラレルDMLが有効） ------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------- | 0 | INSERT STATEMENT | | | 1 | PX COORDINATOR | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | | 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| T02 | | 4 | PX BLOCK ITERATOR | | |* 5 | TABLE ACCESS FULL | T01 |

パラレル実行（DML）パラレル・ダイレクト・パス・ロード/インサート）<=第50回 • Temp Segment Merge(TSM) Loading • 非パーティションに対するパラレル・ロード（TEMPセグメントを使用） •
High Water Mark(HWM) Loading • 各パーティションごとにシリアル実行するパラレル・ロード（Exadata HCCのデフォルト） • 一つのパーティションのときにはシリアル実行と同じ • High Water Mark Brokering(HWMB) Loading • パーティション表に対するパラレル・ロード（HVエンキューを使用） • Hybrid TSM/HWMB Loading • 12cからの非パーティション表に対するパラレル・ロード（TEMPセグメントとHVエンキューを使用） • RACのインスタンスごとにTEMPセグメントを作成し、インスタンス内ではHVエンキューを使用する実行計画の解説 68 Copyright © 2021, Oracle and/or its affiliates.

SQL> UPDATE /*+ parallel(2) */ t1 SET c2 = c2+1
; 実行計画（パラレルDMLが無効） ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | UPDATE STATEMENT | | | 1 | UPDATE | T1 | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | | 4 | PX BLOCK ITERATOR | | | 5 | TABLE ACCESS FULL| T1 | パラレル実行（UPDATE/DELETE/MERGE）実行計画の解説 SQL> UPDATE /*+ enable_parallel_dml parallel(2) */ t1 SET … ; 実行計画（パラレルDMLが有効） ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | UPDATE STATEMENT | | | 1 | PX COORDINATOR | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | | 3 | UPDATE | T1 | | 4 | PX BLOCK ITERATOR | | | 5 | TABLE ACCESS FULL| T1 | SQL> DELETE /*+ parallel(2) */ FROM t1 ; 実行計画（パラレルDMLが無効） ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | DELETE STATEMENT | | | 1 | DELETE | T1 | | 2 | PX COORDINATOR | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | | 4 | PX BLOCK ITERATOR | | | 5 | TABLE ACCESS FULL| T1 | SQL> DELETE /*+ enable_parallel_dml parallel(2) */ FROM t1 ; 実行計画（パラレルDMLが有効） ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | DELETE STATEMENT | | | 1 | PX COORDINATOR | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | | 3 | DELETE | T1 | | 4 | PX BLOCK ITERATOR | | | 5 | TABLE ACCESS FULL| T1 | Copyright © 2021, Oracle and/or its affiliates. 69

実行計画を比較する（第77回） • SQLパフォーマンス・アナライザー（SPA） • データベースの変更（バージョンアップなど）でパフォーマンス比較（実行計画の変化など） • Real Application Testingオプションが必要 •
DBMS_XPLAN.COMPARE_PLANSファンクション（19cから） • 実行計画の違いの原因を特定する実行計画の比較 Copyright © 2021, Oracle and/or its affiliates. 73

DBMS_XPLAN.COMPARE_PLANSファンクション（サンプル） REPORT --------------------------------------------------------------------------------------------- COMPARE PLANS REPORT --------------------------------------------------------------------------------------------- Current user :
SH Total number of plans : 2 Number of findings : 1 --------------------------------------------------------------------------------------------- COMPARISON DETAILS --------------------------------------------------------------------------------------------- Plan Number : 1 (Reference Plan) Plan Found : Yes Plan Source : Cursor Cache SQL ID : 0hxmvnfkasg6q Child Number : 0 Plan Database Version : 19.0.0.0 Parsing Schema : "SH" SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and s.quantity_sold = 43 Plan ----------------------------- Plan Hash Value : 3519235612 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 469 | | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | PARTITION RANGE ALL | | 1 | 3 | 469 | 00:00:01 | | * 3 | TABLE ACCESS FULL | SALES | 1 | 3 | 469 | 00:00:01 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 3 - filter("S"."QUANTITY_SOLD"=43) --------------------------------------------------------------------------------------------- Plan Number : 2 …<省略>… SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and p.prod_min_price > 200 Plan ----------------------------- Plan Hash Value : 3037679890 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 34 | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | * 2 | HASH JOIN | | 781685 | 10161905 | 34 | 00:00:01 | | * 3 | TABLE ACCESS FULL | PRODUCTS | 61 | 549 | 2 | 00:00:01 | | 4 | PARTITION RANGE ALL | | 918843 | 3675372 | 29 | 00:00:01 | | 5 | BITMAP CONVERSION TO ROWIDS | | 918843 | 3675372 | 29 | 00:00:01 | | 6 | BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX | | | | | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("P"."PROD_ID"="S"."PROD_ID") * 3 - filter("P"."PROD_MIN_PRICE">200) Notes ----- - This is an adaptive plan Comparison Results (1): ----------------------------- 1. Query block SEL$1: Transformation JOIN REMOVED FROM QUERY BLOCK occurred only in the reference plan (result query block: SEL$A43D1678). 実行計画の比較 Copyright © 2021, Oracle and/or its affiliates. 74 参照プランの実行計画比較プランの実行計画と比較結果参照プランが「結合排除」変換を行っている

• 実行計画はデータベースの初歩になりますので、最低限の知識はもちましょう • 実行計画はSQL監視またはDBMS_XPLAN.DISPLAY_CURSORを使用してA-Rowsを正しく確認する • アクセス方法、結合方法とタイプ、結合順序、パラレル・データ分散方法は見れるようになりましょうまとめ Copyright
© 2021, Oracle and/or its affiliates. 75

Oracle Database Technology Night #48 津島博士のパフォーマンス講座 - SQLチューニングは実行計画から

Oracle Database Technology Night #48 津島博士のパフォーマンス講座 - SQLチューニングは実行計画から

More Decks by oracle4engineer

Other Decks in Technology

Featured

Transcript

Oracle Database Technology Night #48　津島博士のパフォーマンス講座 - SQLチューニングは実行計画から

Oracle Database Technology Night #48　津島博士のパフォーマンス講座 - SQLチューニングは実行計画から