對資料庫進行性能診斷可以使用SQL跟蹤的方法,把一些資訊記錄在trace檔案裡以後分析。可以通過設定初始化參數SQL_TRACE=TRUE來設定SQL跟蹤。也可以通過設定10046事件來進行SQL跟蹤,10046事件是oracle提供的内部事件,是對SQL_TRACE的增強,10046有以下幾個級别:
Level 0 停用SQL跟蹤,相當于SQL_TRACE=FALSE
Level 1 标準SQL跟蹤,相當于SQL_TRACE=TRUE
Level 4 在level 1的基礎上增加綁定變量的資訊
Level 8 在level 1的基礎上增加等待事件的資訊
Level 12 在level 1的基礎上增加綁定變量和等待事件的資訊
10046事件不但可以跟蹤使用者會話(trace檔案位于USER_DUMP_DEST ),也可以跟蹤background程序(trace檔案位于BACKGROUND_DUMP_DEST )。trace檔案的大小決定于4個因素:跟蹤級别,跟蹤時長,會話的活動級别和MAX_DUMP_FILE_SIZE參數。類似于SQL_TRACE,10046事件可以在全局設定,也可以在session級設定。
啟用跟蹤事件10046
1.在全局設定
修改初始化參數
EVENT = "10046 trace name context forever, level 8"
2.在目前session設定
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';
3.對其他使用者session設定
首先獲得要跟蹤的session的session id和serial number
select sid,serial#,username from v$session where username='TRACE_USERNAME';
exec dbms_support.start_trace_in_session(sid => 1234,serial# => 56789,waits => true,binds => true);
exec dbms_support.stop_trace_in_session(sid => 1234,serial# => 56789);
或者
exec dbms_system.set_ev( 1234, 56789, 10046, 8, '');
exec dbms_system.set_ev( 1234, 56789, 10046, 0, '');
或者
exec dbms_monitor.session_trace_enable(session_id => 1234,serial_num => 56789,waits => true,binds => true);
exec dbms_monitor.session_trace_disable(session_id => 1234,serial_num => 56789);
也可以通過使用oradebug工具來設定10046事件,首先通過V$PROCESS獲得該session的os process id。
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('TRACE_USERNAME');
oradebug setospid 12345;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 8;
oradebug event 10046 trace name context off;
擷取跟蹤檔案
1.使用oradebug
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183.trc
2.設定初始參數TRACEFILE_IDENTIFIER
alter session set tracefile_identifier = 'MyTrace';
生成的trace檔案名中會包含有MyTrace字樣
/opt/oracle/product/9.2.0/rdbms/log/uxdb_ora_9183_MyTrace.trc
3.通過SQL查詢
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;
檢視目前session的跟蹤級别
SQL>Set serveroutput on
SQL> declare
event_level number;
begin
dbms_system.read_ev(10046,event_level);
dbms_output.put_line(to_char(event_level));
end
trace檔案的格式化可以通過tkprof也可以通過trca,trca格式化的trace檔案可以儲存為文本及html格式,可讀性更強。