Blogの更新が滞り、申し訳ございません・・・
9/30(金) Oracle LOVERS シーズン2 第2回 でお話させていただきました。
50名強(もしかするとそれ以上)という多くの方にご出席いただいただけでなく、
著名なOracleエンジニア方々の前で話すということで、非常に緊張いたしましたが
主催者の吉田さんのご配慮もあり、楽しい時間をすごすことができました。
わざわざ貴重なお時間を割いて、ご参加いただいた皆様、ありがとうございました。
実際の業務に有用な情報を1つでもご提供できたらうれしく思います。
また、主催者の吉田さん、いつもありがとうございます。
吉田さんの人柄がなせるものと思いますが、Oracle LOVERSはいつもリラックスした楽しい雰囲気で、
とてもすばらしいですね。
お忙しいとは思いますが、今後もぜひOracle LOVERSを継続していただけたらと思います。
今回の発表資料を公開する予定はありませんが、前半部分のデモで多用した
DBMS_XPLAN.DISPLAY_CURSORの使い方については過去のBlogエントリの内容が参考になると
思いますので、以下にリンクを張っておきます。参考にしていただければ幸いです。
それでは・・・
October 14th, 2011 | Tags: Oracle | Category: Oracle | Leave a comment
さて、これまでで、実行計画のツリーのたどり方と、ツリーの構成要素である各オペレーションで
実行されている処理の概要がわかったと思います。
しかし、これだけの情報では、実行計画と実行されたSQLのWHERE条件を対応づけて理解することは
難しいです。（できなくはありませんが、かなり推測に頼る形になります。）
SQLのWHERE条件と対応付けるためには、フィルタ述語とアクセス述語に着目する必要があります。
Predicate Informationセクション
実行計画をDBMS_XPLAN.DISPLAY_CURSORで取得した場合、フィルタ述語とアクセス述語に関する情報は
Predicate Informationセクションに表示されます。
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | PA | 1 | 2004 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 20080 | 11 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_CHPA | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ←★
---------------------------------------------------
2 - filter("PA"."PID"=1)
4 - access("CH"."PID"=1)
Predicate Informationセクション内のフィルタ述語、アクセス述語の先頭には
オペレーション（ステップ）に対応するIdが記載されています。
More »
June 30th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Leave a comment
先のエントリの説明で、実行計画をいわば「形式的には読める(*1)」ようにはなりました。
しかし、実行計画が何をやっているのかという、意味的な観点では「理解できていない」はずです。
実行計画の処理内容を理解するためには、実行計画を構成するオペレーションの処理内容を理解する
必要があります。
- (*1) 「たどれる」という表現のほうが近いかもしれませんが・・・
オペレーションの一覧
Oracle Databaseには非常に多くのオペレーションが存在し、
大部分のオペレーションについては、マニュアルの以下の箇所で説明されています。
最低限覚えておくべきオペレーション
全てのオペレーションに関して説明することは、現実的でないため、
先のエントリで説明した実行計画に含まれるオペレーションを説明します。
なお、このエントリに含まれるオペレーションは、非常に多くの実行計画で使用されうる、
非常に重要なものであり、最低限覚えておく必要があります。
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | PA | 1 | 2004 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 20080 | 11 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_CHPA | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
More »
June 30th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Leave a comment
インサイトテクノロジーの新久保さんの想いに共感し、Japan Oracle User Group - JPOUG にメンバーとして参加させていただきました！
Oracle Database Userのみなさんに役立つ活動を行っていきたいと思います！
何かご意見がありましたら、twitterやblogのコメント欄で頂戴できればうれしいです！
June 21st, 2011 | Tags: JPOUG, Oracle | Category: Oracle | Leave a comment
これまでのエントリに記載してきたように、実行計画は階層的なツリー構造で表現
されます。
本エントリでは、実行計画の読み方のfirst stepとして、
実際の処理順序に即したツリー構造のたどり方を説明します。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID dvv5bah5b4k51, child number 1
-------------------------------------
SELECT /*+ FULL(PA) INDEX(CH idx_chpa) USE_NL(CH) LEADING(PA) */ cid,
cname, pa.pid, pname FROM ch, pa WHERE ch.pid = pa.pid and pa.pid
= 1
Plan hash value: 3514264536
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | PA | 1 | 2004 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 20080 | 11 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_CHPA | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PA"."PID"=1)
4 - access("CH"."PID"=1)
24 rows selected.
実行計画のツリー構造とオペレーションの親子関係
実行計画は、一般に複数のオペレーションから構成されます。オペレーションは、親子関係に
したがってインデントして整形されたツリー構造となります。
たとえば、上記の実行計画は、NESTED LOOPS, TABLE ACCESS FULL などのオペレーション
から構成されます。(オペレーションの種類と処理内容については次のエントリで説明します。）
オペレーションの親子関係についてですが、
Id=1 NESTED LOOPSは Id=2 TABLE ACCESS FULLと、Id=3 TABLE ACCESS BY INDEX ROWID
の親となります。同様に Id=3 TABLE ACCESS BY INDEX ROWID
Id= 4 INDEX RANGE SCAN の親となります。
Id=0 SELECT STATEMENT
↑
│行ソース
│
Id=1 NESTED LOOPS
↑ ↑
行ソース┌─────┘ └─────┐行ソース
│ │
Id=2 TABLE ACCESS FULL Id=3 TABLE ACCESS BY INDEX ROWID
↑
│行ソース
│
Id=4 INDEX RANGE SCAN
それぞれのオペレーションは、行ソースとよばれる表から取得した行データ（のサブセット）を出力します。
親オペレーションは子オペレーションの行ソースを入力として処理を行います。
概念的な説明だけではわかりにくいので、先の実行計画がどのように処理されるかを
イメージしながら、読み方を説明してみましょう。
More »
June 20th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Comments (1)
これまでのエントリで、DBMS_XPLAN.DISPLAY_CURSORをつかってV$SQL_PLAN
から実行計画を取得する方法をオススメしてきました。
この方法は、手軽であり、かつ、SQLトレースやEXPLAIN PLANの問題点に対処できる
優れた方法であることがお分かりいただけたかと思います。
しかし、当たり前の話ですが、実行計画を取得できたとしても、読めなければ意味がありません。
このため、取得した実行計画の読み方をこれからいくつかのエントリで説明したいと思います。
以下の構成で進める予定です。（エントリ記載次第、リンクを張る予定）
自身の経験上、実行計画の読み方は一度の説明で理解しにくいようです。
このため、一連のエントリでは、わざと同じようなことを何度も繰り返し説明するような
形にしています。
June 20th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Leave a comment
先のエントリ
で、実行計画の見積もりミスの可能性を調べるため、CBOの見積もり行数と実行時行数の
差異をチェックする方法をお伝えしました。
実際にCBOの見積もり行数と実行時行数の差異が大きいSQLを見つけた場合は、
現在使用している実行計画が最適かどうか、最適な実行計画はどのようなものかを
検討する必要が出てきます。
しかし、この作業は簡単なものではありません。
先のエントリ
の例は、1つのテーブルにのみアクセスするきわめてシンプルなものであり、
テーブルスキャン(TABLE ACCESS FULL)
よりもインデックススキャン (INDEX RANGE SCAN)
が効率的であるとすぐにわかりました。
しかし、通常のアプリケーションではSQLはもっと複雑であり、
最適な実行計画の検討は、Oracle Databaseに関するSQLチューニングスキルが必要であり、時間もかかる作業です。
Oracle Database 9.2 以前など、アドバイザ機能が充実していなかったバージョンを
使用していたときは、スキルを持ったデータベースエンジニアが、さまざまな角度から
検討し、最適と思われる実行計画を検討していたと思います。
この作業は、実際に効果が得られるかどうか不透明なわりに、時間と労力を要する非効率的なものでした。
チューニング作業は、時間をかければかけただけ、必ず改善されるという保障があるわけではないという
側面があるからです。
Oracle Database 10g以後では、アドバイザ機能が充実し、SQLチューニングアドバイザ
を活用して、Oracle Database が自動的に最適な実行計画を作成してくれるように
なっており、作業コストを大幅に削減できます。
しかし、SQLチューニングアドバイザを使用するためには Enterprise Edition の利用に
くわえて、別売のオプション機能 Diagnostic Pack と Tuning Packが必要であるため、
使用できる人が限られるのが現実ではないでしょうか。
動的サンプリングによる最適な実行計画検討の自動化
実は、動的サンプリングという機能を使うことで、SQLチューニングアドバイザが使用で
きない場合でも、最適と思われる実行計画の検討をOracle Database任せにすることが
できます。
More »
May 6th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Leave a comment
前回のエントリで
DBMS_XPLAN.DISPLAY_CURSOR の format引数に’TYPICAL’と
‘ALL ALLSTATS LAST’ を主に指定すると説明しました。
本エントリでは、format引数 ‘ALL ALLSTATS LAST’の機能を説明し、
その一例として、CBOの見積りミスを簡単に確認することができることを説明したいと思います。
format引数 ‘ALL ALLSTATS LAST’
format引数 ‘ALL ALLSTATS LAST’を指定すると、
実行計画ツリーを示す PLAN_TABLE_OUTPUT、
フィルタ条件やアクセス情報を示す Predicate Informationに加えて、
複雑なSQLを分割したクエリブロック情報や表のエイリアス情報を示すQuery Block Name / Object Alias、
アクセスした列に関する情報を示すColumn Projection Information が表示されます。
また、PLAN_TABLE_OUTPUTの列に、オペレーション単位の各種実行統計が追加されます。
format引数’TYPICAL’を指定した場合は、確認できるのはCBOによって見積もられた見積統計
(見積もり行数、見積もりバイトなど)しか確認できませんでしたが、
実行時の実行統計(実際にオペレーションを実行した結果得られた行数など）
が確認できます。
また、format引数 ‘LAST’が指定されていることで、
直近のSQL実行の統計情報を表示します。
逆に’LAST’を指定しないと、得られる統計情報が共有カーソル単位の累積値となってしま
い（同じSQLを複数回実行すると複数回実行分の合算値となる）、1回実行分の統計を確認したい
ような通常の用途では不適切であることに注意してください。
なお、前回のエントリで説明しましたが、format引数 ‘ALLSTAT’、’LAST’は
調査対象のSQLの実行前にstatistics_levelパラメータをALLに設定するか、
SQLに /*+ gather_plan_statistics */ ヒントを指定した場合でのみ有効となる
ことに注意してください。
CBOの見積もりミスを確認する
このように、format引数 ‘ALL ALLSTATS LAST’ を指定したDBMS_XPLAN.DISPLAY_CURSOR
を用いて実行計画を取得すると、様々な情報が取得できます。
CBOが作成した実行計画が、本当に最適な実行計画であるか疑問がある場合には、
オペレーション単位で確認できる様々な統計の1つである見積行数(E-Rows)と
実行時行数(A-Rows)が非常に有効です。
More »
April 30th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Comments (1)
先日のエントリ
SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは（ほとんど）使わない
でお勧めしたDBMS_XPLAN.DISPLAY_CURSORについて、基本的な使い方を説明します。
基本的なDBMS_XPLAN.DISPLAY_CURSORの使い方
詳細はマニュアル(Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス)
のDBMS_XPLANパッケージの箇所をご確認いただきたいのですが、
DISPLAY_CURSORファンクションには以下の引数を指定します。
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
sql_id: SQLの識別子
child_number : 子カーソル番号
format: 出力フォーマット(後ほど簡単に説明)
このファンクションはテーブルファンクションであるため、TABLE演算子で変換すると
通常の表のように扱えます。典型的には引数sql_idに調査対象のSQLのsql_idを指定して、
以下のように使用します。
More »
March 24th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Leave a comment
Oracle DatabaseのSQLパフォーマンス問題が発生したときの、実行計画の取得についてまとめます。
SQLパフォーマンス問題が発生したときに、まず確認すべき情報はSQLの実行計画ですが、
Oracle Databaseには、実行計画を確認する方法が、大きく分けて3つあります。
- EXPLAN PLAN : （一般にSQL*Plusから）EXPLAN PLAN FOR < 実行したいSQL>を実行して、実行計画を確認します。
- SQLトレース : SQLの実行前にalter session sql_trace=true;を実行して、トレースファイルに出力された情報から実行計画を確認します。
- DBMS_XPLAN.DISPLAY_CURSORプロシージャ : 共有プールに保管された共有カーソルの情報から、実行計画を確認します。共有カーソルが共有プールからage-outされた場合、実行計画は確認できません。
EXPLAIN PLANとSQLトレースを使わない理由
従来から存在しており、かつ、広く知られている実行計画の取得方法は、
EXPLAIN PLANとSQLトレースの2つです。しかし、私がOracle DatabaseのSQLパフォーマンス問題
に対応する場合、これらの2つの方法は原則的に使いません。
これら2つの方法には、いくつか使いにくい点があるからです。
More »
February 24th, 2011 | Tags: Oracle, SQLチューニング | Category: Oracle | Comments (2)
