[10046]oradebug 10046的使用
来源:互联网 发布:pid补偿网络 编辑:程序博客网 时间:2024/06/08 12:01
一、对当前的session使用oradebug命令:
SQL> conn / as sysdbaConnected.SQL> oradebug setmypidStatement processed.
或者对其他session做跟踪
oradebug setorapid pid连接到一个外部服务进程,且pid=v$process.pidoradebug 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 SALES14 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: 844388907select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptnocall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.03 0.04 0 161 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.02 8 10 0 14------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.03 0.07 8 171 0 14Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSNumber of plan statistics captured: 1Rows (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 STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.03 0.04 0 161 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.02 8 10 0 14------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.03 0.07 8 171 0 14Misses in library cache during parse: 1Elapsed 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.00OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 13 0.01 0.01 0 0 0 0Execute 82 0.03 0.08 0 0 0 0Fetch 104 0.00 0.00 1 261 0 93------- ------ -------- ---------- ---------- ---------- ---------- ----------total 199 0.05 0.09 1 261 0 93Misses in library cache during parse: 12Misses in library cache during execute: 12Elapsed 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.trcTrace file compatibility: 11.1.0.7Sort 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.
0 0
- [10046]oradebug 10046的使用
- 使用oradebug开启10046
- oradebug的使用
- 10046 oradebug sql跟踪
- sqlplus中使用oradebug定位 alter session 10046事件产生的trace文件
- ORADEBUG 使用介绍
- 使用oradebug挂起进程
- ORADEBUG使用详解
- Oracle-oradebug使用小记
- oradebug使用详解
- oradebug
- oradebug
- oradebug
- oradebug
- oradebug
- oradebug
- oracle oradebug hanganalyze简单使用
- 11.未公开的Oracle数据库秘密笔记——ORADEBUG使用
- volatile的详细讲解
- Java动态代理机制
- 虚拟机中伪分布式Hadoop的部署
- Linux 学习(3)-- kernel版本号的修改
- mysql show命令集合
- [10046]oradebug 10046的使用
- ARP协议工作过程
- 大excel文件分割排版为印刷pdf格式:对指定行加粗
- codeforces #329 D. Happy Tree Party (LCA+并查集 || 树链剖分)
- uvalive6952 - Cent Sa dp
- poj2549
- opencv中RNG生成随机数
- 在TabControl中的TabPage选项卡中添加Form窗体
- LeetCode Nim Game 递推