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を実行するか、
もしくは以下のように共有してもＯＫです。
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＞
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-01/d_profil.htm#sthref5502
＜English＞
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_profil.htm#sthref5502
■ DBAsupport.com
http://dbasupport.com/
■ 引用ページ
http://www.dbasupport.com/oracle/ora8/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
※ユーティリティとして完全に日本語になっていますが、
定義としては、他のソフトウェアやシステムに有益な
２次的なソフトウェアを指します。
（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
◆queued parking
◆lined parking
◆parallel parking
○結果を見る
┗ http://clickenquete.com/a/r.php?Q0017095Ce7cd
全然関係無いのになんとなくOracle関連っぽい選択肢ですね。^^
こたえは次回に発表します。
■ 前回のこたえ
☆「C++」英語でなんて読みますか？
◆C Plus Plus
◆C Pla Pla
◆C Double Plus
◆C Cross Cross
○結果を見る
┗ 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.
とか言えばいいんですかね。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
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';
で見れますね。
ご紹介します。
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を実行するか、
もしくは以下のように共有してもＯＫです。
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＞
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-01/d_profil.htm#sthref5502
＜English＞
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_profil.htm#sthref5502
■ DBAsupport.com
http://dbasupport.com/
■ 引用ページ
http://www.dbasupport.com/oracle/ora8/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
※ユーティリティとして完全に日本語になっていますが、
定義としては、他のソフトウェアやシステムに有益な
２次的なソフトウェアを指します。
（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
◆queued parking
◆lined parking
◆parallel parking
○結果を見る
┗ http://clickenquete.com/a/r.php?Q0017095Ce7cd
全然関係無いのになんとなくOracle関連っぽい選択肢ですね。^^
こたえは次回に発表します。
■ 前回のこたえ
☆「C++」英語でなんて読みますか？
◆C Plus Plus
◆C Pla Pla
◆C Double Plus
◆C Cross Cross
○結果を見る
┗ 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.
とか言えばいいんですかね。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
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';
で見れますね。
コメントを投稿