跟踪文件由各个后台进程生成，警报日志中记录关键操作包括:
·所有启动和关闭命令，包括中间命令，如alter database mount
·实例的所有内部错误(ORA-600错误，只能报告给Oracle Support解决)
·任何检测到的数据文件块损坏情况
·任何已经发生的死锁
·影响数据库物理结构的所有操作，如创建或重命名数据文件和联机重做日志
·调整内部参数值的alter system命令
·所有日志开关和日志归档文件
以Oracle死锁为例：
确定警告日志文件的存放目录
SQL>show parameter dump_dest
详细的trc日志如下：
*** 2015-12-15 08:47:31.756
DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock: Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000a000d-0009fdf2 31 745 X 34 817 X
TX-000b0012-00064744 34 817 X 31 745 X session 745: DID 0001-001F-001BA3CC session 817: DID 0001-0022-000E48FD
session 817: DID 0001-0022-000E48FD session 745: DID 0001-001F-001BA3CC Rows waited on:
Session 745: obj - rowid = 0001B049 - AAAbBJAAHAAEN4SAAF
(dictionary objn - 110665, file - 7, block - 1105426, slot - 5)
Session 817: obj - rowid = 0001B049 - AAAbBJAAHAAEN4SAAE
(dictionary objn - 110665, file - 7, block - 1105426, slot - 4) ----- Information for the OTHER waiting sessions -----
Session 817:
sid: 817 ser: 32301 audsid: 7129480 user: 93/LC019999
flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 34 O/S info: user: SYSTEM, term: PERFTESTDB66, ospid: 21068
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: Administrator, term: WANGZG-INSPUR, ospid: 2736:11836
machine: WORKGROUP\WANGZG-INSPUR program: plsqldev.exe
application name: PL/SQL Developer, hash value=1190136663
action name: SQL 窗口 - 新建, hash value=2127054360
current SQL:
update tableName
set name = 'tkk1215'
where xxxxxxxxxxxxxxx ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=01yxw4cmbqgg3) -----
update tableName
set name = 'xyy1215'
where xxxxxxx
===================================================
SQL语句跟踪
1.用户级自我跟踪
alter session set sql_trace=true;
在同一个session中执行SQL语句，将会产生跟踪文件sid_ora_spid.trc(spid为系统进程id):
/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_spid.trc
2.系统级跟踪
alter system set sql_trace=true scope=memory;
在任何session中执行SQL语句，产生跟踪文件sid_ora_spid.trc(spid为系统进程id):
/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_spid.trc
alter system set sql_trace=false scope=memory;
3.用户级的DBA跟踪
使用Oracle的PL/SQL包DBMS_SYSTEM
(1)从session性能视图中查出用户的sid和serial#.
select s.username,s.sid,s.serial#,p.spid,s.program
from v$session s,v$process p
where s.paddr = p.addr
and s.username is not null
and p.background is null;
USERNAME SID SERIAL# SPID PROGRAM
------------------------------ ---------- ---------- ------------------------ -----------------------------
SYSTEM 13 35114 9172 plsqldev.exe
SYSTEM 248 32295 9225 plsqldev.exe
SYS 14 36202 9111 sqlplus@gymvm (TNS V1-V3)
(2)跟踪用户会话
exec sys.dbms_system.set_sql_trace_in_session(248, 32295, true);
在任何id为248的session中执行SQL语句，产生跟踪文件sid_ora_spid.trc(spid为系统进程id):
/u01/app/oracle/diag/rdbms/sid/sid/trace/sid_ora_9225.trc
(3)停止跟踪
exec sys.dbms_system.set_sql_trace_in_session(248, 32295, false);
(4) 10046/10053是Oracle提供的内部事件，是对SQL_TRACE的增强
10046事件可以设置以下四个级别:
1 - 启用标准的SQL_TRACE功能,等价于sql_trace
4 - Level 1 加上绑定值(bind values)
8 - Level 1 + 等待事件跟踪
12 - Level 1 + Level 4 + Level 8
alter session set tracefile_identifier = 'tkk0114';
alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off';
exec sys.dbms_system.set_ev(9, 437, 10046, 12, 'tkk0114');
exec sys.dbms_system.set_ev(9, 437, 10046, 0, 'tkk0114');
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10053 trace name context off';
如果你用的是其他用户来执行dbms_system,则可以这样：
sqlplus 'sys/pass as sysdba'
SQL>grant execute on dbms_system to <your_username>;
4.SQL_TRACE跟踪SQL语句执行情况的内容
·解析、执行、取数据的计数
·CPU时间和占用时间
·物理读和逻辑读
·处理行数目
·所解析的用户名
·每次提交和回滚的情况.
5.格式化10046跟踪文件(tkprof)
使用tkprof程序可以将跟踪文件转化成可以阅读的文本文件，tkprof语法:
tkprof tracefile outputfile [explain=user/password] [table=schema.tablename]
[print=integer] [insert= ] [sys= ] [sort= ] ...
$ tkprof tracefile outputfile