使用 Tkprof 分析 ORACLE 跟踪文件2
四．举例说明：
--启用SQL_TRACE
SQL> alter session set sql_trace=true;
会话已更改。
SQL> select count(*) from bigtab;
COUNT(*)
----------
1922423
--启用timed_statistics
SQL> alter session set timed_statistics=true;
会话已更改。
SQL> select count(*) from bigtab;
COUNT(*)
----------
1922423
SQL> alter session set sql_trace =false;
会话已更改。
--查询此会话产生的TRACE文件
SQL> select username,sid,serial# from v$session where username='SYS';
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYS 19 2518
SQL> select 'orcl_ora_'||spid||'.trc' from v$process where addr = (select paddr from v$session where sid=19);
'DSS_ORA_'||SPID||'.TRC'
------------------------------------
orcl_ora_7240.trc
也可以通过下面的函数来获取当前的trace文件：
create or replace function gettracename return varchar2 is
v_result varchar2(200);
begin
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' into v_result
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
return v_result;
end gettracename;
运行SQL> select gettracename() from dual;即可
SQL> select gettracename() from dual;
GETTRACENAME()
--------------------------------------------------------------------------
d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_7240.trc
--使用tkprof分析trace文件
C:/Users/Administrator.DavidDai>tkprof d:/app/administrator/diag/rdbms/orcl/orcl
/trace/orcl_ora_7240.trc D:/orcl_ora_7240.txt aggregate=yes sys=no waits=yes sor
t=fchela
TKPROF: Release 11.2.0.1.0 - Development on 星期五 5月 28 16:48:49 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
--tkprocf输出了以下文件：D:/orcl_ora_7240.txt
TKPROF: Release 11.2.0.1.0 - Development on 星期五 5月 28 16:48:49 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_7240.trc
Sort options: fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
