TKPROF 与Oracle autotrace 参数含义

来源：互联网发布：java非静态方法调用编辑：程序博客网时间：2024/06/06 04:10

Oracle autotrace 参数含义可以参考官方文档Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) 也可参见道客巴巴的文档 .

recursive calls：
     Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing.
When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
db block gets：
     Number of times a CURRENT block was requested. 注：current模式中处理快的数量(从内存获/读取数据，dml语句中)
consistent gets：
     Number of times a consistent read was requestedfor a block. 注：一致读模式中处理快的数量(从内存获/读取数据，query语句中)
physical reads：
     Total number of data blocks readfrom disk. This number equals the value of "physical reads direct" plus all readsinto buffer cache.
redo size：
     Total amount of redo generated in bytes.
bytes sent via SQL*Net to client：
     Total number of bytes sent to the client from the foreground processes.
bytes received via SQL*Net from client：
     Total number of bytes received from the client over Oracle Net.
SQL*Net roundtrips to/from client：
     Total number of Oracle Net messagessent to and received from the client.
sorts (memory)：
     Number of sort operations that were performed completely in memory and did notrequire any disk writes.
sorts (disk)：
     Number of sort operations that required at least one disk write.
rows processed：
     Number of rows processed during the operation.

下对对比TKPROF跟踪文件中的参数

设置sql_trace未true,开启SQL跟踪后，打开 TKPROF的跟踪文件，通过tkprof命令行显示内容:
call count cpu elapsed disk query current rows
——- —— —— ——– —– —— ——– —-
Parse 75 0.00 0.00 2 3 2 0
Execute 81 0.00 0.00 1 1 5 1
Fetch 153 0.00 0.00 21 355 0 110
——- —— —— ——– —– —— ——– —-
total 309 0.00 0.00 24 359 7 111

disk(等同于physical reads):从硬盘读出数据的快数(物理读)
query(等同于consistent gets):从内存读出数据的快数(逻辑读)，读一致性模式(current-read)中处理的数据快
current(等同于db block gets):从内存读出数据的快数(逻辑读)，在玉壶处理期间，数据被一个外部进程改变的时候发现的读取
count: 该阶段发出的次数
cpu: 该阶段经历的cpu时间
elapsed:经历的真正时间
current mode是相对于query mode来说的，current mode产生db block gets，一般在DML操作时产生，query mode产生consistent gets(一致性读)，一般在查询时产生。他们两个总和一般称为逻辑读，logical read。
逻辑读命中率 = 1- physical reads/(consistent gets+db block gets)，关于命中率可以参考文章：Oracle中各个命中率的总结及调优笔记整理
命中率是越高越好当select 或dml执行时会查buffer cache看相应的块是否读入buffer如果没有就需要从disk读取,如果已经在内存中就可以直接使用，从内存中读取，从内存中读取的效率远比从disk读取效率要高。

TKPROF工具的使用教程：

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