對sql_trace和dbms_monter跟蹤的一點總結(摘錄)

對sql_trace和dbms_monter跟蹤的一點總結

以下是事件的有效trace級別：
Level 0 tracing被關閉。這相當於設定sql_trace=false。
Level 1 標準SQL trace資訊（SQL_TRACE=TRUE）。這是預設級別。
Level 4 SQL trace資訊加繫結變數值。
Level 8 SQL trace資訊加等待事件資訊。
Level 12 SQL trace 資訊，等待事件資訊，和繫結變數值。
兩個引數
alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
你能使用trace事件10046來跟蹤使用者會話或Oracle後臺程式。

會話位置:在USER_DUMP_DEST目錄下。然後可以使用TKPROF處理跟蹤檔案
$tkprof /usr/oracle/....../ora920/udump/ora920_ora_14246.trc tk.prf
後臺位置:後臺程式的跟蹤檔案在BACKGROUND_DUMP_DEST

查詢USER_DUMP_DEST位置，找到trace檔案
SELECT c.VALUE || 'ORA' || to_char(a.spid, 'fm00000') || '.trc'
FROM v$process a,
v$session b,
v$parameter c
WHERE a.addr = b.paddr
AND b.audsid = sys_context('userenv', 'sessionid')
AND c.NAME = 'user_dump_dest'

/
--ORACLE9i以後版本使用
SELECT rtrim(c.VALUE, '/') || '/' || d.instance_name || '_ora_' ||
ltrim(to_char(a.spid)) || '.trc'
FROM v$process a,
v$session b,
v$parameter c,
v$instance d
WHERE a.addr = b.paddr
AND b.audsid = sys_context('userenv', 'sessionid')
AND c.NAME = 'user_dump_dest'
/

SELECT rtrim(c.VALUE, '/') || '/' || d.instance_name || '_ora_' ||
ltrim(to_char(a.spid)) || '.trc'
FROM v$process a,
v$session b,
v$parameter c,
v$instance d
WHERE a.addr = b.paddr
AND b.audsid = sys_context('userenv', 'sessionid')
AND c.NAME = 'user_dump_dest'
/

在10g中使用oradebug trace工具，因為專用服務的SPID數字也被寫到trace檔案。另外，你也能得到真正的跟蹤檔名如下：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8
Statement processed.
SQL> oradebug tracefile_name
d:oracleadminor92udumpor92_ora_171.trc

從Oracle8.1.7開始，你能為你會話設定TRACEFILE_IDENTIFIER引數，使用ALTER SESSION命令，如下顯示：
alter session set tracefile_identifier='MyTrace';


跟蹤自己的會話：命令格式
enable: alter session set events '10046 trace name context forever, level 8';
disable: alter session set events '10046 trace name context off';

------------------------------------------------------------------------------------------------
DBMS_SUPPORT包

安裝：
SQL> conn / as sysdba
SQL> @?/rdbms/admin/dbmssupp.sql
SQL> grant execute on dbms_support to scott;
SQL> conn scott/tiger

開啟和關閉跟蹤：
-- To include Wait Event data with SQL trace (default option)
exec sys.dbms_support.start_trace;
-- To include Bind variable values, Wait Event data with SQL trace
exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE)
-- Run your SQL script or program to trace wait event information
-- To turn off the tracing:
exec sys.dbms_support.stop_trace;
-----------------------------------------------------------------------------------------------------
跟蹤自己當前會話：
EXECUTE SYS.DBMS_SUPPORT.START_TRACE
ALTER SESSION SET events '10046 trace name context forever, level 12';
------------------------------------------------------------------------------------------------------
跟蹤其他人的會話：
如果你不能確信引數TIMED_STATISTICS和MAX_DUMP_FILE_SIZE是否對你想跟蹤的會話設定合適，
你應該從V$SESSION得到SID和它的序列號（SERIAL#）。
在開啟trace之前，以下過程來合適的設定這些引數。
-- Set TIME_STATISTICS to TRUE for SID 1234, Serial# 56789
exec sys.dbms_system.set_bool_param_in_session( sid => 1234, -
serial# => 56789, -
parnam => 'TIMED_STATISTICS', -
bval => true);
-- Set MAX_DUMP_FILE_SIZE to 2147483647 for SID 1234, Serial# 56789
exec sys.dbms_system.set_int_param_in_session(sid => 1234, -
serial# => 56789, -
parnam => 'MAX_DUMP_FILE_SIZE', -
intval => 2147483647);

