本文書に関する指摘については、原文著者のTanel Poderさんではなく、渡部まで御連絡お願いいたします。
注意事項
- 自動翻訳に手を加えたレベルの翻訳と理解してください
- 意味をとりやすくするために追加した箇所については、《…》で囲っています。
- 訳がわからない、または、自信がない箇所は★をつけています。
日本語訳：渡部 亮太 (WR at Csus4 dot net)
履歴
- 2013-04-09: 初稿up
asqlmon.sql : SQL監視のような実行計画の行レベルのSQLレスポンス時間へのドリルダウン
ブログの記事のために割ける多く時間がない。だから、私のasqlmon.sqlスクリプトの出力例を貼り付けます。このスクリプトは、実行計画の中で、応答時間が費やされている箇所を表示するために、ASHのsql_plan_line列を使用しています。Oracleに組み込まれているSQL監視レポートをなぜ使用しないのか？まあ、SQL監視は、非常に頻繁に実行されることはない、"実行時間の長い"クエリを意図してしているます。言い換えれば、頻繁に実行されるOLTPスタイルのSQLにドリルダウンするために、SQL監視を使用することはできません。Oracle-Lメーリングリストへの私の最近の記事をここにコピーします：
従来からあるGATHER_PLAN_STATISTICS / STATISTICS_LEVEL = ALL の主なパフォーマンスへの影響は、コストの高い時刻関連のシステムコール（gettimeofday（））が行ソースのA-timeを取得するために使用されという事実に端を発しています。
これが、_rowsource_statistics_sampfreqが導入された理由です。行ソース間のすべてのスイッチにおいて時刻を取得する必要があるのではなく、（デフォルトでは）128回《を数える》毎に《時刻を取得します》。これにより、すべての子《の行ソース》を合わせたよりも親の行ソースが短い時間使用されるような、いくつかの興味深い（またはむしろ推定に近い）測定エラーが発生していました。（ – 昔からSolarisおよびRHEL 5.4以降の例 – ちなみに、ご使用のプラットフォームと新しさに依存しますが、多少オーバーヘッドが削減された、軽量の "高速トラップ"システムコールが、現在のタイムスタンプを取得するために使用されているかもしれません）。
とにかく、SQL監視における行ソースのタイミング情報は、ASH sql_plan_lineサンプルから来ている。このため、この仕組み- SQL監視はちょうどこれらを記録するためにASHを照会します – は常に有効になっている。
V$SQL_MONITORビュー内の他のデータは膨大なオーバーヘッドが発生することはありません – それはあなたのクエリに依存しますが、（私のlotslios.sqlを使用した） 負荷の高い論理I/Oを伴うネステッドループは、NO_MONITORヒントを使用して実行する場合に比べて、MONITORヒントを使用した場合で約0.6%多くのCPUを消費していました。私はVM上でそれをテストしており、数回実行しただけなので、この結果は単なる統計的なエラーまたは他のいくつかのバックグラウンドアクティビティが原因であるかもしれません。言い換えれば、重要なオーバーヘッドではないということです。
さて、これは、実行時間の長いクエリについての話です。
SQL監視データは、共有プール?メモリー構造に保管し、更新しなければならないので、ヒントを使用してすべての短い（OLTP）のクエリについてSQL監視を有効にしないでください – そして、もし、あなたの1000個のセッションすべてがすべてのSQL実行について監視データを突然開始すると、Real-time plan statistics latch競合（と、過去のSQL実行の監視データを頻繁に検索し、パージする処理による、幾分かのCPUオーバーヘッド） を待機することになるでしょう。
asqlmon（ASHベースのレポートのようなSQL監視）は、対応する実行計画がライブラリ?キャッシュにまだ存在することを前提とし、それをパラメータとしてsql_idとchild_numberを取ります。SQL監視レポートがするような、ある特定のSQL実行について表示するのではなく、ASHデータでキャプチャして、すべてのSQL実行、および、子カーソルに対して計画行レベルの内訳を表示します（測定範囲は、WHERE句を変更することで変更できます）：
SQL> @ash/asqlmon 6c45rgjx1myt6 0 SECONDS Activity Visual Line ID Parent ASQLMON_OPERATION SESSION EVENT OBJ_ALIAS_QBC_NAME ASQLMON_PREDICATES PROJECTION \---------- -------- ------------ ------- ------ ---------------------------------------------------------------------------------------------------- ------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- % | | 0 SELECT STATEMENT 5 10.4 % |# | 1 0 SORT AGGREGATE ON CPU [SEL$1] (#keys=0) COUNT(*)[22] 3 6.3 % |# | 2 1 COUNT STOPKEY ON CPU [F:]ROWNUM<=1000000000 % | | 3 2 MERGE JOIN CARTESIAN % | | 4 3 VIEW [DBA_SOURCE] DBA_SOURCE@SEL$1 [SET$1] % | | 5 4 UNION-ALL [SET$1] % | | 6 5 FILTER [SEL$335DD26A] [F:](("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))) % | | 7 6 HASH JOIN [A:] "O"."OBJ#"="S"."OBJ#" (#keys=1) "O"."OBJ#"[NUMBER,22], "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."TYPE#"[NUMBER,22] % | | 8 7 HASH JOIN [A:] "O"."SPARE3"="U"."USER#" (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."TYPE#"[NUMBER,22] % | | 9 8 INDEX FULL SCAN [I_USER2] U@SEL$2 [SEL$335DD26A] "U"."USER#"[NUMBER,22] % | | 10 8 HASH JOIN [A:] "O"."OWNER#"="U"."USER#" (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22] % | | 11 10 INDEX FULL SCAN [I_USER2] U@SEL$3 [SEL$335DD26A] "U"."USER#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22] 1 2.1 % | | 12 10 INDEX FAST FULL SCAN [I_OBJ2] ON CPU O@SEL$3 [SEL$335DD26A] [F:](("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#")) "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22] % | | 13 7 INDEX FAST FULL SCAN [I_SOURCE1] S@SEL$2 [SEL$335DD26A] "S"."OBJ#"[NUMBER,22] % | | 14 6 NESTED LOOPS [SEL$5] % | | 15 14 INDEX SKIP SCAN [I_USER2] U2@SEL$5 [SEL$5] [A:] "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) "U2"."USER#"[NUMBER,22] [F:]("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) % | | 16 14 INDEX RANGE SCAN [I_OBJ4] O2@SEL$5 [SEL$5] [A:] "O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#" % | | 17 5 NESTED LOOPS [SEL$68B588A0] % | | 18 17 NESTED LOOPS "O"."SPARE3"[NUMBER,22] % | | 19 18 NESTED LOOPS "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22] % | | 20 19 FIXED TABLE FULL [X$JOXSCD] S@SEL$6 [SEL$68B588A0] "S"."JOXFTOBN"[NUMBER,22] % | | 21 19 TABLE ACCESS BY INDEX ROWID [OBJ$] O@SEL$7 [SEL$68B588A0] "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22] % | | 22 21 INDEX RANGE SCAN [I_OBJ1] O@SEL$7 [SEL$68B588A0] [A:] "O"."OBJ#"="S"."JOXFTOBN" AND "O"."TYPE#"=28 [F:]"O"."TYPE#"=28 "O".ROWID[ROWID,10], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22] % | | 23 18 INDEX RANGE SCAN [I_USER2] U@SEL$7 [SEL$68B588A0] [A:] "O"."OWNER#"="U"."USER#" [F:](("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0) % | | 24 17 INDEX RANGE SCAN [I_USER2] U@SEL$6 [SEL$68B588A0] [A:] "O"."SPARE3"="U"."USER#" 39 81.3 % |######## | 25 3 BUFFER SORT ON CPU (#keys=0)% | | 26 25 VIEW [DBA_OBJECTS] DBA_OBJECTS@SEL$1 [SET$2] % | | 27 26 UNION-ALL [SET$2] % | | 28 27 FILTER [SEL$18BE6699] [F:](("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))) % | | 29 28 HASH JOIN [A:] "O"."SPARE3"="U"."USER#" (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."TYPE#"[NUMBER,22] % | | 30 29 INDEX FULL SCAN [I_USER2] U@SEL$10 [SEL$18BE6699] "U"."USER#"[NUMBER,22] % | | 31 29 HASH JOIN [A:] "O"."OWNER#"="U"."USER#" (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22] % | | 32 31 INDEX FULL SCAN [I_USER2] U@SEL$12 [SEL$18BE6699] "U"."USER#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22] % | | 33 31 TABLE ACCESS FULL [OBJ$] O@SEL$12 [SEL$18BE6699] [F:]("O"."TYPE#"<>10 AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22] "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL) % | | 34 28 NESTED LOOPS [SEL$14] % | | 35 34 INDEX SKIP SCAN [I_USER2] U2@SEL$14 [SEL$14] [A:] "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) "U2"."USER#"[NUMBER,22] [F:]("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) % | | 36 34 INDEX RANGE SCAN [I_OBJ4] O2@SEL$14 [SEL$14] [A:] "O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#" % | | 37 27 NESTED LOOPS [SEL$15] % | | 38 37 INDEX FULL SCAN [I_LINK1] L@SEL$15 [SEL$15] "L"."OWNER#"[NUMBER,22] % | | 39 37 INDEX RANGE SCAN [I_USER2] U@SEL$15 [SEL$15] [A:] "L"."OWNER#"="U"."USER#" 40 rows selected.
出力は、意図的に非常に広範です – 私が、実行計画の行と、それに関連する情報を、視覚的に同じテキスト行に配置することが好きであるためです。私は、多くの時間が費やされた場所（Activity % とVisual列）を視覚的に識別して、そのような実行計画をnavigate aroundし、そして、端末ウィンドウの行全体をハイライト（それがマーカーのようになります）するために、行をダブルクリックしたり、トリプルクリックしたり、フィルタ条件と投影などのような、詳細情報を参照するために、左から右スクロールしたりします。
このスクリプトはあなたに、A-rows、StartsのようにSQL監視レポート（または昔ながらのSQL計画プロファイリング）が提供してくれる情報のすべてを与えるものではありませんので注意してください。これは、ASHが単にこの範囲のデータを提供していないためです。しかし、このアプローチは、あなたのSQLが多くの時間を費やしている場所の概要を素早く特定するために、頻繁に実行されるOLTPスタイルのクエリで動作し、セッションの何らかの設定を有効したり、ヒントを追加する必要がないことを、再び《言っておきます》。
追記：私はRAC対応で GV$-を使用するgasqlmon.sqlスクリプトも持っている。私はしばらくの間、最新の状況に追随していないので、時々誤った出力を返すことがあります。
（いくつかの注目すべき課題がある。注目しているchild#が、すべてのインスタンスで同じにならない可能性がある。簡単のため、私は一度に1つのインスタンスを見ている。 – 非パラレルSQLチューニングでは、1つのスクリプトでクラスタ全体の概要を得る必要がほとんどない）。