DBMS_PROFILER: PL/SQL Performance Tuning
今日は久々にDBAsupport.comより、DBMS_PROFILERに関する記事を ご紹介します。 DBMS_PROFILERを使用するとPLSQLの各行の実行時間の統計を実測したり、 カバレッジを計測できたりします。 使用前の準備としてsysユーザで @?/rdbms/admin/profload.sql を実行します。 (@?はORACLE_HOMEと同じ意味です) 次に、実際に計測対象となるユーザで @?/rdbms/admin/proftab.sql を実行します。 ※10gの場合、catproc.sql内で、パッケージを作成してしまうので、 profload.sqlは必須ではありません。 profload.sqlを実行することでserver sideのprofiler packageを インストールすることができます。 ですから、10gならDBAさんにお願いしなくても 自分のユーザ内であれば、profilerを実行できるということですね。 別のユーザでも実行したい場合、全てのユーザでproftab.sqlを実行するか、 もしくは以下のように共有してもOKです。 create public synonym plsql_profiler_data for plsql_profiler_data; create public synonym plsql_profiler_units for plsql_profiler_units; create public synonym plsql_profiler_runs for plsql_profiler_runs; create public synonym plsql_profiler_runnumber for plsql_profiler_runnumber; grant insert,update,select,delete on plsql_profiler_data to public; grant insert,update,select,delete on plsql_profiler_units to public; grant insert,update,select,delete on plsql_profiler_runs to public; grant select on plsql_profiler_runnumber to public; そして実際に使用するには exec dbms_profiler.start_profiler('aaa'); -- PLSQLの実行 exec dbms_profiler.stop_profiler; だけです。もちろんaaaは任意の値ですね。 実行結果やカバレッジ表示のサンプルとしては引用先に 貼り付けてありますのでそのままご利用いただけると思います。 DBMS_PROFILERの詳細は以下URLのマニュアルで確認できます。 <Japanese> <English> ■ DBAsupport.com http://dbasupport.com/ ■ 引用ページ dbms_profiler3.shtml _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 記事本文 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ◆ DBMS_PROFILER: PL/SQL Performance Tuning DBMS_PROFILER is a very powerful tool and the first of its kind to identify performance issues on the PL/SQL front. This utility can be best used in the development stages to fine tune code based on various applicable scenarios. It can also be used to fine tune routines that are already in production and are taking noticeable time to execute. Overall, this utility gives statistics for each line of code that will help us in evaluating and tuning at a finer level. Just as SQL statements are checked for performance, PL/SQL code should not be ignored but should be tuned for optimal results as well. _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 英語の語順に近い解釈 ※自然な語順で解釈する癖をつけるために 敢えて不自然な日本語になっています。 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ◆ DBMS_PROFILER: PL/SQL Performance Tuning DBMS_PROFILER is a very powerful tool DBMS_PROFILERはとてもパワフルなツールです。 and the first of its kind そしてその種における一番手です。 to identify performance issues パフォーマンス問題を特定するための on the PL/SQL front. PL/SQLにおいて真っ先に This utility can be best used in the development stages このユーティリティは開発工程において使用されるのが最良な場合があります。 to fine tune code コードを微調整するために based on various applicable scenarios. 様々な適切なシナリオに基づいて。 It can also be used それはまた、使用される場合があります。 to fine tune routines ルーチンを微調整するために that are already in production 既に本番リリースされた(ルーチン) and are taking noticeable time to execute. そして目立って遅い(ルーチン) Overall, 全般的に this utility gives statistics このユーティリティは統計を与えます。 for each line of code 行単位で that will help us それ(ユーティリティ)は私たちを助けます。 in evaluating and tuning 評価やチューニングする際に at a finer level. 詳細レベルで Just as SQL statements are checked for performance, まさにSQL文がパフォーマンスの為にチェックされるように PL/SQL code should not be ignored PL/SQLコードも無視されるべきではありません。 but should be tuned for optimal results as well. チューニングされるべきです。最適な結果の為に。同様に。 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 英語解説 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ front ※通常の前的なニュアンスの他に、「最重要な」とか「真っ先に」とか いうニュアンスもあります。 utility ※ユーティリティとして完全に日本語になっていますが、 定義としては、他のソフトウェアやシステムに有益な 2次的なソフトウェアを指します。 (sort,copy,compressなど) fine tune ※fine tuneで微調整するという動詞になります。 applicable =suitable,appropriate,relevant routines ※日本語ではルーチンですが、英語ではru:ti:n(accentはti)ですね。 ティをチと言うと全く通じません。^^; アンチもaenti(or aentai,accentはae)だし、 マルチもm∧lti(or m∧ltai,accentはm∧)ですね。 意味としては繰り返し呼び出される共通の処理です。 in production ※以前クイズでご紹介したので覚えている方もいるかもしれませんが、 本番は英語ではproductionです。本番環境はproduction environment 本番機はproduction server noticeable ※言葉通りですが、notice-ableで気づき易い、いわゆる目立っている状態です。 statistics ※statsとも言います。統計です。 evaluating ※評価すること at a finer level. ※fineはi'm fine thank youのfineですが、 Oracle上では細かいという意味として使われることが多いです。 有名なのはfine grained access controlやfine grained auditですね。 fine grained はきめ細かいという意味です。 ちなみにfineの後ろにthank youをつけるのは日本人だけかもしれません。 Just as ※まさに(asの強調。極めて同様の状態を指す) _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ★ 英語ぷちクイズ ★ ※答えと思うリンクをぷちっとクリックしてください。 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ☆「縦列駐車」英語でなんて言いますか? how do you say -- jyu-retsu-chuusya -- in english? ◆column parking ┗ http://clickenquete.com/a/a.php?M0002066Q0017095A1d6dd ◆queued parking ┗ http://clickenquete.com/a/a.php?M0002066Q0017095A2400d ◆lined parking ┗ http://clickenquete.com/a/a.php?M0002066Q0017095A339a8 ◆parallel parking ┗ http://clickenquete.com/a/a.php?M0002066Q0017095A4ca3e ○結果を見る ┗ http://clickenquete.com/a/r.php?Q0017095Ce7cd 全然関係無いのになんとなくOracle関連っぽい選択肢ですね。^^ こたえは次回に発表します。 締切:2006年10月08日18時00分 協力:クリックアンケート http://clickenquete.com/ ■ 前回のこたえ ☆「C++」英語でなんて読みますか? ◆C Plus Plus ┗ http://clickenquete.com/a/a.php?M0002066Q0016997A184af ◆C Pla Pla ┗ http://clickenquete.com/a/a.php?M0002066Q0016997A2dc56 ◆C Double Plus ┗ http://clickenquete.com/a/a.php?M0002066Q0016997A34e9b ◆C Cross Cross ┗ http://clickenquete.com/a/a.php?M0002066Q0016997A44837 ○結果を見る ┗ http://clickenquete.com/a/r.php?Q0016997C9df0 こたえはC plus plusです。 そのまんまですね。 C pla plaは日本での呼び名ですね。 面接で I am well experienced C plus plus programmer. I have strong skill of C plus plus. とか言えばいいんですかね。 締切:2006年10月04日18時00分 協力:クリックアンケート http://clickenquete.com/ _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ Oracle解説 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ DBMS_PROFILERとは主旨が少し異なってしまいますが、 10gではADDMという便利なパフォーマンス分析機能がデフォルトで機能しており、 @?/rdbms/admin/addmrpt.sql を使用していつでも好きな時間間隔を指定して ネックとなっているSQLや原因を非常に簡単に知ることができます。 前提としてstatistics_level初期化パラメータがtypicalになっている必要があります。 例えばこんな感じでレポートが出ます。(本当はもっとたくさん出ます) ※無条件に実行できますが、Diagnostics Packライセンス購入が前提となっています。 〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・ FINDING 1: 100% impact (5803 seconds) ------------------------------------- 共有プールに関連したラッチの競合により、データベース処理時間がかなり消費されました。 NO RECOMMENDATIONS AVAILABLE ADDITIONAL INFORMATION: "latch: library cache"待機により、データベース処理時間が12%に達しました。 "latch: shared pool"待機により、データベース処理時間が164%に達しました。 SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: 待機クラス"同時実行性"により、 データベース処理時間がかなり消費されました。 (100% impact [5834 seconds]) FINDING 2: 100% impact (675 seconds) ------------------------------------ データベース処理に長時間かかるSQL文が見つかりました。 RECOMMENDATION 1: SQL Tuning, 100% benefit (9874 seconds) ACTION: SQL_ID "uyc7hj980k73d"のPL/SQLブロックを チューニングしてください。PL/SQLユーザーズ・ガイドおよ びリファレンスのPL/SQLアプリケーションのチューニングの 章を参照してください。 RATIONALE: SQL_ID "uyc7hj980k73d"が指定された SQL文は1回実行され、平均経過時間は9874秒でした。 RECOMMENDATION 2: SQL Tuning, 100% benefit (8571 seconds) ACTION: SQL_ID "aq8yqxyyb40nn"のPL/SQLブロックを チューニングしてください。 PL/SQLユーザーズ・ガイドおよ びリファレンスのPL/SQLアプリケーション のチューニングの章を参照してください。 〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・〜・ このケースではバインド変数がちゃんと使用されていないPLSQLが豪快なバッチ処理を している為に、library cacheが大変なことになっています。 該当するSQLを確認するなら、 set serveroutput on size 32767 declare w_clob clob; amt integer:=32767; w_buf varchar(32767); begin select sql_fulltext into w_clob from v$sql where sql_id='該当するSQL_ID'; dbms_lob.read(w_clob,amt,1,w_buf); dbms_output.put_line(w_buf); end; / こんな感じでしょうか。 なにもclob使う程長くないSQLなら alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'; set pages 50 set line 115 select elapsed_time,cpu_time,application_wait_time,concurrency_wait_time, user_io_wait_time,plsql_exec_time,address,hash_value,sql_text,executions, last_active_time from v$sql where sql_id='該当するsql_id'; で見れますね。 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 編集後記 _________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 今号からご購読いただいた皆様はじめまして。 本日も最後まで読んでいただきありがとうございます。 既にお気づきの方々も多いかもしれませんが、 発行を重ねるにつれ、到底3分とは程遠い ボリュームになってきています。 すみません。 暗黙の題名として(フォトリーディングなら)ほぼ3分 だと思って読んでいただければ幸いです。^^ 適当に好きなところだけ3分くらいで読んでいただければ 幸いです。 それではまた。 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ おわりに ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 記述誤りなどのご指摘、 記事に関する疑問点・質問・感想・ご意見・ご感想など yakusa_oracle@yahoo.co.jpまでお願い致します。 簡単な自己紹介はこちら http://pr2.cgiboy.com/S/3191274 バックナンバー兼ブログはこちら http://imoment.web.fc2.com/ 登録・解除はこちらから http://www.mag2.com/m/0000200441.htm