Tracing Enhancements Using DBMS_MONITOR

這篇文章主要介紹dbms_monitor包中新增加的跟蹤方法。新增加的跟蹤過程可以基於特定的客戶端標識或服務名，模組名與操作名的組合來啟用診斷與工作量管理。跟蹤也可以在會話級別啟用。在有些情況下可以產生多個跟蹤檔案（例如，當對一個模組跟蹤服務級別時）。trcsess工具可以用來掃描所有跟蹤檔案並將它們合併成一個跟蹤檔案。在合併之後可以使用標準跟蹤檔案分析方法比如tkprof。對客戶端標識或服務/模組/操作的跟蹤狀態是永久的可以跨會話的斷開與資料庫的關閉，並且可以應用於所有例項。跟蹤直到使用dbms_monitor禁用之前都是啟用狀態。

如何檢視是否啟用跟蹤
當客戶端與服務/模組/操作跨會話斷開與資料庫關閉永久存在時，有一種方法來判斷是否啟用了跟蹤。當啟用跟蹤時，跟蹤資訊會被記錄到dba_enabled_traces中。

SQL> select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                              QUALIFIER_ID1                   WAITS BINDS
--------------------- --------------------------------------- ------------------------------- ----- -----
SERVICE_MODULE        SYS$USERS                               SQL*Plus                       TRUE  FALSE
CLIENT_ID             HUGO                                                                   TRUE  FALSE
SERVICE               v101_DGB                                                               TRUE  FALSE  

可以看到三個不同的跟蹤被啟用。
第一行:跟蹤所有由SQL*Plus所執行的SQL語句
第二行：跟蹤所有客戶端識別符號為'HUGO'的所有會話
第三行:跟蹤透過服務'v101_DGB'連線到資料庫的所有程式

session_trace_enable函式
session_trace_enable對本地例項的指定會話啟用SQL跟蹤,語法如下:
啟用跟蹤

dbms_monitor.session_trace_enable(session_id=>x,serial_num=>y,waits=>(TRUE|FALSE),binds=>(TRUE|FALSE));

禁用跟蹤

dbms_monitor.session_trace_disable(session_id=>x,serial_num=>y);

預設情況下跟蹤對於等待為true，而繫結變數為false

透過查詢v$session得到會話與serial號

SQL> select sid, serial#,username from  v$session;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
         2       3257 INSUR_CHANGDE
         4      45331
        41      20923 INSUR_CHANGDE
        42      19225 INSUR_CHANGDE
        77          1
        78       2191 CARD_DB
       115          1
       118      47221 YBCX
       153          1
       157      25173 INSUR_CHANGDE
       191          1

執行下面的命令開始跟蹤

SQL> execute dbms_monitor.session_trace_enable(157,25173);

PL/SQL procedure successfully completed.

需要注意的是在dba_enabled_traces檢視中沒有記錄，因為跟蹤並沒有經歷資料庫關閉。

可以透過查詢v$session得到被跟蹤會話列表:

SQL> select sid,serial#,username,sql_trace_waits,sql_trace_binds,sql_trace_plan_stats
  2  from   v$session 
  3  where  sql_trace = 'enabled'; 

no rows selected

當會話斷羨慕或執行以下命令可以停止跟蹤

SQL> execute dbms_monitor.session_trace_disable(157,25173);

PL/SQL procedure successfully completed.

client_id_trace_enable函式
在多層架構環境中，來自終端使用者的一個請求將會透過中間層被路由到不同的資料庫會話。這意味著在終端客戶端與資料庫會話之間不存在靜態關聯。10g之前的版本，沒有一種簡單方法來跨不同資料庫會話對客戶端進行跟蹤。透過引入新的屬性client_identifier使用端對端的跟蹤成為可能，它用來唯一標識一個指定的終端。客戶端標識被記錄在v$session檢視的client_identifier列中。還可以透過系統上下文來檢視。語法如下:
啟用跟蹤

execute dbms_monitor.client_id_trace_enable ( client_id =>'client_id', waits => (TRUE|FALSE), binds => (TRUE|FALSE) );

禁用跟蹤

execute dbms_monitor.client_id_trace_disable ( client_id =>'client_id');

預設情況下跟蹤對於等待為true，繫結變數為false

下面透過使用dbms_session.set_identifier過程來設定client_identifier

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.

可以透過兩種方法來找到客戶端標識
1.在實際會話中

SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

CLIENT_ID
--------------------------------------------------------------------------------
JY

2.從不同的會話中

SQL> select client_identifier client_id from v$session where sid =18;
CLIENT_ID
----------------------------------------------------------------
JY

對客戶端標識為'JY'的所有會話啟用跟蹤

SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

現在這個跟蹤可以跨越資料庫的關閉

