[@more@]

[oracle@test52 udump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 11 15:48:40 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
138 58815 SYS
142 64007 SYS
146 8447 SCOTT

找到被跟踪用户会话所对应的操作系统ID号，即操作系统pid

SQL> select spid from v$process where addr=(select paddr from v$session
2 where sid=&1 and serial#=&2);
Enter value for 1: 146
Enter value for 2: 8447
old 2: where sid=&1 and serial#=&2)
new 2: where sid=146 and serial#=8447)

SPID
------------
28563

SQL> exec dbms_system.set_sql_trace_in_session(146,8447,true);

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(146,8447,false);

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

进入udump目录
[oracle@test52 udump]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall 0 May 11 15:48 b.txt
-rw-r----- 1 oracle oinstall 1737 May 11 15:34 street3_ora_27452.trc
-rw-r----- 1 oracle oinstall 1234 May 11 15:51 street3_ora_28563.trc
[oracle@test52 udump]$ tkprof street3_ora_28563.trc abc.txt

TKPROF: Release 10.2.0.4.0 - Production on Wed May 11 15:51:51 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

[oracle@test52 udump]$ cat abc.txt |more

TKPROF: Release 10.2.0.4.0 - Production on Wed May 11 15:51:51 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Trace file: street3_ora_28563.trc
Sort options: default

********************************************************************************
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
********************************************************************************

select count(*)
from
salgrade

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 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

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 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 1

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
********************************************************************************
Trace file: street3_ora_28563.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
27 lines in trace file.
0 elapsed seconds in trace file.

[oracle@test52 udump]$ exit
exit

Script done on Wed 11 May 2011 03:52:21 PM CST