実行計画についてのよくある誤解～Oracle Database編～ #dbltokyo 5

Copyright © 2017 CO-Sol Inc. All Rights Reserved. 1Copyright © 2017 CO-Sol Inc. All Rights Reserved.

2017年9月19日株式会社コーソル渡部亮太

実行計画についてのよくある誤解～Oracle Database編～

Copyright © 2017 CO-Sol Inc. All Rights Reserved. 2

本セミナーについて

• Oracle Databaseを主に想定して、実行計画に関する「よくある誤解」を題材にして、実行計画について説明します。

– Oracle Database以外のRDBMSの実行計画の扱いについて考えてみる契機にもなるかもしれません。

• 9月7日 db tech showcase 2017 tokyoの「Oracleの実行計画を読んでみよう！」の内容を、切り口を変えて再構成した内容になっています。

– https://www.slideshare.net/ryotawatabe/20170907-dbts2017-tokyo-cosol-how-to-read-oracle-execution-plan

– 聴講済みのかたはごめんなさい m(_ _)m

– コマンドなどは本セッションでの説明を割愛しているため、お知りになりたいかたは、「Oracleの実行計画を読んでみよう！」の資料を参照してくださいませ


自己紹介+所属会社紹介

• 渡部亮太（わたべりょうた）

– JPOUG 共同創設者、ボードメンバー

– 日本に8名しかいないOracle ACEの一人

– 著書「Oracleの基本」9月22日発売！「プロとしてのOracleアーキテクチャ入門[第2版]」

– ブログ「コーソルDatabaseエンジニアのBlog」http://cosol.jp/techdb/

• 株式会社コーソル

– 「CO-Solutions＝共に解決する」の理念のもと、Oracle技術に特化した事業を展開中。心あるサービスの提供とデータベースエンジニアの育成に注力している

– 社員数: 132名 (2017年9月時点)

– ORACLE MASTER Platinum 11g 取得者数 50名ORACLE MASTER Platinum 12c 取得者数 34名取得者数日本 No.1 (おそらくWorld WideでNo.1)

http://www.oracle.com/jp/education/omdata-171891-ja.html


[宣伝] 書籍「Oracleの基本」9月22日発売！

• データベース、Oracleがわからない人が、Oracleおよびその利用・運用について理解するのに最適（だと思う）な入門書

• お近くに、IT基礎研修を終えた新人エンジニア、データベースを知らないのにOracleを使うことになったエンジニアがいらっしゃったら是非！


[コーソル紹介] 技術者育成の取り組み

ORACLE MASTER Platinum取得者数国内No1！

充実した教育＋支援制度で技術者育成に力を入れています！


製品サービス

[コーソル紹介] 弊社ソリューションについて

独自のサービスと製品を掛け合わせ、データベースの運用を強力に支援致します。

＆


[JPOUG] 11月17日(金) イベントのお知らせ

• Japan Oracle User Groupで、 Dockerをテーマにしたイベントをやります！

– 11月17日(金) 19:00- (開始時間は仮) @ オラクル青山センター

• Docker の利活用やTips、興味深い技術要素などについて15分間語ってくれる方を募集しています！是非 https://goo.gl/forms/BNWRso2ZC9nJ1paS2 よりご応募くださいませ。


本セミナーで取り上げる「誤解」

• 誤解1) 実行計画はEXPLAIN PLANで確認すれば十分！

• 誤解2) 実行計画はもっともインデントが深いステップから実行される！

• 誤解3) Rows統計の値が同じなら、アクセスしたデータ量は同程度の効率！

• 誤解4) 実行計画の各ステップは必ず1回だけ実行される


実行計画とは


実行計画とは

• 実行計画は、SQLがOracleで実行される手順を示す• 実行計画はコストベースオプティマイザ(CBO; Cost-

Based Optimizer)が作成する– CBOはOracleの内部コンポーネント– オプティマイザ統計が最新でないと適切な実行計画が作成され

ないことに注意

• 不適切な実行計画でSQLが実行されると、本来であれば実現できる処理パフォーマンスを得られない

---------------------------------------------------------------------| Id | Operation | Name | Rows |...| Time |---------------------------------------------------------------------| 0 | SELECT STATEMENT | | |...| || 1 | TABLE ACCESS BY INDEX ROWID| TAB0 | 16 |...| 00:00:01 ||* 2 | INDEX RANGE SCAN | T0_IDX | 16 |...| 00:00:01 |---------------------------------------------------------------------


Oracleの実行計画に含まれる情報

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------SQL_ID 3hrvf65nm048d, child number 0-------------------------------------SELECT * from tab0 WHERE col_idx = 'A'

