tkprof使用一例

最新推荐文章于 2025-11-26 13:50:39 发布

转载最新推荐文章于 2025-11-26 13:50:39 发布 · 113 阅读

文章标签：

#数据库

tkprof用来使TRACE文件可读

会话1：

SQL> conn /as sysdba

Connected.

SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from t;

----------

10001

SQL> update t t1 set x=x+1;

1 row updated.

SQL> update t t2 set x=x+1;

1 row updated.

会话2：格式化刚产生的TRACE文件。

[oracle@oraclelinux udump]$ rm -rf *

[oracle@oraclelinux udump]$ ll

total 8

-rw-r----- 1 oracle oinstall 2444 Apr 24 15:17 testenv01_ora_23302.trc

[oracle@oraclelinux udump]$ tkprof testenv01_ora_23302.trc tkprof1.txt

TKPROF: Release 10.2.0.1.0 - Production on Tue Apr 24 15:18:02 2012

[oracle@oraclelinux udump]$ ll

total 20

-rw-r----- 1 oracle oinstall 2444 Apr 24 15:17 testenv01_ora_23302.trc

-rw-r--r-- 1 oracle oinstall 6237 Apr 24 15:18 tkprof1.txt

[oracle@oraclelinux udump]$ more tkprof1.txt

TKPROF: Release 10.2.0.1.0 - Production on Tue Apr 24 15:18:02 2012

Trace file: testenv01_ora_23302.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

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

alter session set sql_trace=true

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

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

select *

from

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 3 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

------- ---------------------------------------------------

1 TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=694 us)

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

update t t1 set x=x+1

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.02 0.02 0 0 0 0

Execute 1 0.00 0.00 0 3 3 1

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.02 0.02 0 3 3 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

------- ---------------------------------------------------

0 UPDATE T (cr=3 pr=0 pw=0 time=3645 us)

1 TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=348 us)

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

update t t2 set x=x+1

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0

Execute 1 0.00 0.00 0 3 1 1

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.01 0.01 0 3 1 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows Row Source Operation

------- ---------------------------------------------------

0 UPDATE T (cr=3 pr=0 pw=0 time=1627 us)

1 TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=859 us)

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 3 0.03 0.03 0 0 0 0

Execute 4 0.00 0.00 0 6 4 2

Fetch 2 0.00 0.00 0 3 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 9 0.04 0.04 0 9 4 3

Misses in library cache during parse: 2

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

4 user SQL statements in session.

0 internal SQL statements in session.

4 SQL statements in session.

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

Trace file: testenv01_ora_23302.trc

Trace file compatibility: 10.01.00

Sort options: default

1 session in tracefile.

4 user SQL statements in trace file.

0 internal SQL statements in trace file.

4 SQL statements in trace file.

4 unique SQL statements in trace file.

49 lines in trace file.

29 elapsed seconds in trace file.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15720542/viewspace-722131/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15720542/viewspace-722131/