sql_traceパラメータの設定

Oracle ノウハウ 反省会企画

昔々、このブログをリニューアルする前は、
リニューアルしたんだから当然なんですけど、違うタイトルでブログを書いてました。
そこにも、Oracle関連の情報を書いてたんですね。
# もううろ覚えだけど、たぶん Oracle Master Silver か Gold 受験のために、
# 自分でまとめたものだったと思う。


で、そこに引っかかったと思われる検索痕が「リンク元」に残ってるんですね。
元ページはもう無くなって、ただのPerfumeバカのブログに成り下がっているというのに・・・。
来た人は「このアイドルオタクめ！」とがっかりしたことでしょう。ごめんなさい。


ということで、罪滅ぼしではないですが、
どういう検索でここに来たかをリンク元アドレスから確認し、
その人が求めていたであろう情報をまとめていこうと思います。


第１弾は「sql_trace」の設定について。




初期化パラメータ「sql_trace」は下記のようなパラメータです。


Oracle Database リファレンス 10gリリース2（10.2）
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19228-02/initparams.htm#92881

SQL_TRACEの値によって、SQLトレース機能が使用禁止または使用可能になります。
このパラメータをtrueに設定すると、パフォーマンスを改善するためのチューニングについての情報が提供されます。
この値は、DBMS_SYSTEMパッケージを使用しても変更できます。


例によって周りくどい言い方ですが、これを設定すると、SQL実行状況に関してのトレースが
出力されるようになるというわけです。


設定の方法は何種類かあります。



  • init.ora

# pfile運用の場合のみです。pfile運用かどうかは、
# SQL> show parameter spfile で確認してください。
init.oraに下記を記述します。

sql_trace=true


この場合、設定したインスタンスの全てのセッションのトレースが
取得されることになりますので、background_dump_dest が含まれるファイルシステムの容量や
CPU負荷などに注意をしなければなりません。
一般的に、本番運用中のシステムに仕掛けることはあまりありません。



  • alter system 文 で 即時反映

下記SQL文を実行します。

SQL> alter system set sql_trace=true scope=[memory|both];


この場合、設定後に生成された全てのセッションのトレースが取得されるようになります。
再起動は必要ありません。
ただし、セッションに関するパラメータはセッション生成時のものが反映されますので、
バックグラウンドプロセスなどが既に生成しているセッションなどには、反映はされません。
これらのセッション(既に生成されているセッション)に対しても sql_trace の設定を反映させたい場合は、
scope=both で設定し、再起動するか、後述の scope=spfile で設定する必要があります。


なお、scope=both で設定した場合は再起動しても設定が残ります。
scope=memory で設定した場合、再起動後は設定が残りません。


init.oraでの設定と同様に、考慮しなければならない点がおおく、
本番運用中のシステムに仕掛けることはあまりありません。



  • alter system 文 で 再起動後反映

下記SQL文を実行します。

SQL> alter system set sql_trace=true scope=spfile;


再起動後に設定が有効になり、全てのセッションに対してトレースが取得されることになります。
こちらも、init.oraでの設定と同様に、考慮しなければならない点がおおく、
本番運用中のシステムに仕掛けることはあまりありません。



  • alter session 文 で現行sessionにのみ即時反映

トレースを取得したいセッションで下記SQL文を実行します。

SQL> alter session set sql_trace=true;


実行したセッションのトレースが取得されます。
SQLチューニングなどの際には最も一般的な手法でしょう。


なお、マニュアルを確認すると、下記のような記述があります。


Oracle Database リファレンス 10gリリース2（10.2）
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19228-02/initparams.htm#92881

SQL_TRACEは、下位互換性のためにのみ残されています。


よって、今後は、DBMS_MONITORを使ったほうがいいのかもしれません。


Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 10g リリース2（10.2）
60 DBMS_MONITOR
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-01/d_monitor.htm#i1003993


ちなみに、DBMS_MONITORを使うなら、今回ここで紹介したことは、
DBMS_MONITOR.SESSION_TRACE_ENABLE
DBMS_MONITOR.DATABASE_TRACE_ENABLE
で代替可能かと思います。
こっちだったら、トレースレベルも変更できるので、いい感じです。
詳しくはまた今度。ということで、この話はこの辺で。
# 2008/05/15 追記
# DBMS_MONITORについてエントリしました。
# SQL Trace を取得する方法 - DBMS_MONITOR
# http://d.hatena.ne.jp/nijimass/20080515/1210863594


明日は東京でのPlatinum Clubのイベントに行ってきます。
では。