Plan hash value: 2195618789

--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| || 1 | TABLE ACCESS BY INDEX ROWID| TAB0 | 16 | 32160 | 7 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T0_IDX | 16 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------

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

2 - access("COL_IDX"='A')

ツリー構造をしたステップ各ステップの統計値

補足情報

ステップ実行計画の実行手順における実行要素親子関係がある

←SQL文

sql_id: SQL文の識別子（Oracleが自動的に付与）

←実行計画のハッシュ値

※：取得方法によって、情報が表示される／されないが異なる点に注意


誤解1) 実行計画はEXPLAIN PLANで確認すれば十分！


EXPLAIN PLANの実行例

SQL> EXPLAIN PLAN FOR SELECT * FROM tab0 WHERE col_idx = 'A';

解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Plan hash value: 2195618789

--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 16 | 32160 | 7 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TAB0 | 16 | 32160 | 7 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T0_IDX | 16 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------

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

2 - access("COL_IDX"='A')

14行が選択されました。




• 作成される実行計画は、実行時の実行環境に左右される→ EXPLAIN PLANで確認した実行計画は、確認対象の環境やタイミングにおける実行計画と同じである保証はない

Oracle

SQL>

SQL*Plus

(EXPLAIN PLAN実行)

Javaなどのカスタムアプリケーション

同一のSQLを実行

異なる実行計画！


Oracle

共有プール

[補足] 「そのSQL実行の」実行計画を確認する方法

• 基本的な考え方は、「SQL実行後、共有プールにキャッシュされている実行計画を確認する」

– キャッシュを直接確認する方法、キャッシュの履歴データを確認する方法（AWR、Statspack）などのバリエーションがある

• ただし、同一のSQLについて、複数の実行計画がキャッシュされている場合はちょっと厄介・・・

– 調査対象のSQL実行において、どの実行計画が使用されたかをいくつかの情報をもとに調査する必要がでてくる・・・（直近の実行時間、直近の実行計画作成時間など）

アプリケーション

②キャッシュから実行計画を確認①SQL実行

→実行計画がキャッシュされる


誤解2) 実行計画は最もインデントが深いステップから実行される！


誤解2) 実行計画は最もインデントが深いステップから実行される！

• 誤解を前提にすると、最初に実行されるステップはcだが、実際は？？

----------------------------------------------------| Id | Operation | Name |----------------------------------------------------| 0 | SELECT STATEMENT | || 1 | NESTED LOOPS | ||* 2 | TABLE ACCESS FULL | PA || 3 | TABLE ACCESS BY INDEX ROWID | CH ||* 4 | INDEX RANGE SCAN | IDX_CHPA |----------------------------------------------------

a

b

c


ツリー構造を整理

-------------------------------------------------| Id | Operation | Name |-------------------------------------------------| 0 | SELECT STATEMENT | || 1 | NESTED LOOPS | ||* 2 | TABLE ACCESS FULL | PA || 3 | TABLE ACCESS BY INDEX ROWID| CH ||* 4 | INDEX RANGE SCAN | IDX_CHPA |-------------------------------------------------

0: SEL 1: NL

2: TAF

3: TAIR 4: IRS


ツリー構造のたどり方

0: SEL 1: NL

2: TAF

3: TAIR 4: IRS

1. 最上位から親→子方向にたどってゆく

2. 複数の「子ステップ」がある場合、表形式表示で上にある「子ステップ」に進む

3. そのステップから、さらに親→子方向にたどってゆく

4. 最下位の「子ステップ」に到達したら、実行を開始する。

5. 実行終了したら、「親ステップ」に戻る

6. その「親ステップ」に、他の「子ステップ」がある場合、表形式表示で上にある「子ステップ」に進み、3. へない場合、その「親ステップ」を実行し、その「親ステップ」の「さらに親のステップ」に戻り、6.を繰り返す


というわけで正解は

• かならずしも、最も深いインデントのステップから実行されるわけではないことに注意– きちんとした説明が面倒だからか・・・誤った情報が結構流布されている

-------------------------------------------------| Id | Operation | Name |-------------------------------------------------| 0 | SELECT STATEMENT | || 1 | NESTED LOOPS | ||* 2 | TABLE ACCESS FULL | PA || 3 | TABLE ACCESS BY INDEX ROWID| CH ||* 4 | INDEX RANGE SCAN | IDX_CHPA |-------------------------------------------------

0: SEL 1: NL

2: TAF

3: TAIR 4: IRS

最初に実行


誤解3) Rows統計の値が同じならば、アクセスしたデータの量は同じ



統計値解釈・意味累積?

