附(转化及获取trace文件名tkprof tracefile file sys=no)
SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM (SELECT P.SPID
FROM SYS.V$MYSTAT M, SYS.V$SESSION S, SYS.V$PROCESS P
WHERE M.STATISTIC# = 1
AND S.SID = M.SID
AND P.ADDR = S.PADDR) P,
(SELECT T.INSTANCE
FROM SYS.V$THREAD T, SYS.V$PARAMETER V
WHERE V.NAME = 'thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
(SELECT VALUE FROM SYS.V$PARAMETER WHERE NAME = 'user_dump_dest') D
1、SQL TRACE(轻量级跟踪)
1) 本session Method A: alter session set sql_trace=true;
本session Method B: alter session set events '10046 trace name context forever,level 1'; (Start)
alter session set events '10046 trace name context off'; (Stop)
2) 其它session：exec dbms_system.set_sql_trace_in_session(16,1042,true) ; (start)
exec dbms_system.set_sql_trace_in_session(16,1042,false) ; (stop)
2、Event 10046(中量级跟踪)
注： sid：543；serial#：1306；event：10046；level：12 ；一般也可用8，跟踪出来等待事件。
1)本session :alter session set tracefile_identifier='mystr';
or SELECT b.spid FROM v$session a , v$process b WHERE a.paddr=b.addr AND a.sid=149;
alter session set events '10046 trace name context forever,level 8'; (Start)
alter session set events '10046 trace name context off'; (Stop)
2)其它session:exec dbms_system.set_ev(543,1306,10046,12,'TOM'); (Start)
exec dbms_system.set_ev(543,1306,10046,0,'TOM'); (Stop)
level 1 ：to enable the standard SQL_TRACE facility (same as SQL_TRACE=TRUE)
等同于标准的SQL_TRACE=TRUE；
level 4 ：to enable SQL_TRACE and also capture bind variable values in the trace file
启用SQL_TRACE，并捕捉跟踪文件中的绑定变量；
level 8： to enable SQL_TRACE and also capture wait events into the trace file
启用SQL_TRACE，并捕捉跟踪文件中的等待事件；
level 12：to enable standard SQL_TRACE and also capture bind variables and wait events
启用SQL_TRACE，并捕捉跟踪文件中的绑定变量和等待事件（捕获信息能力最强）。
3)其它session(从OS的PID着手):
SQL>connect / as sysdba; SQL>oradebug setospid 4835
SQL>oradebug unlimit --不限制转储文件的大小
SQL> oradebug event 10046/10053 trace name context forever,level 12 --设置事件进行sql 跟踪
SQL> oradebug event 10046/10053 trace name context off
--关闭,注意禁用oradebug跟踪oracle 的smon,pmon等进程，会杀掉这引起宕库
SQL> oradebug tracefile_name;显示跟踪文件名
3、Event 10053(重量级跟踪寻找执行计划错误的原因)
1)设置本session 的10053
开启：Alter session set events’10053 trace name context forever[,level {1/2}]’;
关闭：Alter session set events’10053 trace name context off’;
2)设置其他session 的10053
开启：SYS.DBMS_SYSTEM.SET_EV (, , 10053, {1|2}, '''')
关闭：SYS.DBMS_SYSTEM.SET_EV (, , 10053,0, '''')
跟其他跟踪事件不同，10053 提供了两个跟踪级别，但是级别2 的跟踪信息比级别1 少(其他跟踪事件如10046 跟踪级别越高信息越多)，跟踪信息将被记录到user_dump_dest 目录底下。注意，要实现跟踪必须满足两个条件：sql 语句必须被hard parse 并且必须使用CBO 优化器模式。如果sql 语句已经被parse 过，那么10053 不生成跟踪信息。如果你使用RULE 优化器，那么10053 也不会生成跟踪信息。
4、关于trace 文件内容分析
1）摘录第一部分，SQL语句的执行情况总览
---------- -------------------
SECselect count(*)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 10.94 10.68 222186 222957 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 10.94 10.68 222186 222957 0 1
关于统计表格的标题信息中count、cpu、elapsed、disk、query、current和rows的说明在该trace文件的最前端有一个简要的说明，这里再分别赘述一下。
count ：查询在此阶段执行的次数；
cpu ：该查询在此阶段的CPU时间量，以毫秒为单位；
elapsed ：花费在此阶段上的挂钟时间，该值比cpu值大的时候，表明存在等待事件；
disk ：执行物理I/O次数；
query ：在意一致性检索方式获得块时，执行逻辑I/O次数；
current ：逻辑I/O次数；
rows ：此阶段，被处理或受影响的行数。
关于第一列的赘述：
Parse ：软编译和硬编译次数；
Execute ：在open和execute语句中完成的内容;
Fetch ：select中会有数据显示，在update语句中不会有数据显示。
2）摘录运行环境信息
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 51
第一行的“0”表示查询使用的是软解析（soft parse）。
优化模式是：ALL_ROWS
使用最后一行的用户ID可以获得执行时的会话信息。获得用户信息可以通过下面的SQL语句完成。
sys@ora10g> select * from all_users where user_id = 51;
USERNAME USER_ID CREATED
------------------------------ 51 2009-10-15 13:04:03
3）摘录执行计划信息
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=222957 pr=222186 pw=0 time=10686023 us)
100000000 INDEX FAST FULL SCAN PK_T (cr=222957 pr=222186 pw=0 time=100000562 us)(object id 45619)
有趣发现：通过第二行可以得到这个t表的数据量，这里显示结果是1亿。
“解剖”上面出现的几个重要参数：
cr=222957 -- 一致性读 ；pr=222186 -- 物理读 pw=0 -- 物理写
time=100000562 us -- 占用时间，单位：微妙
4）摘录等待事件
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.00 0.00
db file scattered read 14249 0.00 1.10
db file sequential read 59 0.00 0.00
5、other reference
一.Memory Dumps
1).Global Area
ALTER SESSION SET EVENTS 'immediate trace name global_area level n';
1 包含PGA
2 包含SGA
4 包含UGA
8 包含indrect memory2).Library Cache
ALTER SESSION SET EVENTS 'immediate trace name library_cache level n';
1 library cache统计信息
2 包含hash table histogram
3 包含object handle
4 包含object结构（Heap 0）3).Row Cache
ALTER SESSION SET EVENTS 'immediate trace name row_cache level n';
1 row cache统计信息
2 包含hash table histogram
8 包含object结构4).Buffers
ALTER SESSION SET EVENTS 'immediate trace name buffers level n';
1 buffer header
2 level 1 + block header
3 level 2 + block contents
4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
8 level 4 + users/waiters
9 level 5 + users/waiters
10 level 6 + users/waiters5).Buffer
ALTER SESSION SET EVENTS 'immediate trace name buffer level n';
n为某个指定block的rdba，该命令可以转储某个block在buffer中的所有版本。
6).Heap
ALTER SESSION SET EVENTS 'immediate trace name heapdump level level';
1 PGA摘要
2 SGA摘要
4 UGA摘要
8 Current call(CGA)摘要
16 User call(CGA)摘要
32 Large call(LGA)摘要
1025 PGA内容
2050 SGA内容
4100 UGA内容
8200 Current call内容
16400 User call内容
32800 Large call内容7).Sub Heap
Oracle 9.0.1版本之前
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level n';
若n为subheap的地址，转储的是subheap的摘要信息
若n为subheap的地址+1，转储的则是subheap的内容Oracle 9.2.0版本之后
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level n, addr m';
其中m为subheap的地址
n为1转储subheap的摘要，n为2转储subheap的内容
8).Process State
ALTER SESSION SET EVENTS 'immediate trace name processstate level n';
9).System State
ALTER SESSION SET EVENTS 'immediate trace name systemstate level n';
10).Error State
ALTER SESSION SET EVENTS 'immediate trace name errorstack level n';
0 Error stack
1 level 0 + function call stack
2 level 1 + process state
3 level 2 + context area11).Hang Analysis
ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level n';
12).Work Area
ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level n';
1 SGA信息
2 Workarea Table摘要信息
3 Workarea Table详细信息13).Latches
ALTER SESSION SET EVENTS 'immediate trace name latches level n';
1 latch信息
2 统计信息14).Events
ALTER SESSION SET EVENTS 'immediate trace name events level n';
1 session
2 process
3 system15).Locks
ALTER SESSION SET EVENTS 'immediate trace name locks level n';
16).Shared Server Process
ALTER SESSION SET EVENTS 'immediate trace name shared_server_state level n';
n取值为1~14
17).Background Messages
ALTER SESSION SET EVENTS 'immediate trace name bg_messages level n';
n为pid+1
二.File Dumps
1).Block
Oracle 7之前
ALTER SESSION SET EVENTS 'immediate trace name blockdump level n';
n为block的rdba
Oracle8以后
ALTER SYSTEM DUMP DATAFILE file# BLOCK block#;
ALTER SYSTEM DUMP DATAFILE file#
BLOCK MIN minimum_block#
BLOCK MAX maximum_block#;2).Tree Dump
ALTER SESSION SET EVENTS 'immediate trace name treedump level n';
n为object_id
3).Undo Segment Header
ALTER SYSTEM DUMP UNDO_HEADER 'segment_name';
4).Undo for a Transaction
ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XID xidusn xidslot xidsqn;
5).File Header
ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level n';
1 控制文件中的文件头信息
2 level 1 + 文件头信息
3 level 2 + 数据文件头信息
10 level 36).Control file
ALTER SESSION SET EVENTS 'immediate trace name controlf level n';
1 文件头信息
2 level 1 + 数据库 信息 + 检查点信息
3 level 2 + 可重用节信息
10 level 37).Redo log Header
ALTER SESSION SET EVENTS 'immediate trace name redohdr level n';
1 控制文件中的redo log信息
2 level 1 + 文件头信息
3 level 2 + 日志文件头信息
10 level 38).Redo log
ALTER SYSTEM DUMP LOGFILE 'FileName';
ALTER SYSTEM DUMP LOGFILE 'FileName'
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;其中time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;
9).Loghist
ALTER SESSION SET EVENTS 'immediate trace name loghist level n';
1dump控制文件中最早和最迟的日志历史项
>1 dump 2^n个日志历史项
1155
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
