Oracle 10046跟踪事件操作步骤

来源：互联网发布：linux下web服务器配置编辑：程序博客网时间：2024/05/29 19:31

1、开启10046事件跟踪

alter session set events '10046 trace name context forever, level 12';

2、随便执行一个SQL语句（trace文件中我们会发现此SQL的执行情况）

select * from dba_users where username='HR';

3、停止10046事件跟踪

alter session set events '10046 trace name context off';

4、定位此次生成的跟踪文件

select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;

5、用tkprof工具规范文件格式（便于查看分析）

tkprof.exe E:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_3304.trc

output=1.txt

nodepad 1.txt

SQL ID: 7bx241ats4942 Plan Hash: 4242951753

select *

from

dba_users where username='HR'

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.03 0.01 0 0 1 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.00 0.01 1 16 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 0.03 0.02 1 16 1 1

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

---------- ---------- ---------- ---------------------------------------------------

1 1 1 MERGE JOIN CARTESIAN (cr=16 pr=1 pw=0 time=13157 us cost=12 size=228 card=1)

1 1 1 HASH JOIN OUTER (cr=14 pr=1 pw=0 time=13068 us cost=11 size=215 card=1)

1 1 1 HASH JOIN (cr=12 pr=1 pw=0 time=12782 us cost=9 size=177 card=1)

1 1 1 NESTED LOOPS (cr=10 pr=1 pw=0 time=12407 us)

17 17 17 NESTED LOOPS (cr=9 pr=1 pw=0 time=12355 us cost=6 size=167 card=1)

1 1 1 NESTED LOOPS (cr=8 pr=0 pw=0 time=153 us cost=5 size=154 card=1)

1 1 1 NESTED LOOPS (cr=6 pr=0 pw=0 time=135 us cost=4 size=142 card=1)

1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=115 us cost=3 size=130 card=1)

1 1 1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=39 us cost=1 size=111 card=1)

1 1 1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=23 us cost=0 size=0 card=1)(object id 46)

1 1 1 TABLE ACCESS FULL USER_ASTATUS_MAP (cr=2 pr=0 pw=0 time=74 us cost=2 size=19 card=1)

1 1 1 TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=17 us cost=1 size=12 card=1)

1 1 1 INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 7)

1 1 1 TABLE ACCESS CLUSTER TS$ (cr=2 pr=0 pw=0 time=14 us cost=1 size=12 card=1)

1 1 1 INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 7)

17 17 17 INDEX RANGE SCAN I_PROFILE (cr=1 pr=1 pw=0 time=12206 us cost=0 size=0 card=17)(object id 285)

1 1 1 TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=1 pr=0 pw=0 time=43 us cost=1 size=13 card=1)

2 2 2 TABLE ACCESS FULL PROFNAME$ (cr=2 pr=0 pw=0 time=46 us cost=2 size=10 card=1)

0 0 0 TABLE ACCESS FULL RESOURCE_GROUP_MAPPING$ (cr=2 pr=0 pw=0 time=46 us cost=2 size=38 card=1)

1 1 1 BUFFER SORT (cr=2 pr=0 pw=0 time=80 us cost=10 size=13 card=1)

1 1 1 TABLE ACCESS BY INDEX ROWID PROFILE$ (cr=2 pr=0 pw=0 time=42 us cost=1 size=13 card=1)

17 17 17 INDEX RANGE SCAN I_PROFILE (cr=1 pr=0 pw=0 time=35 us cost=0 size=0 card=17)(object id 285)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 1 0.00 0.00

Disk file operations I/O 1 0.00 0.00

db file sequential read 1 0.01 0.01

SQL*Net message from client 1 22.50 22.50

以上信息即为规范后的相关SQL的执行信息，以便DBA对相关问题SQL进行诊断