對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@]
以下是事件的有效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@]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16396910/viewspace-1030090/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql_trace 和 events 跟蹤事件SQL事件
- sql_trace跟蹤工具(轉)SQL
- 利用sql_trace跟蹤一個指定會話的操作SQL會話
- oracle 跟蹤檔案和轉儲命令(轉摘）Oracle
- oracle跟蹤事件（dump）總結Oracle事件
- <摘錄>演算法策略的總結演算法
- SQL SERVER 跟蹤標記總結SQLServer
- 關於oracle中session跟蹤的總結OracleSession
- 【最佳化】SQL_TRACE之生成跟蹤檔案SQL
- SQL效能的度量 - 會話級別的SQL跟蹤sql_traceSQL會話
- KCF目標跟蹤方法分析與總結
- HP下對程式的跟蹤
- 【DB】使用SQL_TRACE進行資料庫診斷跟蹤SQL資料庫
- sql_trace跟蹤檔案中名詞簡寫說明SQL
- oracle跟蹤檔案和跟蹤事件(zt)Oracle事件
- oracle 使用者跟蹤 需要的sql語句總結OracleSQL
- <摘錄>位元組對齊與結構體大小結構體
- 對session進行跟蹤Session
- 小專案跟蹤記錄
- 【摘錄】index（一）Index
- 多目標跟蹤論文及程式碼總結
- 【SQL_TRACE】解決普通使用者無法執行SQL_TRACE跟蹤其他會話問題SQL會話
- 物聯網對位置跟蹤和個人安全的好處
- git的跟蹤分支和遠端跟蹤分支學習筆記Git筆記
- Oracle跟蹤事件和dumpOracle事件
- 和分割槽表相關的一點總結
- 資料庫資料跟蹤記錄資料庫
- Exadata 之 解除安裝、智慧掃描（摘錄加總結）
- 《圖解HTTP》知識點摘錄圖解HTTP
- 【Longkin】ASP.NET應用程式跟蹤---（一）跟蹤頁面ASP.NET
- Git管理專案例項說明-記錄和跟蹤專案Git
- Duplicate的一點總結
- ash的一點總結
- 一點總結
- celery筆記一之celery介紹、啟動和執行結果跟蹤筆記
- session跟蹤失效的問題和原因Session
- SQL跟蹤工具和TKPROF使用SQL
- 對使用dblink的10046事件跟蹤事件