Rows そのステップが上位のステップに返す見積行数 N

Bytes そのステップが上位のステップに返すRowsの見積バイト数 N

Cost そのステップ以下のステップの累積コストコスト＝平たく言うと「オラクルが予測した処理時間（単位なし）」

Y

Time そのステップ以下のステップの予想処理時間 Y

SELECT * FROM tab0 WHERE col_idx = 'A' AND col_noix='B'--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| ||* 1 | TABLE ACCESS BY INDEX ROWID| TAB0 | 1 | 2010 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T0_IDX | 3 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------

1 - filter("COL_NOIX"='B')2 - access("COL_IDX"='A')

0: SEL 1: TIR 2:IRS

Rows=1 Rows=3

Rows統計


• Rows統計＝そのステップが上位のステップに返す見積行数であり、 ≠そのステップでアクセスした行数ではない

• Rows統計が同じでも、アクセスした行数（＝データの量）が異なることは十分に起こりうる

SELECT * from t9 WHERE id = 1

-----------------------------------------------------| Id | Operation | Name | Rows |-----------------------------------------------------| 0 | SELECT STATEMENT | | || 1 | TABLE ACCESS BY INDEX ROWID| T9 | 1 ||* 2 | INDEX UNIQUE SCAN | PK_T9 | 1 |-----------------------------------------------------

SELECT * from t9 WHERE id = 1

------------------------------------------| Id | Operation | Name | Rows |------------------------------------------| 0 | SELECT STATEMENT | | 1 ||* 1 | TABLE ACCESS FULL| T9 | 1 |------------------------------------------

TABLE ACCESS FULL

filterINDEX UNIQUE SCAN

TABLE ACCESS BY INDEX ROWID

access



誤解4) 実行計画の各ステップは必ず1回だけ実行される


Starts実行統計 : オペレーションの実行回数

SELECT cid, cname, pa.pid, pnameFROM ch, paWHERE ch.pid = pa.pid and pa.pid IN (1,2,3)

-------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 3 || 1 | NESTED LOOPS | | 1 | 3 | 3 ||* 2 | TABLE ACCESS FULL | CH | 1 | 3 | 3 || 3 | TABLE ACCESS BY INDEX ROWID| PA | 3 | 1 | 3 ||* 4 | INDEX UNIQUE SCAN | PK_PA | 3 | 1 | 3 |-------------------------------------------------------------------------

0: SEL 1: NL

2: TAF

4: TAIR 5: IUS

Starts=3 → 3回実行


Starts実行統計 : オペレーションの実行回数

SELECT cid, cname, pa.pid, pnameFROM ch, paWHERE ch.pid = pa.pid and pa.pid IN (1,2,3)

-------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 3 || 1 | NESTED LOOPS | | 1 | 3 | 3 ||* 2 | TABLE ACCESS FULL | CH | 1 | 3 | 3 || 3 | TABLE ACCESS BY INDEX ROWID| PA | 3 | 1 | 3 ||* 4 | INDEX UNIQUE SCAN | PK_PA | 3 | 1 | 3 |-------------------------------------------------------------------------

INDEX UNIQUE SCAN

TABLE ACCESSBY INDEX ROWID

NL

TABLE ACCESSFULL

CH

PK_PA

PA

access

① ② ③

0: SEL 1: NL

2: TAF

4: TAIR 5: IUS

Starts=3 → 3回実行①

②③

① ② ③


まとめ

• 誤解1) 実行計画はEXPLAIN PLANで確認すれば十分！→ 実行後に共有プール（キャッシュ）から確認

• 誤解2) 実行計画はもっともインデントが深いステップから実行される！→ 一概に言えない。ルールに従い実行されるため、インデントが深いステップが最初に実行されないケースもある

• 誤解3) Rows統計の値が同じなら、アクセスしたデータ量は同程度の効率！→ そのステップが返す行数に過ぎないので、同程度の場合もあれば、もっと大きい場合もある

• 誤解4) 実行計画の各ステップは必ず1回だけ実行される→ たいてい1回だが、複数回実行されることもある

実行計画についてのよくある誤解～Oracle Database編～ #dbltokyo 5

Data & Analytics

Transcript of 実行計画についてのよくある誤解～Oracle Database編～ #dbltokyo 5

実行計画についてのよくある誤解 ～Oracle Database編～ #dbltokyo 5

Data & Analytics

Transcript of 実行計画についてのよくある誤解 ～Oracle Database編～ #dbltokyo 5

実行計画についてのよくある誤解～Oracle Database編～ #dbltokyo 5

Transcript of 実行計画についてのよくある誤解～Oracle Database編～ #dbltokyo 5