对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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16396910/viewspace-1030090/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16396910/viewspace-1030090/