WR blog

about Enterprise IT, Oracle Database, Jazz/Fusion Music, etc…

WR blog RSS Feed
 
 
 
 

Posts tagged SQLチューニング

フィルタ述語とアクセス述語 (実行計画の読み方#3)

さて、これまでで、実行計画のツリーのたどり方と、ツリーの構成要素である各オペレーションで 実行されている処理の概要がわかったと思います。

しかし、これだけの情報では、実行計画と実行されたSQLのWHERE条件を対応づけて理解することは 難しいです。(できなくはありませんが、かなり推測に頼る形になります。)

SQLのWHERE条件と対応付けるためには、フィルタ述語とアクセス述語に着目する必要があります。

Predicate Informationセクション

実行計画をDBMS_XPLAN.DISPLAY_CURSORで取得した場合、フィルタ述語とアクセス述語に関する情報は Predicate Informationセクションに表示されます。

—————————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————————– | 0 | SELECT STATEMENT [...]

オペレーションの一覧と最低限覚えておくべきオペレーション (実行計画の読み方#2)

先のエントリの説明で、実行計画をいわば「形式的には読める(*1)」ようにはなりました。 しかし、実行計画が何をやっているのかという、意味的な観点では「理解できていない」はずです。 実行計画の処理内容を理解するためには、実行計画を構成するオペレーションの処理内容を理解する 必要があります。

(*1) 「たどれる」という表現のほうが近いかもしれませんが・・・

オペレーションの一覧

Oracle Databaseには非常に多くのオペレーションが存在し、 大部分のオペレーションについては、マニュアルの以下の箇所で説明されています。

10.2 11.1 11.2

最低限覚えておくべきオペレーション

全てのオペレーションに関して説明することは、現実的でないため、 先のエントリで説明した実行計画に含まれるオペレーションを説明します。 なお、このエントリに含まれるオペレーションは、非常に多くの実行計画で使用されうる、 非常に重要なものであり、最低限覚えておく必要があります。

—————————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————————– | 0 | SELECT STATEMENT [...]

実行計画のツリーのたどり方 (実行計画の読み方#1)

これまでのエントリに記載してきたように、実行計画は階層的なツリー構造で表現 されます。 本エントリでは、実行計画の読み方の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 [...]

実行計画の読み方

これまでのエントリで、DBMS_XPLAN.DISPLAY_CURSORをつかってV$SQL_PLAN から実行計画を取得する方法をオススメしてきました。 この方法は、手軽であり、かつ、SQLトレースやEXPLAIN PLANの問題点に対処できる 優れた方法であることがお分かりいただけたかと思います。

しかし、当たり前の話ですが、実行計画を取得できたとしても、読めなければ意味がありません。 このため、取得した実行計画の読み方をこれからいくつかのエントリで説明したいと思います。

以下の構成で進める予定です。(エントリ記載次第、リンクを張る予定)

実行計画のツリーのたどり方 オペレーションの一覧と最低限覚えておくべきオペレーション フィルタ述語とアクセス述語 結合の動作イメージとオペレーション

自身の経験上、実行計画の読み方は一度の説明で理解しにくいようです。 このため、一連のエントリでは、わざと同じようなことを何度も繰り返し説明するような 形にしています。

SQLチューニングアドバイザの代用品としての動的サンプリング

先のエントリ で、実行計画の見積もりミスの可能性を調べるため、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任せにすることが できます。

DISPLAY_CURSORでCBOの見積りミスを簡単に確認する

前回のエントリで 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)が非常に有効です。

DBMS_XPLAN.DISPLAY_CURSORの使い方とちょっとした落とし穴

先日のエントリ SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わない でお勧めしたDBMS_XPLAN.DISPLAY_CURSORについて、基本的な使い方を説明します。

基本的なDBMS_XPLAN.DISPLAY_CURSORの使い方

詳細はマニュアル(Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス) のDBMS_XPLANパッケージの箇所をご確認いただきたいのですが、

10.2 : DISPLAY_CURSORファンクション 11.1 : DISPLAY_CURSORファンクション 11.2 : DISPLAY_CURSORファンクション

DISPLAY_CURSORファンクションには以下の引数を指定します。

DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT [...]

SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは(ほとんど)使わない

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つの方法には、いくつか使いにくい点があるからです。

優れたカーソル共有があればバッチ処理SQLのリテラル化は不要か?

先日のエントリ に、 Oracle Database 11.1 からは優れたカーソル共有(ACS, Adaptive Cursor Sharing, 適用カーソル共有) という機能が導入されているため、エントリの内容が当てはまらないと書きました。 優れたカーソル共有が機能すると、バインド変数化されたSQLと共有カーソルの対応関係が異なってきます。

具体的には、従来(10.2以前)では、バインド変数値によらず、 原則的にバインド変数化されたSQLと共有カーソルは1対1関係にありましたが、 11.1で優れたカーソル共有が機能する(デフォルトで有効)と、 バインド変数に指定された値によって、既存の共有カーソル(≒実行計画)を使用 すべきでないとOracleが判断すると、新規に別の共有カーソル(≒実行計画)を作成し、 SQL実行にこの共有カーソルを使用します。 すなわち、バインド変数化されたSQLと共有カーソルは1対多の関係となる可能性があります。

(*1) ここの説明において、共有カーソルは子カーソルを指します。

では、優れたカーソル共有により、意図しない実行計画が使用される事態を予防できるか?

突然のSQLパフォーマンスダウンを防ぐためのバッチ処理SQLリテラル化のススメ

またまた前回の更新から時間が空いてしまいました・・・。 いろいろと忙しく時間が取れないのですが、このままだと2010年まったくblogを書いていないことになってしまうのと、 twitterで後押しをいただいたこともあり、何とか書き上げました。お役に立てば幸いです。

はじめに

最近、Oracle Databaseのパフォーマンスに関連するトラブルシューティング を担当することが多いのですが、特に多い事例が、 ある特定のSQLのパフォーマンスが突然ダウンするような問題です。

原因はいろいろなのですが、バッチ処理で実行されるSQLに、バインド変数 を使用しているためにパフォーマンスが突然ダウンしてしまった状況が多くみられました。 (もちろんケースバイケースの側面はありますが、)バッチ処理で実行されるSQLは 一般的にバインド変数化するべきではありません。 SQLをバインド変数化することにはメリットもありますが、デメリットもあります。 特に、デメリットの部分が広く知られていないため、プログラムをコーディング するときに、開発者がある種機械的・盲目的にSQLをバインド変数化していることが この問題の背景にあるのではと思っています。

本エントリでは、SQLをバインド変数化のメカニズムと SQLをバインド変数化することのメリットとデメリットを説明した上で、 バッチ処理で実行されるSQLをリテラルSQL化することをお勧めします。

Profile

渡部亮太 / Watabe Ryota
代官山在住のOracle Database Engineer。 株式会社コーソル所属。講演/講師業もぼちぼち。書籍「プロとしてのOracle運用管理入門」「プロとしてのOracleアーキテクチャ入門」買ってくれるとうれしいなっと。 twitter:wrcsus4

Book



Other Works

Certifications

  • Oracle Master 10g Platinum
  • Oracle Master 11g Gold
  • Oracle Master Silver Oracle PL/SQL Developer
  • Oracle Master Expert 10g RAC
  • Oracle Master Expert Oracle on Linux
  • LPIC level2
  • CCNA
  • 日商簿記3級

Contact

wrcsus4 _at_ gmail _dot_ com

Archives

Recent Posts

Recent Comments

Categories

Tags

Meta