oracle 追踪一个SQL会话

来源：互联网发布：mac百度网盘上传速度慢编辑：程序博客网时间：2024/05/01 23:28

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