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.をベースにしました。 取得に失敗します。, どうすれば取得できるかというと、「child number 1」であれば、DBMS_XPLAN.DISPLAY_CURSORプロシージャの引数に"1"を指定するだけです。 statistics_levelがALLでないと取得できません。そのため、sessionレベルでstatistics_levelを以下のように設定します。 | 1 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 | AUTOTRACE(SQL*Plus) が、DBMS_XPLAN.DISPLAY_CURSORが使えれば十分なケースが多いと思います。 DBMS_XPLAN.DISPLAY_CURSORは、v$sql_planから実行計画を取得することができるファンクションです。ただし、共有プールから実行計画が削除されている場合は取得することができません。 以降、O… 対応したデータの絞込みがなされているかを理解することが重要になります。, ### フィルタ述語とアクセス述語の違い 前回の投稿から間が空いてしまいましたが、今回はOracle Databaseの「実行計画」についてお話ししたいと思います。例のとおり、今回もわかりやすさ追求のため、詳細を省略しているところがある点ご了承願います。 ただし、コマンド実行後は、実行中の SQL の再解析が実行されて一時的に遅くなる可能性があります。また、シーケンスオブジェクトのキャッシュもクリアされるので欠番が発生します。 データベース・バッファ・キャッシュのクリア. ^^^^^^^^^^, 今回実行したSQL(から不要なヒントを除いて整形したもの)は、上記のとおりですが、 実行計画の解析方法(1) 本ページでは結合を含まない単一テーブルに対しての実行計画の解析方法を記載します。 関連ページ:実行計画の解析方法(2) 実行計画を読む上での前提知識 ・実行計画は基本的にはインデントの深いものから順番に実行される。 当然、インスタンスレベルで影響がありますので実施するときは注意をしてください。, 共有プールには、ディクショナリ、共有SQL、共有PL/SQLなどがキャッシュされています。, キャッシュがクリアされたどうかは、共有カーソルの数を前後で比較することで分かります。, 以下のSQLで特定のカーソルだけをキャッシュを削除することができます。1つ目の引数はv$sqlの[ADDRESS]と[HASH_VALUE]をカンマを挟んで結合します。, バッファキャシュとは、ディスクから読み込んだブロックのキャッシュであり、またディスクに書き込むときのバッファでもあります。, V$BHのカウントを取るとバッファキャシュの数が分かりますので前後で比較してみると変化がわかります。, 減っていることがわかりますね。ちなみに、クリアしても様々なプロセスがSQLを実行していますので、すぐに増加していきます。, 通常、データベースにログインするにはユーザとパスワードで認証します。OS認証では、OSにログインできたことでデータベースでのログインをパスできる機能です。, インスタンスを跨いでも一意性を担保しようとするためパフォーマンス劣化がありました。こういった問題を解決するためにシーケンスの発番体系にシーケンスとセッションを加えてパフォーマンスの向上を行ったものです。, 不可視に設定されたカラムは、select * from で検索しても表示されません。しかし具体的にカラムを指定(select name,id from )すると表示できます。つまり見たいときは表示され、見たくないときは非表示になるわけです。これだけ, 検索前にOSコマンドを実行することができます。この機能を利用すると圧縮されたファイルも外部表として扱えるようになります。, アプリケーションコンテキストとは、セッションごとに情報をメモリに保持でき、切断されれば消えます。つまり、セッション固有の情報をセットできる変数です。, 一時表は、データを一時的に登録しておきたいときによく利用しますが、 プライベート一時表は、トランザクションまたはセッションの終了時に自動的に削除される一時データベース・オブジェクト。, […] テスト中の場合は、こちらの記事で対象のカーソルを共有プールから削除しておくとよいです。これをしておかないと過去の処理の実績と混じってしまいます。 […], ソフトウェアベンダーでITコンサルタントとして働いています。製造業のお客様を中心に、業務アプリケーションのデリバリーを担当しています。これまでの経験をフィードバックしていきます。, 当サイトのすべてのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、情報が古くなったりすることもあります。  実行計画を作る都度サンプリングするためシステムのパフォーマンスに影響する一方で、 付与したヒント句が本当に反映されたかどうかを確認できます。, 開発環境(性能試験以外)なら、statistics_levelをALLにして、formatを"ALLSTATS LAST ADVANCED"しておけば良いかと思います。 基本的に仕事外での自分用のメモ(興味があること)として記事を書いています。. (※3)引用・参考資料の2.に説明のとおり、共有プール上にある実行計画をキャッシュアウトさせる前提で、統計情報の再収集後にOracle Databaseは新しい統計情報をもとに実行計画を作り直します。, 2.門外不出のOracle現場ワザ 第4章 Oracleデータベースの頭脳 「オプティマイザ」徹底研究. もう一度、下のSQLをもとに、例として考えてみます。 実行されている処理の概要がわかったと思います。, しかし、これだけの情報では、実行計画と実行されたSQLのWHERE条件を対応づけて理解することは SQLで実行中のプログラムの進捗状況を調べる方法 | Tech. DBMS_XPLAN.DISPLAY_CURSOR 2. フィルタ述語 filter(“PA”.”PID”=1) の条件は、SQLのWHERE条件に記載された データにアクセスしたことを示します。 pa.pid = 1 に対応していることがわかります。, アクセス述語は、Predicate Informationではaccess(…)と記載され、 SQLで実行中のプログラムの進捗状況を調べる方法 | Tech. 必ずしも正確性・信頼性等を保証するものではありません。 Oracleで実行計画を取得する方法は、以下のように多く存在します。(他にもありますが) 1. Why not register and get more from Qiita? 行われているかをはっきりと理解することができます。, 効率的なSQL実行の鉄則は、初期段階においてデータをできるだけ絞り込むことですので、 また、"LAST"をつけて実行計画を取得しましたが、バインド変数の値は毎回保存されるわけではないため、前回の情報が表示されないことに注意が必要です。, v$sql_bind_captureビューでも同様にバインド変数の値を取得することができます。, ヒントはコメントであるため記述に誤りがあってもエラーにはなりません。そのため、逆にヒントが正しく書けて適用されているかどうかも確認するのが難しいです。, Oracle19cはヒントレポートを出力できるようになり、ヒントが実行計画で使用されているかどうか、エラーならその理由を確認することができます。, 以下のようにHint Reportの項目が出力されます。 "E-"はOracleによって推定されたデータで、"A-"は実際の実行結果になります。BuffersはバッファからGetしたデータブロック数です。, statistics_levelを使用せずに、GATHER_PLAN_STATISTICSヒントをつけても同様の結果を表示できます。, format=>'ALL'よりもOutline Dataが増える。 を理解するために非常に有効な情報です。, このため、実行計画を確認する際は、フィルタ述語とアクセス述語が出力される 対象データを抜粋(フィルタ)する処理が実行されたことを示します。, 上記の実行計画では、Id=2 TABLE ACCESS FULL において —————————————————, 2 – filter(“PA”.”PID”=1) アクセス述語を使用する索引アクセスで行うことが一般に推奨されます。, ### SQLトレースの実行計画ではフィルタ述語とアクセス述語が表示されないことに注意, 理由は不明ですが、SQLトレースの実行計画にはフィルタ述語とアクセス述語が表示されません。 EXPLAIN PLAN 4. 「Peeked Binds」の部分でバインド変数の値を確認できます。 しばちょう先生の試して納得!DBAへの道 indexページ みなさん、こんにちは。"しばちょう"こと柴田長(しばた つかさ)です。3月はお客様のシステムのデータベースにおいてSQLの性能テストに没頭してい … 行頭の番号に対応するオペレーションの実行時に、表示された述語を使用して LEADINGとUSE_NLが使用されていることが確認できます。, DBMS_XPLAN.DISPLAY_CURSORのformatに"ALL", "HINT_REPORT", "HINT_REPORT_USED"を指定した場合に表示されます。 適用されます。アクセスした後にデータを絞り込む形になるため、 今回は共有プールとバッファキャシュのクリアについて記事にしたいと思います。 SQLトレース 3. ch.pid = pa.pid and pa.pid = 1 を数学的/算術的に変換したものであることが 万が一、データが意図したように絞り込めていない場合は実行計画を再検討することに (※2)DBバッファキャッシュ、REDOログバッファ、UNDOデータが登場するとともに、COMMITの処理も加わります。 ことに相当します。, SELECT cid, cname, pa.pid, pname FROM ch, pa WHERE ch.pid = pa.pid and pa.pid = 1 | 3 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 20080 | 11 (0)| 00:00:01 | 内部的には、ヒントに基づいて実行計画を変更するので、Oracle Database 12cR2(Oracle12cR2)からの手動作成により、SQLチューニングなどでも使用できるようになりました。 (1)手動作成 まずは、SQLパッチの手動作成について簡単に説明します。 —————————————————————————————–, Predicate Information (identified by operation id): ←★ わかります。(推移律 : pa.pid=1であれば、ch.pid = pa.pidは ch.pid=1となる), このように、フィルタ条件、アクセス条件を確認すると、 formatに指定できる情報はたくさんあるのですが、よく利用する指定方法だけ以降に記載します。, 次に、"ALL ALLSTATS LAST"を指定した場合です。 Oracleで実行計画を取得する方法は、以下のように多く存在します。(他にもありますが), が、DBMS_XPLAN.DISPLAY_CURSORが使えれば十分なケースが多いと思います。 ②手動で取得 指定しなくても、使用されないヒントがある場合は自動で表示されます。, SIer&バックエンドエンジニア&日曜プログラマー。 索引をTraverseする(索引のroot → branch → leafとたどる)ときの索引列の検索条件に対応します。, 上記の実行計画では、Id=4 INDEX RANGE SCAN において Predicate Informationセクションに表示されます。, —————————————————————————————– Oracle Database 11g以前では最適な実行計画が選択されなかった状況でも、12cではより良い実行計画を検出し、SQL実行時のパフォーマンス低下を回避することができます。 Advanced Oracle Troubleshooting Guide, Part 4 : 長時間パース問題の診断; Advanced Oracle Troubleshooting Guide, Part 5:WaitProfで本当に素早くV$ビューをサンプリング。SQLで! Advanced Oracle Troubleshooting Guide, Part 6:os_explainを使用してOracleの実行計画を理解する WHERE ch.pid = pa.pid and pa.pid = 1 引数を指定しないと全部表示してくれれば良いのですが、そうはなっていないようです。, child numberの取得方法は色々ありますが、v$sqlでSQL_IDを調べたのであれば、CHILD_NUMBERカラムの値を見るのが一番簡単です。, DBMS_XPLAN.DISPLAY_CURSORの引数formatにより、出力する情報を設定することができます。 and more work is in progress! 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. Help us understand the problem. ブログを報告する, OLAP(分析)関数について -- OLAP(分析)関数はSELECT句で並び替え. のSELECT文を実行しているわけですが、 ディクショナリキャッシュ 上記のとおり、フィルタ述語とアクセス述語は実行計画内の各オペレーションの処理内容  収集する日、時間帯を変更することも可能。 オススメします。,
, Japanese translations of Tanel Poder’s posts and articles, Snapper v3.61 released ? アクセス述語に合致したデータにのみ選択的にアクセスします。 フィルタ述語は、Predicate Informationではfilter(…)と記載され、 実行計画とは、オプティマイザがSQLをどの様に処理するか導き出した結果といったところです。SQLが遅い場合は、この実行計画を見てやると問題点が見えてきます。, 実行計画の取得方法を4つのシチュエーションに分けました。1.単体テスト、2.システムテスト、3.本番稼働、4.処理中, 単体テストでは、データ量も質も本番とは異なります。しがたってあくまで机上レベルで狙った実行計画になるか確認するくらいだと思います。, この場合はSQLDeveloperを利用した方法が便利です。確認したいSQLを[ワークシート]にコピーして[実行計画]ボタンをクリックすると実行計画が表示されます。, SQLにバインド変数が含まれていても実行計画は表示されます。ただしバインド変数によって選択される実行計画が変わること多々ありますので注意です。, テスト中に遅いと分かった場合は、問題のプログラムが特定されています。またテスト環境なのでトレースを取得する方法が適しています。, トレースは、特定のセッションから実行された全SQLの実行計画を取得できます。また同じSQLが要した処理時間などがサマリされることで問題箇所を把握しやすいです。, 本番稼働中の環境では、トレースを簡単には取得できないと思います。また何のプログラムが原因なのかもはっきりとしない場合があります。, この場合は、AWRレポートを使用して遅いSQLを特定して、実行計画を確認する方法がてきしています。またこの方法も実際に選択された実行計画です。, ただし、AWRがスナップショットとして記録したものしか確認できません。つまり遅くないSQLはAWRに残らないので見れません。またスナップショットは定期的に保存されるものですので、今この瞬間を見ることはできません。, 遅いプログラムが今まさに動ている場合は、v$sqlからSQLを特定して統計情報を習得する方法があります。この場合も実際に選択された実行計画です。, ただし、共有プールに残っている場合しか確認できません。時間がたつとageoutしますので古い情報は見れません。, 実行計画の取得方法を表にまとめてみました。これを参考に適切な方法で実行計画を確認しましょう。, AWRレポートを読み解くには、高度な知識が要求されます。その上、情報の読み方が詳しくは公開されていません。今回は、データベースにある程度負荷をかけたサンプルを見ながら内容を確認していきます。, 今回は、ログスイッチが多発したケースについてAWRレポートを確認していきたいと思います。, 今回は生トレースと成形後のトレースの両方を見ていきたいと思います。生トレースをじっくりと見ることはあまりないと思いますが、生トレースでないと見えないものもあります。, ソフトウェアベンダーでITコンサルタントとして働いています。製造業のお客様を中心に、業務アプリケーションのデリバリーを担当しています。これまでの経験をフィードバックしていきます。, 当サイトのすべてのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、情報が古くなったりすることもあります。 My hobby is creating small programs almost every weekend although I'm always busy weekdays. 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」になっています。 Help us understand the problem. 実行計画をキャッシュしつつ、不適切な実行計画を使うことをできる限り避けるには、Oracle の逆の考え方にするのがベストと私は考えています。 そうならなかったのは、前回書いたような当たり前のコーディング(私は20年以上前からそうしている)が、想定されていないからです。 – 日本語訳, full table scan実行時にオプティマイザ統計駆動でdirect path read実行を決定する(_direct_read_decision_statistics_driven), SQL計画安定性のための CURSOR_SHARING = FORCEおよびFORCE_MATCHING_SIGNATUREの限界, lazyなOracleユーザーのためのヒント – タイプ数を削減するANSI DATEおよびTIMESTAMP SQL構文, poor-manスタック プロファイラでCPU使用率が高い事象をトラブルシューティング – ワンライナーで!, ALTER SESSION FORCE PARALLEL QUERYは、実際に何かを強制するものではありません, asqlmon.sql : SQL監視のような実行計画の行レベルのSQLレスポンス時間へのドリルダウン, quick'n'dirtyトラブルシューティングのために/procファイルシステムを使用してLinuxカーネルランドを覗く, Advanced Oracle Troubleshooting Guide [PART 1] :待機インタフェースが十分でない場合, Advanced Oracle Troubleshooting Guide, Part 2 : 魔法は必要ありません、体系的なアプローチで成しえるものです, Advanced Oracle Troubleshooting Guide, Part 3: プロセス?スタックをさらに冒険する, Advanced Oracle Troubleshooting Guide, Part 4 : 長時間パース問題の診断, Advanced Oracle Troubleshooting Guide, Part 5:WaitProfで本当に素早くV$ビューをサンプリング。SQLで!, Advanced Oracle Troubleshooting Guide, Part 6:os_explainを使用してOracleの実行計画を理解する, Advanced Oracle Troubleshooting Guide, Part 7:LatchProfを使用してラッチホルダの統計情報をサンプリング, Advanced Oracle Troubleshooting Guide, Part 8:LatchProfXを使用した、より詳細なラッチのトラブルシューティング, Advanced Oracle Troubleshooting Guide, Part 9 – OStackProfを使用してSQL*Plusからプロセススタックプロファイリング, Advanced Oracle Troubleshooting Guide – Part 10: インデックスユニークスキャンがマルチブロック読み込み?, Advanced Oracle Troubleshooting Guide – Part 11 :ash_wait_chains.sqlを用いた複雑な待機チェーンのシグネチャ分析, ash_wait_chains.sqlスクリプト(v0.2)でbuffer busy waitsを診断する, JPOUG Tech Talk Night #4 やりますよ! 今回は LTと12c座談会です, Oracle OpenWorld Unconference presented by JPOUG, オペレーションの一覧と最低限覚えておくべきオペレーション (実行計画の読み方#2).  「動的サンプリング」と呼ばれている。 ご使用のバージョンが Oracle Database 10.2.0.4 の場合のみ、当機能を使用する前にまず事前準備として下記 event をパラメータ・ファイルに設定してからインスタンスを再起動する必要があります。 event="5614566 trace name context forever" フラッシュ手順は以下の通りです。

.

フリード リバース連動ドアミラー Gb5, Teams いいね 誰が, Teams カメラオフ デフォルト, エクセル 一括削除 できない, 都営三田線 時刻表 大手町, アシックス 契約選手 サッカー, マイン クラフト 全 実績, 沖縄 アミークス うわさ, エプソン ノズルチェック 黒だけ出ない, 住民税 6月から なぜ, 5リットル ゴミ袋 黒, イラレ 線 抜く, 大阪上空 ヘリコプター 今日, Aomei Backupper Pro ライセンスコード, ミニベロ シートポスト 曲がる, 岩国空港 飛行機 運航状況, Git Clone --sparse, Gap キッズ サイズ感, ウエスティ ブリーダー 九州, アクオス 外付けhdd 価格, パワーポイント 印刷 つぶれる, 奥二重 韓国人 女優, ヨルシカ 昼鳶 歌詞, Matlab Mファイル 呼び出し, 足立区 保育園 申し込み, 人から評価 され る のが 嫌い, 心電図 再検査 中学生, お絵描き お題 サイト,
oracle 実行計画 クリア 4 2020