用TKPROF工具查看trace文件
[html]
SQL> alter session set sql_trace=true;
或者
[html]
SQL> alter system set sql_trace=true;
接下来，运行脚本
[html]
begin
for i in 7369..8000
loop
update test_emp set sal = '1000' where empno=i;
end loop;
end;
关掉追踪，两种方法，匹配上面的打开方式
[html]
SQL> alter session set sql_trace=false;
或者
[html]
SQL> alter system set sql_trace=false;
想要查看trace文件，必须找到它，trace文件将在diagnostic_dest或者user_dump_dest这两种参数指定的目录下生成，两种方法：
1）通过查看user_dump_dest参数
[html]
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/orcl/udu
mp
[oracle@RedHat ~]$ cd /u01/app/oracle/admin/orcl/udump
[oracle@RedHat udump]$ ll
total 36
-rw-r----- 1 oracle oinstall 1154 Aug 23 10:09 orcl_ora_20965.trc
-rw-r----- 1 oracle oinstall 7688 Aug 23 10:09 orcl_ora_20970.trc
-rw-r----- 1 oracle oinstall 23813 Aug 23 10:09 orcl_ora_21451.trc
2）通过查询
[html]
SELECT a.VALUE
|| b.symbol
|| c.instance_name
|| '_ora_'
|| d.spid
|| '.trc' trace_file
FROM (SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol
FROM v$parameter
WHERE NAME = 'user_dump_dest') b,
(SELECT instance_name
FROM v$instance) c,
(SELECT spid
FROM v$session s, v$process p, v$mystat m
17 WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
18 /
TRACE_FILE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/udump/orcl_ora_21451.trc
接下来用TKPROF工具将trace文件转换成txt文件
[html]
[oracle@RedHat ~]$ tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_21451.trc /u01/app/oracle/admin/orcl/udump/orcl_ora_21451.txt
TKPROF: Release 10.2.0.1.0 - Production on Fri Aug 23 10:21:04 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
已经生成
[html]
[oracle@RedHat orcl]$ cd udump
[oracle@RedHat udump]$ ll
total 60
-rw-r----- 1 oracle oinstall 1276 Aug 23 10:19 orcl_ora_20965.trc
-rw-r----- 1 oracle oinstall 7688 Aug 23 10:09 orcl_ora_20970.trc
-rw-r----- 1 oracle oinstall 23813 Aug 23 10:09 orcl_ora_21451.trc
-rw-r--r-- 1 oracle oinstall 22541 Aug 23 10:21 orcl_ora_21451.txt
查看trace文件中的以下记录
[html]
begin
for i in 7369..8000
loop
update test_emp set sal = '1000' where empno=i;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.01 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
**************************************************