oracle 追踪一个SQL会话

oracle中追踪一个sql会话有很多方式,oracle 11g中官方推荐使用DBMS_MONITOR.

在实际生产环境中,尽量慎重这个操作,尽可能找出具体的会话ID或pid来追踪.
–新建测试表
create tablespace test
datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\test01.DBF’
size 500m
autoextend on
next 100m maxsize unlimited
extent management local autoallocate
segment   space management auto;
create table t1
(
sid int not null primary key,
sname varchar2(10)
)
tablespace test;

 
–循环导入数据
declare
maxrecords constant int:=100000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,’ocpyang’);
end loop;
dbms_output.put_line(‘ 成功录入数据! ‘);
commit;
end;
/

 

 

 

select se.sid,
se.serial#,
pr.spid,
se.status 状态,
substr(se.program, 1, 20) “程序”,
substr(se.machine, 1, 30) “机器名”,
sq.sql_text “sql文本”
from v$session se,
v$sqlarea sq,
v$process pr
where se.paddr = pr.addr(+)
and se.sql_address = sq.address(+)
and schemaname <> ‘SYS’
and se.username=upper(‘&username’)
order by se.sid
;

 

SID    SERIAL# SPID          状态       程序                   机器名       sql文本
———- ———- ————– ——– ——————– ———- —————–

171      25079 1539784       INACTIVE sqlplus.exe          WORKGROUP\LOG     select * from t1

 

execute dbms_monitor.session_trace_enable(session_id=>171,serial_num=> 25079,waits=>true,binds=>false);
select * from t1 ;

 

execute dbms_monitor.session_trace_disable();

 

–步骤5)获得跟踪文件名

—方法1:

SELECT    d.VALUE
|| ‘\’
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| ‘_ora_’
|| p.spid
|| ‘.trc’ trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = ‘thread’
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = ‘user_dump_dest’) d
/
—方法2:
select value from v$diag_info
where name=’Default Trace File’;

 

E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_1539784.trc
–步骤6)分析跟踪文件
—-方法1:tkprof
tkprof E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_1539784.trc e:\ocpyang427.out sys=yes

标签