|* 4 | INDEX RANGE SCAN | IDX_CHPA | 10 | | 1 (0)| 00:00:01 | What is going on with this article? 前回の投稿から間が空いてしまいましたが、今回はOracle Databaseの「実行計画」についてお話ししたいと思います。例のとおり、今回もわかりやすさ追求のため、詳細を省略しているところがある点ご了承願います。, Oracle Databaseの「実行計画」について話す前に、まずはSQLが実行されるときの仕組み(流れ)について触れる必要があります。以下に簡単なイメージ(解説つき)を添付しましたので、それをもとに理解を深めてもらえればと思います。(※1), SQLが投げられている先のデータベースのテーブルの中身はこちらです。ユーザが10件登録されています。, 上のイメージにお気づきになったかもしれませんが、SQLを実行するにあたって「実行計画」が重要な役割を果たしているのがわかるかと思います。この例はSELECT文であり、INSERT、UPDATE、DELETE文になるともう少し複雑になってきますが(※2)、本質は変わりません。, Oracle Databaseの「実行計画」とは、文字どおり「SQLを実行するための計画」と言えばそれまでなのですが、言い換えると、どうしたらより短い時間でSQLを実行できるか、計算して導き出された、具体的な方法を「計画」としてまとめたものと言えます。実行計画の典型的な例として、上のイメージを挙げます。, ここでは、 付与したヒント句が本当に反映されたかどうかを確認できます。, 開発環境(性能試験以外)なら、statistics_levelをALLにして、formatを"ALLSTATS LAST ADVANCED"しておけば良いかと思います。 今回は共有プールとバッファキャシュのクリアについて記事にしたいと思います。 —————————————————————————————–, Predicate Information (identified by operation id): ←★ ^^^^^^^^^^, 今回実行したSQL(から不要なヒントを除いて整形したもの)は、上記のとおりですが、 アクセス述語に合致したデータにのみ選択的にアクセスします。 指定しなくても、使用されないヒントがある場合は自動で表示されます。, SIer&バックエンドエンジニア&日曜プログラマー。 また、oracleを再起動することで、メモリをクリアする事も出来ますが、上記sql実行の方が、処理時間も短く手続きも簡易です。 参考 ライブラリキャッシュ :sqlの解析結果や実行計画等を保持. ch.pid = pa.pid and pa.pid = 1 を数学的/算術的に変換したものであることが WHERE ch.pid = pa.pid and pa.pid = 1 もう一度、下のSQLをもとに、例として考えてみます。 基本的に仕事外での自分用のメモ(興味があること)として記事を書いています。.  実行計画を作る都度サンプリングするためシステムのパフォーマンスに影響する一方で、 Help us understand the problem.  SQL実行計画を作成する際、統計情報が古いまたはそもそもない場合にサンプリングして取得。 必ずしも正確性・信頼性等を保証するものではありません。 —————————————————————————————– 前回の投稿から間が空いてしまいましたが、今回はOracle Databaseの「実行計画」についてお話ししたいと思います。例のとおり、今回もわかりやすさ追求のため、詳細を省略しているところがある点ご了承願います。 | DBMS_PLAN.DISPLAY_CURSOR(または類似のファンクション)を使用することを を理解するために非常に有効な情報です。, このため、実行計画を確認する際は、フィルタ述語とアクセス述語が出力される 当サイトの情報によるいかなる損失に関して、免責とさせて頂きます。ご利用の際はあらかじめご了承ください。, 特定のセッションから実行された全SQLの実行計画を取得できます。また同じSQLが要した処理時間などがサマリされる, SQL Developer Excelファイルを簡単にインポート/エクスポートする方法. formatに指定できる情報はたくさんあるのですが、よく利用する指定方法だけ以降に記載します。, 次に、"ALL ALLSTATS LAST"を指定した場合です。  収集する日、時間帯を変更することも可能。 SELECT * FROM USERTABLE WHERE USERID='detarame' AND PASSWORD='detarame'; ある日の朝、あるユーザがSQLを実行したことに伴い実行計画がされました。このときUSERTABLEにあるデータは10件(10行)で、実行計画は索引を見るのではなく、当該テーブル全行見るように指示されていました。この日の夜に、別のユーザによる夜間処理でUSERTABLEに100万件のデータが挿入されました。テーブルの中身はこのようになりますね。, この後、統計情報へは反映せず翌朝を迎え、再度このSQLを実行しようとします。さてどうなるでしょうか?, もうわかりますよね?全行見るという実行計画は変わらないので、最大100万行見るという最悪の結果に陥ってしまいます。そして、どうするのが正しかったのでしょうか?これもわかりますよね?100万件のデータが挿入された後で、統計情報へ反映するべきであったのです。それにより実行計画が新しく作り替わり、索引を見るように変更されるというわけです(※3)。, 上述の、テーブルのデータ件数が急激に増えた例のように、表や列、索引等の実態情報は適切なタイミングで統計情報へ反映し、実行計画へ反映する必要があります。統計情報の収集を自動にするか、手動で任意のタイミングにするか、SQL実行時にするかはシステムの特性に依存しますが、重要なのは3者間で整合が取れるようにすることです。, (※1)SQL実行の仕組みについては、以下引用・参考資料の1.、2.、3.にて詳しく解説されています。なお、今回のイメージ作成に当たっては、引用・参考資料の4.をベースにしました。 By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away.  「動的サンプリング」と呼ばれている。 SELECT * FROM USERTABLE WHERE USERID='detarame' AND PASSWORD='detarame'; アクセス述語を使用する索引アクセスで行うことが一般に推奨されます。, ### SQLトレースの実行計画ではフィルタ述語とアクセス述語が表示されないことに注意, 理由は不明ですが、SQLトレースの実行計画にはフィルタ述語とアクセス述語が表示されません。  データの質・量の変化が大きい場合に推奨されている。, SQLの実行計画は統計情報をベースに作成されています。統計情報はデータベースの実態を反映します。そうなると、SQLが最適なパフォーマンスで実行されるためには、実行計画、統計情報、データベースの実態の3者の整合性が取れている必要があります。逆に3者の整合が取れていない、例えば統計情報はデータベースの実態を反映しているが、実行計画には反映されていない場合、SQL実行時のパフォーマンスに影響する可能性があります。. DBMS_XPLAN.DISPLAY_CURSORは、v$sql_planから実行計画を取得することができるファンクションです。ただし、共有プールから実行計画が削除されている場合は取得することができません。, 以降、Oracle 12c R2にてDBMS_XPLAN.DISPLAY_CURSORを使用してみます。, SQL IDを指定して実行計画を取得します。通常はこちらのやり方を利用することが多いです。, 下のように表示されている行ですが、「child number 0」と表示されています。, 今回は同じSQL(SQL_ID)に対して作成された実行計画が一つだったため、「child number 0」になっています。 実行計画とは、オプティマイザがSQLをどの様に処理するか導き出した結果といったところです。SQLが遅い場合は、この実行計画を見てやると問題点が見えてきます。, 実行計画の取得方法を4つのシチュエーションに分けました。1.単体テスト、2.システムテスト、3.本番稼働、4.処理中, 単体テストでは、データ量も質も本番とは異なります。しがたってあくまで机上レベルで狙った実行計画になるか確認するくらいだと思います。, この場合はSQLDeveloperを利用した方法が便利です。確認したいSQLを[ワークシート]にコピーして[実行計画]ボタンをクリックすると実行計画が表示されます。, SQLにバインド変数が含まれていても実行計画は表示されます。ただしバインド変数によって選択される実行計画が変わること多々ありますので注意です。, テスト中に遅いと分かった場合は、問題のプログラムが特定されています。またテスト環境なのでトレースを取得する方法が適しています。, トレースは、特定のセッションから実行された全SQLの実行計画を取得できます。また同じSQLが要した処理時間などがサマリされることで問題箇所を把握しやすいです。, 本番稼働中の環境では、トレースを簡単には取得できないと思います。また何のプログラムが原因なのかもはっきりとしない場合があります。, この場合は、AWRレポートを使用して遅いSQLを特定して、実行計画を確認する方法がてきしています。またこの方法も実際に選択された実行計画です。, ただし、AWRがスナップショットとして記録したものしか確認できません。つまり遅くないSQLはAWRに残らないので見れません。またスナップショットは定期的に保存されるものですので、今この瞬間を見ることはできません。, 遅いプログラムが今まさに動ている場合は、v$sqlからSQLを特定して統計情報を習得する方法があります。この場合も実際に選択された実行計画です。, ただし、共有プールに残っている場合しか確認できません。時間がたつとageoutしますので古い情報は見れません。, 実行計画の取得方法を表にまとめてみました。これを参考に適切な方法で実行計画を確認しましょう。, AWRレポートを読み解くには、高度な知識が要求されます。その上、情報の読み方が詳しくは公開されていません。今回は、データベースにある程度負荷をかけたサンプルを見ながら内容を確認していきます。, 今回は、ログスイッチが多発したケースについてAWRレポートを確認していきたいと思います。, 今回は生トレースと成形後のトレースの両方を見ていきたいと思います。生トレースをじっくりと見ることはあまりないと思いますが、生トレースでないと見えないものもあります。, ソフトウェアベンダーでITコンサルタントとして働いています。製造業のお客様を中心に、業務アプリケーションのデリバリーを担当しています。これまでの経験をフィードバックしていきます。, 当サイトのすべてのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、情報が古くなったりすることもあります。 "E-"はOracleによって推定されたデータで、"A-"は実際の実行結果になります。BuffersはバッファからGetしたデータブロック数です。, statistics_levelを使用せずに、GATHER_PLAN_STATISTICSヒントをつけても同様の結果を表示できます。, format=>'ALL'よりもOutline Dataが増える。 行頭の番号に対応するオペレーションの実行時に、取得したデータから このため、一般にアクセスするブロック数を小さく抑えることができます。, 一方、フィルタ述語は、ブロックにアクセスした後のデータを除外する際に (※3)引用・参考資料の2.に説明のとおり、共有プール上にある実行計画をキャッシュアウトさせる前提で、統計情報の再収集後にOracle Databaseは新しい統計情報をもとに実行計画を作り直します。, 2.門外不出のOracle現場ワザ 第4章 Oracleデータベースの頭脳 「オプティマイザ」徹底研究. それぞれのオペレーションにおいて、SQLのWHERE条件に対応したデータの絞込みが Oracleで実行計画を取得する方法は、以下のように多く存在します。(他にもありますが), が、DBMS_XPLAN.DISPLAY_CURSORが使えれば十分なケースが多いと思います。 —————————————————, 2 – filter(“PA”.”PID”=1) ご使用のバージョンが Oracle Database 10.2.0.4 の場合のみ、当機能を使用する前にまず事前準備として下記 event をパラメータ・ファイルに設定してからインスタンスを再起動する必要があります。 event="5614566 trace name context forever" フラッシュ手順は以下の通りです。 適用されます。アクセスした後にデータを絞り込む形になるため、 しばちょう先生の試して納得!DBAへの道 indexページ みなさん、こんにちは。"しばちょう"こと柴田長(しばた つかさ)です。3月はお客様のシステムのデータベースにおいてSQLの性能テストに没頭してい … statistics_levelをシステム全体で変更することもできますが、オーバーヘッドが大きいので、通常指定することはありません。, E-Rows、E-Bytes、E-Time、A-Rows、A-Time、Buffers が追加されています。 (※2)DBバッファキャッシュ、REDOログバッファ、UNDOデータが登場するとともに、COMMITの処理も加わります。 フィルタ述語とアクセス述語は、ともに述語に合致したデータのみが得られるという DBMS_XPLAN.DISPLAY_CURSOR 2. オススメします。,
, Japanese translations of Tanel Poder’s posts and articles, Snapper v3.61 released ? ②手動で取得 By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away. なります。その際に、現行の実行計画がどのオペレーションにおいて、どのWHERE条件に フィルタ述語は、Predicate Informationではfilter(…)と記載され、 ブログを報告する, OLAP(分析)関数について -- OLAP(分析)関数はSELECT句で並び替え. and more work is in progress! 当然、インスタンスレベルで影響がありますので実施するときは注意をしてください。, 共有プールには、ディクショナリ、共有SQL、共有PL/SQLなどがキャッシュされています。, キャッシュがクリアされたどうかは、共有カーソルの数を前後で比較することで分かります。, 以下のSQLで特定のカーソルだけをキャッシュを削除することができます。1つ目の引数はv$sqlの[ADDRESS]と[HASH_VALUE]をカンマを挟んで結合します。, バッファキャシュとは、ディスクから読み込んだブロックのキャッシュであり、またディスクに書き込むときのバッファでもあります。, V$BHのカウントを取るとバッファキャシュの数が分かりますので前後で比較してみると変化がわかります。, 減っていることがわかりますね。ちなみに、クリアしても様々なプロセスがSQLを実行していますので、すぐに増加していきます。, 通常、データベースにログインするにはユーザとパスワードで認証します。OS認証では、OSにログインできたことでデータベースでのログインをパスできる機能です。, インスタンスを跨いでも一意性を担保しようとするためパフォーマンス劣化がありました。こういった問題を解決するためにシーケンスの発番体系にシーケンスとセッションを加えてパフォーマンスの向上を行ったものです。, 不可視に設定されたカラムは、select * from で検索しても表示されません。しかし具体的にカラムを指定(select name,id from )すると表示できます。つまり見たいときは表示され、見たくないときは非表示になるわけです。これだけ, 検索前にOSコマンドを実行することができます。この機能を利用すると圧縮されたファイルも外部表として扱えるようになります。, アプリケーションコンテキストとは、セッションごとに情報をメモリに保持でき、切断されれば消えます。つまり、セッション固有の情報をセットできる変数です。, 一時表は、データを一時的に登録しておきたいときによく利用しますが、 プライベート一時表は、トランザクションまたはセッションの終了時に自動的に削除される一時データベース・オブジェクト。, […] テスト中の場合は、こちらの記事で対象のカーソルを共有プールから削除しておくとよいです。これをしておかないと過去の処理の実績と混じってしまいます。 […], ソフトウェアベンダーでITコンサルタントとして働いています。製造業のお客様を中心に、業務アプリケーションのデリバリーを担当しています。これまでの経験をフィードバックしていきます。, 当サイトのすべてのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、情報が古くなったりすることもあります。