Oracle通过debug跟踪会话

1. 查看会话信息

set linesize 180 pagesize 10000
col username for a10
col program for a25
col machine for a25
COL EVENT FOR A30
col sql_id for a20
select USERNAME,INST_ID,sid,serial#,program,machine,sql_id,event 
from gv$session where machine like '%93%';

USERNAME      INST_ID        SID    SERIAL# PROGRAM                   MACHINE                   SQL_ID               EVENT
---------- ---------- ---------- ---------- ------------------------- ------------------------- -------------------- ------------------------------
HMISW2003           2       2479       1037 egmzsf.exe                WorkGroup\XXK93                                SQL*Net message from client

2. 根据sid找到pid

SQL> select pro.spid from v$session ses,v$process pro where ses.sid=2479 and ses.paddr=pro.addr;

SPID
------------------------
51186

oradebug
SQL> oradebug setospid 51186;
Oracle pid: 42, Unix process pid: 51186, image: oracle@djyy-oracle02
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.


SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_51186.trc
SQL> oradebug event 10046 trace name context off;
Statement processed.

3. tkprof转化trace文件

tkprof /oracle/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_51186.trc /home/oracle/aa.txt sys=no

4. 查看trace

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      524      0.07       0.08          0          0          0           0
Execute    506      0.03       0.08          0          0          0           0
Fetch   105636     14.45      16.78          0    4343649          0      392494
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   106666     14.56      16.95          0    4343649          0      392494

Misses in library cache during parse: 17
Misses in library cache during execute: 4

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                  106564        0.00          0.05
  SQL*Net message from client                106563      210.78        539.12
  SQL*Net break/reset to client                  28        0.00          0.01
  SQL*Net more data to client                    16        0.00          0.00