SQL>  select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----
CLIENT_ID             JY                                                                                                                                TRUE  FALSE

為了禁用跟蹤，執行以下命令:

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.


SQL>  select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----

這可能造成有時生成多個跟蹤檔案。例如，當使用共享伺服器時，不同的共享伺服器程式可以執行SQL語句。這將導致生成多個跟蹤檔案。對於RAC來說也同樣會生成多個跟蹤檔案。後面將介紹如何使用trcsess工具將多個跟蹤檔案合併成一個跟蹤檔案。

SERV_MOD_ACT_TRACE_ENABLE函式
端到端的跟蹤對於有效管理與使用services，module與action來計算應用程式工作量很有用。可以使用serv_mod_act_trace_enable函式來對服務名，模組名與操作名特定的組合對全域性或特定例項啟用SQL跟蹤。

透過查詢v$session檢視的service_name,module和action列可以檢視服務名，模組與操作名。語法如下:
啟用跟蹤

execute dbms_monitor.serv_mod_act_trace_enable('Service1', 'Module1', 'Action1', waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' );

禁用跟蹤

execute dbms_monitor.serv_mod_act_trace_disable('Service1', 'Module1', 'Action1');

預設情況下跟蹤對於等待為true，對於綁變數為false。預設例項名為null。

示例
下面跟蹤透過SQL*Plus與預設服務SYS$USERS所執行的所有SQL語句

SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );

PL/SQL procedure successfully completed.

檢查是否啟用跟蹤

SQL> select primary_id, qualifier_id1, waits, binds from  dba_enabled_traces where trace_type = 'SERVICE_MODULE';
PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----
SYS$USERS                                                        SQL*Plus                                                         TRUE  FALSE

禁用跟蹤

SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus');

PL/SQL procedure successfully completed.


SQL> select primary_id, qualifier_id1, waits, binds from  dba_enabled_traces where trace_type = 'SERVICE_MODULE';
PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----

使用trcsess合併跟蹤檔案
有些跟蹤操作會生成多個跟蹤檔案。 trcsess可以根據特定會話或客戶端標識來合併跟蹤檔案。
語法如下:

trcsess [output=] [session=] [clientid=] [service=] [action=] [module=] 

會話1：

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.


SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

SQL> select 'session 1' from dual;

'SESSION1
---------
session 1

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.

會話2：

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.

SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

SQL> select 'session 2' from dual;

'SESSION2
---------
session 2

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.

使用trcsess合併跟蹤檔案

[oracle@jyrac1 trace]$ trcsess output=trcsess_Jy_Trace.txt clientid='JY'  *.trc
[oracle@jyrac1 trace]$ ls -lrt *Jy*.txt
-rw-r--r-- 1 oracle oinstall 97786 Mar  2 15:17 trcsess_Jy_Trace.txt

dbms_application_info
dbms_application_info.set_x_info過程在會話開始前呼叫可以用來註冊並命名事務/客戶端資訊/模組為以後的效能檢查所使用。
dbms_application_info包含以下過程:
set_client_info(client_info in varchar2)
set_action(action_name in varchar2)
set_module(module_name in varchar2,action_name in varchar2)

SQL> begin
  2  dbms_application_info.set_module(module_name => 'add_employee',action_name => 'insert into emp');
  3  insert into scott.emp (ename, empno, sal, mgr, job, hiredate, comm, deptno )
  4  values ( 'scott', 9998, 1000, 7698,'clerk', sysdate,0, 10);
  5  dbms_application_info.set_module(null,null); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

下面透過使用module與action列作為查詢條件來查詢v$sqlarea檢視來獲得上面執行的SQL語句

SQL> select sql_text from v$sqlarea where module = 'add_employee' and action = 'insert into emp';

SQL_TEXT
--------------------------------------------------------------------------------
INSERT INTO SCOTT.EMP (ENAME, EMPNO, SAL, MGR, JOB, HIREDATE, COMM, DEPTNO ) VAL
UES ( 'scott', 9998, 1000, 7698,'clerk', SYSDATE,0, 10)

也可以執行以下過程來獲得資訊

SQL> set serveroutput on
SQL> declare
  2  l_clinent varchar2(100);
  3  l_mod_name varchar2(100);
  4  l_act_name varchar2(100);
  5  begin
  6  dbms_application_info.set_client_info('my client');
  7  dbms_application_info.read_client_info(l_clinent);
  8  dbms_output.put_line('client='||l_clinent);
  9  dbms_application_info.set_module('my mod','inserting');
 10  dbms_application_info.read_module(l_mod_name,l_act_name);
 11  dbms_output.put_line('mod_name='||l_mod_name);
 12  dbms_output.put_line('act_name='||l_act_name);
 13  end;
 14  /
client=my client
mod_name=my mod
act_name=inserting
PL/SQL procedure successfully completed