[10046]oradebug 10046的使用

最新推荐文章于 2024-10-11 08:30:00 发布

原创最新推荐文章于 2024-10-11 08:30:00 发布 · 648 阅读

CC 4.0 BY-SA版权

本文详细介绍了如何使用oradebug 10046命令对Oracle数据库进行调试，包括针对当前session的调试设置，以及对其他session的跟踪方法。同时，还讲解了利用tkprof工具对收集的trc文件进行格式化，以便于分析和理解执行过程。最终，读者将学会查看和解析生成的trace文件，以提升数据库问题诊断能力。

一、对当前的session使用oradebug命令：

SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.

或者对其他session做跟踪

oradebug  setorapid pid
连接到一个外部服务进程，且pid=v$process.pid
oradebug  setospid  spid
连接到一个外部服务进程，且spid=v$process.spid

二、激活10046事件

SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.

三、执行目标sql语句

SQL> select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      ...省略相同内容
      7654 MARTIN     SALES

14 rows selected.

四、查看对应的trc文件及目录

SQL> oradebug tracefile_name;
/u01/oracle/diag/rdbms/neal/neal/trace/neal_ora_18691.trc

五、关闭当前的10046跟踪事件

SQL> oradebug event 10046 trace name context off;
Statement processed.

六、使用tkprof来格式化

[oracle@server ~]$ tkprof /u01/oracle/diag/rdbms/neal/neal/trace/neal_ora_18691.trc /tmp/neal_ora_18691.trc

七、查看得到的neal_ora_18691.trc文件

SQL ID: 3yfu3wh150aqt Plan Hash: 844388907

select empno,ename,dname 
from
 scott.emp,scott.dept where emp.deptno=dept.deptno


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0        161          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.02          8         10          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.07          8        171          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        14         14         14  MERGE JOIN  (cr=10 pr=8 pw=0 time=29081 us cost=6 size=364 card=14)
         4          4          4   TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=2 pw=0 time=19696 us cost=2 size=52 card=4)
         4          4          4    INDEX FULL SCAN PK_DEPT (cr=2 pr=1 pw=0 time=18734 us cost=1 size=0 card=4)(object id 87107)
        14         14         14   SORT JOIN (cr=6 pr=6 pw=0 time=9416 us cost=4 size=182 card=14)
        14         14         14    TABLE ACCESS FULL EMP (cr=6 pr=6 pw=0 time=9371 us cost=3 size=182 card=14)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net break/reset to client                   2        0.00          0.00
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       10.81         20.69
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         3        0.01          0.01
  db file scattered read                          1        0.00          0.00



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0        161          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.02          8         10          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.07          8        171          0          14

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net message from client                     6       34.85         80.64
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         3        0.01          0.01
  SQL*Net break/reset to client                   2        0.00          0.00
  db file scattered read                          1        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       13      0.01       0.01          0          0          0           0
Execute     82      0.03       0.08          0          0          0           0
Fetch      104      0.00       0.00          1        261          0          93
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      199      0.05       0.09          1        261          0          93

Misses in library cache during parse: 12
Misses in library cache during execute: 12

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         1        0.00          0.00

    1  user  SQL statements in session.
   17  internal SQL statements in session.
   18  SQL statements in session.
********************************************************************************
Trace file: /u01/oracle/diag/rdbms/neal/neal/trace/neal_ora_18691.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
      17  internal SQL statements in trace file.
      18  SQL statements in trace file.
      17  unique SQL statements in trace file.
    1323  lines in trace file.
      10  elapsed seconds in trace file.