使用10046 的level 1及sql trace都可以,其实这两者些时是相等的。
如下给出一个eg:
======================================================
1、设时间统计为true
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
timed_statistics boolean TRUE
2、设置sql_trace为true,打开跟踪
SQL> alter session set sql_trace=true;
Session altered.
如果想跟踪别的session,首先要找到它的sid的serial#,然后如下操作
打开:SQL>execute dbms_system.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
关闭: SQL>execute dbms_system.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
或
打开:SQL>execute dbms_system.SET_EV(sid,serial#,10046,1'');
关闭: SQL>execute dbms_system.SET_EV(sid,serial#,0,0,'');
3、随便执行一些sql
SQL> select * from ee;
SDFSDFSDF
----------
aaaaa
bbbbb
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EE TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
NEW_EMP TABLE
NEW_EMP1 TABLE
REGIONS TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSDFSDF TABLE
S_DEPT TABLE
13 rows selected.
4、关闭sql_trace
SQL> alter session set sql_trace=false;
Session altered.
5、查找生成的trace文件
SQL> show user
USER is "SYS"
SQL> select sid,paddr from v$session where username='HR'
SQL> /
SID PADDR
---------- --------
9 51A00BD8
SQL> select spid from v$process where addr='51A00BD8';
SPID
------------
6321
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
oracle@yang :~/yang1> cd $ORACLE_HOME
oracle@yang :/opt/oracle/product/9ir2> cd $ORACLE_BASE
oracle@yang :/opt/oracle> cd admin
oracle@yang :/opt/oracle/admin/ocm1> cd udump
oracle@yang :/opt/oracle/admin/ocm1/udump> ls -l *6321*
-rw-r----- 1 oracle dba 7521 2006-03-17 13:27 ocm1_ora_6321.trc
6、用tkprof解析一下,sys=no是关掉系统视图查找的显示
oracle@yang :/opt/oracle/admin/ocm1/udump> tkprof ocm1_ora_6321.trc 1.txt sys=no
TKPROF: Release 9.2.0.4.0 - Production on Fri Mar 17 13:32:31 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
7、查看结果
oracle@yang :/opt/oracle/admin/ocm1/udump>vi 1.txt
截取部分输入,可以看到在parse、execute、fetch各部分各自花费了多少时间。
select *
from
ee
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.01 0 4 0 2
(完毕)