SQL_TRACE診断

SQL_TRACEツールは主にSQLトラッキングに使用され、DBAでよく使われる診断ツールです.
SQL_TRACEは、グローバル起動に設定してもよいし、あるセッションのみを追跡してもよい.
 
グローバル有効化
pfile/spfileにsql_を指定するtrace=true
データベースの起動後に変更することもできます.

alter system set sql_trace=true scope=both;

現在のセッションの有効化

alter session set sql_trace=true scope=both;

 
しかし、実際のメンテナンスプロセスでは、常に他のユーザーのプロセスを追跡する必要があります.OracleではDBMSを提供しています.SYSTEM.SET_SQL_TRACE_IN_SESSIONシステムパッケージはこのタスクを完了します.

SQL> desc dbms_system
PROCEDURE SET_SQL_TRACE_IN_SESSION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SID                            NUMBER                  IN
 SERIAL#                        NUMBER                  IN
 SQL_TRACE                      BOOLEAN                 IN

v$sessionでSID,SERIAL#情報を取得できます

SQL> col username for a15
SQL> select sid,serial#,username from v$session where username is not null;
       SID    SERIAL# USERNAME
---------- ---------- ---------------
         6        579 SYS
        66          4 SYS
        96          5 SCOTT
        98         27 SYS
       222         85 SYS
         ：
SQL> exec dbms_system.set_sql_trace_in_session(96,5,true)

10046イベントの説明:
10046イベントはOracleが提供する内部イベントであり、SQL_です.TRACEの強化.
10046イベントでは、次の4つのレベルを設定できます.
レベル1標準のSQL_を有効にするTRACE機能.
level 4はlevel 1にバインド値(bind value)を加算する
level 8はlevel 1に加えてイベント追跡を待つ
level 12 level 1 + level 4 + level 8
 
グローバル設定
同様にpfile/spfileに構成パラメータを追加
event="10046 trace name context forever,level 12"
セッションレベルの設定

alter session set events'10046 trace name context forever,level 12';

トレースを閉じる

alter session set events‘10046 trace name context off’;

他のユーザー・セッションの設定

SQL> desc dbms_system
PROCEDURE SET_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI                             BINARY_INTEGER          IN
 SE                             BINARY_INTEGER          IN
 EV                             BINARY_INTEGER          IN
 LE                             BINARY_INTEGER          IN
 NM                             VARCHAR2                IN

ここでパラメータSI,SEはSIDとSERIALを表し,V$SESSIONから,EVはEVENTSイベント番号,LEはLEVEL,NMはユーザ名を表す.

exec dbms_system.set_ev(222,85,10046,12,'SYS')

現在のセッション設定のトレースレベルを表示するには、次の手順に従います.

set feedback off
set serveroutput on
declare
  event_level number;
  event_number number;
begin
  for event_number in 10000 .. 10999 loop
    sys.dbms_system.read_ev(event_number, event_level);
    if (event_level > 0) then
      sys.dbms_ouput.put_line('Event' || to_char(event_number) ||
                              'is set to level' || to_char(event_level));
    end if;
  end loop;
end;

イベント情報を取得した後、どのように表示しますか?11 G以降のOracleでは、新しい監査証跡ファイルを導入します.

SQL> show parameter diagnostic_dest
NAME              TYPE           VALUE
----------------- -------------- ----------------------
diagnostic_dest   string         /DBBK/oracle

11 g以前の表示方法

SELECT a.VALUE || b.SYMBOL || c.instance_name || '_ora_' || d.spid ||
       '.trc' trace_file
  FROM (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') a,
  (SELECT SUBSTR(VALUE, -6, 1) SYMBOL FROM V$PARAMETER
         WHERE NAME = 'user_dump_dest') b,
       (SELECT INSTANCE_NAME FROM V$INSTANCE) c,
       (SELECT SPID FROM v$SESSION S, V$PROCESS P, V$MYSTAT M
         WHERE S.PADDR = P.ADDR
           AND S.SID = M.SID
           AND M.STATISTIC# = 0) d;
   
SQL> show parameter user_dump_dest
NAME             TYPE                VALUE
---------------- ------------------- ------------------------------
user_dump_dest   string              /DBBK/oracle/diag/rdbms/orcl/o     rcl/trace

パラメータモード

alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

一般的な方法

alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';

セッションのプロセスIDの取得

select p.PID, p.SPID, s.SID
  from v$process p, v$session s
 where s.paddr = p.addr
   and s.sid = &SESSION_ID;

SQLPLUSにログインして次の文を実行します.

oradebug setospid SPID/oradebug setorapid PID
oradebug unlimit
oradebug event 10046 trace name context forever,level12
oradebug event 10046 trace name context off

初期化パラメータによるインスタンストレースの設定

event="10046 trace name context forever,level 12"

logon triggerによるトレースの設定

CREATE OR REPLACE TRIGGER SYS.SET_TRACE
AFTER LOGON ON DATABASE
WHEN (USER LIKE '&USERNAME')
DECLARE
L_CMD VARCHAR2(200);
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET TARCEFILE_IDENTIFIER=''From_Trigger''';
EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL=ALL';
EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events '10046 trace name context forever,level 12';
END SET_TRACE;

 
 
参考記事:http://blog.sina.com.cn/s/blog_61cd89f60102edlx.html