怎样使用TKPROF工具——《Oracle高效设计》学习笔记

最新推荐文章于 2017-05-02 10:46:48 发布

swordmanwk

最新推荐文章于 2017-05-02 10:46:48 发布

阅读量3.3k

点赞数

分类专栏： oracle 文章标签： oracle 工具 table session statistics access

本文链接：https://blog.csdn.net/swordmanwk/article/details/6200774

版权

oracle 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

Oracle具有打开一个相当低层的跟踪功能的能力。

一旦启用跟踪文件，Oracle将会记录许多有价值的信息。

TKPROF工具就是用于将这些跟踪文件转换成我们容易阅读的格式。

1.启用跟踪功能

alter session set timed_statistics = true;
alter session set events '10046 trace name context forever, level 12';

2.生成跟踪文件

随便运行一个sql：

select count(*) from all_objects

获取跟踪文件名

（solaris下运行此sql：）

select c.value || '/ORA' || to_char(a.spid,'fm00000') || '.trc'
from v$process a, v$session b, v$parameter c
where a.addr = b.paddr
and b.audsid = sys_context('userenv','sessionid')
and c.name = 'user_dump_dest'
/

（Windows下或linux下运行此sql：）

select rtrim(c.value,'/') || '/' || d.instance_name ||
'_ora_' || ltrim(to_char(a.spid)) || '.trc'
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = sys_context('userenv','sessionid')
and c.name = 'user_dump_dest'
/

我只在Windows平台下作过测试

在Windows开始菜单选择运行

在运行窗口中输入cmd

3.生成TKPROF报告

然后键入 tkprof D:/ORACLE/PRODUCT/10.2.0/ADMIN/NOC/UDUMP/...trc e:/tk.prf

tkprof 后面跟跟踪文件名（上一步查询中查到的），以及要生成的包括路径的文本文件名e:/tk.prf

这样就会在e盘根目录下创建一个tk.prf的文本文件，打开这个文件就可以看到刚才那个查询的报告。

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

select count(*)
from
all_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.04       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.26       1.27          0      61820          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      1.31       1.35          0      61820          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows     Row Source Operation
------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=93333 pr=0 pw=0 time=4050871 us)
49295   FILTER (cr=93333 pr=0 pw=0 time=8775503 us)
50903    HASH JOIN (cr=615 pr=0 pw=0 time=815455 us)
     56     TABLE ACCESS FULL USER$ (cr=5 pr=0 pw=0 time=275 us)
50903     TABLE ACCESS FULL OBJ$ (cr=610 pr=0 pw=0 time=205115 us)
   1707    TABLE ACCESS BY INDEX ROWID IND$ (cr=4478 pr=0 pw=0 time=70606 us)
   2236     INDEX UNIQUE SCAN I_IND1 (cr=2238 pr=0 pw=0 time=29142 us)(object id 39)
22672    NESTED LOOPS (cr=56727 pr=0 pw=0 time=939451 us)
22789     INDEX RANGE SCAN I_OBJAUTH1 (cr=56727 pr=0 pw=0 time=310848 us)(object id 103)
22672     FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=215016 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=45 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=52 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=42 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=80 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=35 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=52 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=42 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=87 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=94 us)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=60 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=107 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=68 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=39 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=69 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=68 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=59 us)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      1    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=66 us)
      0    FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
     25    VIEW (cr=0 pr=0 pw=0 time=726 us)
     25     FAST DUAL (cr=0 pr=0 pw=0 time=295 us)

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait Total Waited
----------------------------------------   Waited ---------- ------------
SQL*Net message to client                       2        0.00          0.00
SQL*Net message from client                     2        0.09          0.13
********************************************************************************