接下來的步驟是在其他會話中去開啟trace，然後在你已經收集足夠的trace資訊之後關閉它。你能使用以下方法之一來處理:
方法1 使用DBMS_SUPPORT包過程
execute sys.dbms_support.start_trace (true,true);
-- Enable 'level 12' trace in session 1234 with serial# 56789
exec dbms_support.start_trace_in_session( sid => 1234, -
serial# => 56789, -
waits => true, -
binds => true);-- Let the session execute SQL script or program for some amount of time
-- To turn off the tracing:
exec dbms_support.stop_trace_in_session( sid => 1234, serial# => 56789);
------------------------------------------------------------------------------------------
方法2 使用dbm_system
-- Enable trace at level 8 for session 1234 with serial# 56789
execute dbms_system.set_ev( 1234, 56789, 10046, 8, '');
-- Let the session execute SQL script or program for some amount of time
-- To turn off the tracing:
execute dbms_system.set_ev( 1234, 56789, 10046, 0, '');
如果我們用之前的方法跟蹤時，由於連線池是共享的，一個資料庫會話可以為多個終端所共享，因此沒有辦法跟蹤一個具體的終端會話。
因此如果要檢視那個使用者使用了最多的資源，將使用以下查詢：
SPOOL traceall.sql
SET HEADING OFF FEEDBACK OFF
SELECT 'EXECUTE SYS.dbms_system.set_ev (' || to_char(sid) || ', ' ||
to_char(serial#) || ', 10046, 8, '''')'
FROM v$session
WHERE username = 'WEB_USER';
spool off
SET FEEDBACK ON
@traceall.sql
在基於web的應用下，該語句通常會產生大量的跟蹤檔案，並且為資料庫造成很大的負載。並且也得不到具體終端會話的資訊。
---------------------------------------------------------------------------------------------------------------------------
方法3 使用oradebug工具。你需要知道會話的OS程式ID(SPID)或Oracle程式ID(PID)。你能檢視他們在v$process檢視。假設你 知道你想跟蹤的使用者名稱：
SELECT s.username,
p.spid os_process_id,
p.pid oracle_process_id
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.username = upper('&user_name');
現在使用SQL*Plus以sysdba連線併發出以下命令：
alter system set timed_statistics=true;
oradebug setospid 12345; --12345 是會話的OS程式id
Oradebug unlimit;
Oradebug event 10046 trace name context forever ,level 8;
--讓會話執行SQL指令碼或程式一段時間
--關閉trace
Oradebug event 10046 trace name context off;
--------------------------------------------------------------------------------------------------------------------
方法4 在ORACLE10g1版本中，你能使用DBMS_MONITOR包過程來開啟基於SID，服務名，模組，或動作來跟蹤。

使用DBMS_MONITOR包來啟動對會話1234和serial#56789如下(跟蹤自己的會話)：
execute dbms_monitor.session_trace_enable(1234,56789,true,true);
execute dbms_monitor.session_trace_enable(waits=>true, binds=>true);
-- Let the session execute SQL script or program for some amount of time
跟蹤其他的資料庫會話：
使用DBMS_MONITOR只對下面的會話啟用跟蹤，所有這些設定都是永久性的，所有與該服務和模組關聯的會話都會被跟蹤，而不僅僅是跟蹤當前會話
exec dbms_monitor.session_trace_enable(139);
exec dbms_monitor.session_trace_enable(session_id=>139, serial_num=>53, waits=>true, binds=>false);

-- To turn off the tracing:
execute dbms_monitor.session_trace_disable(1234, 56789);

使用DBMS_MONITOR包用於服務，模組，和基於動作的跟蹤。
--開啟級別12跟蹤已知服務，模組，和動作
execute dbms_monitor.serv_mod_act_trace_enable('APPS1','GLEDGER','DEBIT_ENTRY',TRUE,TRUE,NULL);
--執行sql指令碼或程式一段時間
--關閉tracing
Execute dbms_monitor.serv_mod_act_trace_disable('APPS1','GLEDGER','DEBIT_ENTRY');

在跟蹤連線池的會話中，DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE過程特別有用，其允許跟蹤一個給定客戶端識別符號的會話的 所有活動。
如果多個資料庫會話為一個客戶端識別符號服務，該過程將寫入多個跟蹤檔案。
exec dbms_monitor.client_id_trace_enable(client_id=>'kimberly');

另一個重要過程是DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE，其允許跟蹤service_name, module_name, action_name確定的特定模組，
如果應用程式進行了恰當的組織，該過程將比較有用。
SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'testenv', module_name=>'product_update');
（PL/SQL開發人員可以透過使用DBMS_APPLICATION_INFO包設定模組名稱和動作名稱，把一些呼叫嵌入到他們的應用程式中）

另一個提高是trcsess工具，其用來將DBMS_MONITOR建立的多個跟蹤檔案結合在一起：
trcsess [output=] [session=] [clientid=]
[service=] [action=] [module=]
為全部跟蹤檔案，然後我們可以應用TKPROF到新的檔案。


舉例說明：
在實際的使用過程中，透過在頁面開始時設定會話識別符號：
PROCEDURE set_ora_session_id (p_session_id IN VARCHAR2)
IS
BEGIN
dbms_session.set_identifier (p_session_id);
END set_ora_session_id;
在頁面結束時清除會話識別符號：
PROCEDURE clear_ora_session_id
IS
BEGIN
dbms_session.clear_identifier;
END clear_ora_session_id;

然後我們就可以在會話活動期間進行跟蹤：
exec dbms_monitor.client_id_trace_enable('20558307491688865029', waits=> true, binds=> true)；
會話退出後關閉監控：
exec dbms_monitor.client_id_trace_disable ('20558307491688865029')；
你可以透過DBA_ENABLED_TRACES檢視所有正啟用的跟蹤。[@more@]