tkprof使用

最新推荐文章于 2024-09-25 11:06:23 发布

ciyunbiao8310

最新推荐文章于 2024-09-25 11:06:23 发布

阅读量99

点赞数

文章标签：数据库

tkprof使用说明
tkprof: stands for transient kernel profiler.
tkprof 系统级调用。

使用步骤：
一.开启10046事件
1：修改当前session (For Current Session)
开启： SQL> alter session set sql_trace=true;
SQL> execute dbms_session.set_sql_trace(true);
关闭： SQL> alter session set sql_trace=false;
SQL> execute dbms_session.set_sql_trace(false);

2：修改任何session (For Any Session)
开启： SQL> execute dbms_system.set_sql_trace_in_session(session_id,serial_id,true);
关闭： SQL> execute dbms_system.set_sql_trace_in_session(session_id,serial_id,true);

3：数据库级修改(For an instance,set the following parameter.)
开启：SQL_TRACE=TRUE
关闭：SQL_TRACE=FALSE

二：使用tkprof
=======================================用例===========================================
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as thinker

SQL> alter session set sql_trace=true;

Session altered

SQL> select count(*) from test_tkprof;

COUNT(*)
----------
1000000

SQL> alter session set sql_trace=false;

Session altered

/*
查找trc文件
*/
SQL>
SQL> SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
2         p.spid || '.trc' AS "trace_file_name"
3    FROM (SELECT p.spid
4            FROM v$mystat m, v$session s, v$process p
5           WHERE m.statistic# = 1
6             AND s.SID = m.SID
7             AND p.addr = s.paddr) p,
8         (SELECT t.INSTANCE
9            FROM v$thread t, v$parameter v
10           WHERE v.NAME = 'thread'
11             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
12         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

trace_file_name
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\THINKER\UDUMP/thinker_ora_6004.trc

格式化trc文件
C:\Documents and Settings\administor>tkprof E:\ORACLE\PRODUCT\10.2.0\ADMIN\THINK
ER\UDUMP/thinker_ora_6004.trc C:\test_tbprof3 sys=no

TKPROF: Release 10.2.0.1.0 - Production on 星期日 2月 26 21:27:05 2012

C:\Documents and Settings\administor>

三：查看结果

TKPROF: Release 10.2.0.1.0 - Production on 星期日 2月 26 21:27:05 2012

Trace file: E:\ORACLE\PRODUCT\10.2.0\ADMIN\THINKER\UDUMP/thinker_ora_6004.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: 61
********************************************************************************

begin :id := sys.dbms_transaction.local_transaction_id; end;

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

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

select 'x'
from
dual

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

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

Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=14 us)

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

select count(*)
from
test_tkprof

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        1      0.35       0.35          0       1891          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.35       0.35          0       1891          0           1

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

Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=1891 pr=0 pw=0 time=354027 us)
1000000   INDEX FAST FULL SCAN SYS_C005943 (cr=1891 pr=0 pw=0 time=4000106 us)(object id 54705)

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

alter session set sql_trace=false

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        0      0.00       0.00          0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        2      0.00       0.00          0          0          0           0

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

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        8      0.00       0.00          0          0          0           0
Execute      9      0.01       0.00          0          0          0           4
Fetch        3      0.35       0.35          0       1891          0           3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       20      0.37       0.35          0       1891          0           7

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

    9 user SQL statements in session.
    0 internal SQL statements in session.
    9 SQL statements in session.
********************************************************************************
Trace file: E:\ORACLE\PRODUCT\10.2.0\ADMIN\THINKER\UDUMP/thinker_ora_6004.trc
Trace file compatibility: 10.01.00
Sort options: default

       2 sessions in tracefile.
       9 user SQL statements in trace file.
       0 internal SQL statements in trace file.
       9 SQL statements in trace file.
       5 unique SQL statements in trace file.
     107 lines in trace file.
      37 elapsed seconds in trace file.

结果说明：
任何一条SQL，都应该至少包括3个步骤，对于下面call列。
*分析（Parse）：SQL分析阶段
*执行（Execute）：SQL执行阶段
*数据提取（Fetch）：数据的提取阶段
横向的列除了call（调用）之外，还包括以下信息：
* count    ：计数器，表示当前的操作被执行多少次
* cpu      ：当前的操作消耗cpu的时间（单位秒）。
* elapsed ：当前操作一共用时多少（包括cpu和等待时间）
* disk     ：当前操作的物理读（磁盘i/o次数）
* query    ：当前操作一致性读取方式读取的数据块数（通常是查询使用的方式）
* current ：当前操作的current的方式读取的数据块数（通常是修改数据使用的方式）
* rows     ：当前操作处理的数据记录数

可以直接设置tracefile的名字
SQL> alter session set tracefile_identifier='thinker';

目前未明白为什么为有select * from dual的语句出现。

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

转载于:http://blog.itpub.net/25146649/viewspace-717179/