tkprof_oracle之工具-CSDN博客

Tkprof工具可用来格式化sql trace产生的文件，让你更容易看懂trace的内容

用法：

tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

参数说明：

tracefile：你要分析的trace文件

outputfile：格式化后的文件

explain=user/password@connectstring

table=schema.tablename

这两个参数是一起使用的，通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中

注意，该table必须是数据库中不存在的，如果存在会报错

print=n：只列出最初N个sql执行语句

insert=filename：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中

sys=no：过滤掉由sys执行的语句

record=filename：可将非嵌套执行的sql语句过滤到指定的文件中去

waits=yes|no：是否统计任何等待事件

aggregate=yes|no：是否将相同sql语句的执行信息合计起来，默认为yes

sort=option：设置排序选项，选项如下：

prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor

可根据自己的需要设置排序

举例：（trace文件可用sql trace去产生，在此略过）

1.列出前2条sql语句的执行情况：

C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt print=2

2.将数据保存到数据库：

C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt insert=c:\insert.sql

执行后会在c:\产生insert.sql文件，执行该文件即可将数据保存到数据库，以下为insert.sql部分内容：

REM Edit and/or remove the following CREATE TABLE
REM statement as your needs dictate.
CREATE TABLE tkprof_table
(
date_of_insert DATE
,cursor_num NUMBER
,depth NUMBER
,user_id NUMBER
,parse_cnt NUMBER
,parse_cpu NUMBER
,parse_elap NUMBER
,parse_disk NUMBER
,parse_query NUMBER
,parse_current NUMBER
,parse_miss NUMBER
,exe_count NUMBER
,exe_cpu NUMBER
,exe_elap NUMBER
,exe_disk NUMBER
,exe_query NUMBER
,exe_current NUMBER
,exe_miss NUMBER
,exe_rows NUMBER
,fetch_count NUMBER
,fetch_cpu NUMBER
,fetch_elap NUMBER
,fetch_disk NUMBER
,fetch_query NUMBER
,fetch_current NUMBER
,fetch_rows NUMBER
,ticks NUMBER
,sql_statement LONG
);
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 61, 1, 0, 418, 0, 0, 0, 1
, 1, 15625, 1435, 0, 0, 0, 0, 0
, 4, 0, 4417, 0, 24, 0, 36, 13450151
, ’select * from tblinventoryhistory
‘);

3.提取sql执行语句：

C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt record=sqlstr.sql

sqlstr.sql中的内容：

alter session set sql_trace=true ;
alter session set events ‘10046 trace name context forever,level 12′ ;
select * from tblinventoryhistory ;
select * from tblorder ;
select * from tblproduct ;
select * from tbluser ;
select * from tblroute ;

4.产生执行计划：

C:\>tkprof c:\oc_ora_2892.trc c:\ff.txt explain=ocuser/ocuser table=ocuser.test1

在产生的ff.txt文件中会体现其执行计划：

Rows Execution Plan
——- —————————————————
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF ‘TBLROUTE’

小技巧：

1.如何查找你产生的trace文件：

可用#eygle写的脚本去查找：

SQL> select

2 d.value||’/'||lower(rtrim(i.instance, chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name

3 from

4 ( select p.spid

5 from sys.v$mystat m,sys.v$session s,sys.v$process p

6 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,

7 ( select t.instance from sys.v$thread t,sys.v$parameter v

8 where v.name = ‘thread’ and (v.value = 0 or t.thread# = to_number(v.value))) i,

9 ( select value from sys.v$parameter where name = ‘user_dump_dest’) d

10 /

TRACE_FILE_NAME
——————————————————————————–
/opt/oracle/admin/hsjf/udump/hsjf_ora_1026.trc

2.sort选项可同时用多个，做法是用括号括起来，中间用逗号分割：

C:\>tkprof c:\oc_ora_2892.trc c:\cc.txt sort=(prsela, exeela, fchela)
注意：最后排序是按照各个选项的数字之和进行排序，类似于order by (sort1+sort2+sort3)，而不是order by sort1,sort2,sort3

oracle性能优化：如何讀懂tkprof

感覺這方面的資料都比較少，目前知道的：（將陸續整理）
CALL：每次SQL语句的处理都分成三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
COUNT:这个语句被parse、execute、fetch的次数。
CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。
DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。

A、query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
B、Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse
C、rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，增加了数据在客户端和服务器之间的往返次数。
D、disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
E、elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
F、cpu　Or　elapsed 太大表示执行时间过长，或消耗了了大量的CPU时间，应该考虑优化
G、执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少

EG：

alter session set max_dump_file_size=unlimited;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
select 'Hello, world; today is '||sysdate from dual; exit;

tkprof card_ora_13226.trc trace.txt print=100 record=sql.txt sys=no

然后查年trace.txt就是分析内容了

ORACLE 系统中的性能调整方法主要分为两类：应用系统的调整和数据库系统的性能调整。应用系统的调整关注的焦点在应用设计相关的方面，比如：事务规则的调整、数据的设计、应用程序的设计，ORACLE系统的性能与应用系统的设计密切相关，性能调整90％的项目都集中在应用程序的调整。

在应用程序调整结束后就将进行数据库方面的调整。在进行数据库的性能调整之前，要做的一项关键工作是利用工具进行SQL语句的性能分析：TKPROF工具、解释执行计划、AUTOTRACE工具。应用程序中低效的SQL语句往往是导致整个系统问题所在。本篇文章将就TKPROF工具进行讨论和应用分析。

TKPROF工具

TKPROF工具（Trace Kernel Profile）是用于格式化用户跟踪文件的工具，它是个独立的工具，可以在操作系统下直接执行。

$tkprof ora_12558.trc trace.txt

（1）生成用户跟踪文件

用户跟踪文件包括所有的用户发出的SQL语句的信息，包括：每条语句的执行时间和占用资源的数量。因为跟踪文件的信息是没有格式化的原始信息，所以用TKPROF工具来转换信息，使其易于理解。

（2）用TKPROF格式化用户跟踪文件

$trprof ora_12558.trc trace.txt

这个例子将会格式化ora_12558.trc跟踪文件，并生成格式化的转换文件trace.txt。除了命令的头两个参数代表输入文件名和输出文件名，还有其他的一些附加参数用于指定期望的信息。这些选项如下所示：

选项定义和用法

EXPLAN 为跟踪文件中的每条语句生成一个执行计划。产生的结果保存在指定模式的PLAN_TABLE中，该模式在命令中给出，即命令中必须要给出用户名和口令。

TABLE 指定执行计划保存的表名称，替换缺省的PLAN_TABLE表名。

SYS 表明是否格式化的文件包括递归的SQL语句。

SORT 表明格式化的文件信息排序的规则。

RECORD 指定跟踪文件中的SQL语句写入哪一个文件。使你能将SQL语句与附加的资源使用信息分开。

PRINT 指定格式化的跟踪文件最多可以写入的SQL语句的数量。

INSERT 执行时创建一个脚本，用于生成一个TKPROF_TABLE表。

AGGREGATE 决定是否将多个执行相同语句分析的结果合并。

例1：$tkprof ora_1234.trc trace.txt sys=no explain=joe/sidney

该命令将忽略原始文件中的递归SQL；在格式化的跟踪文件中包括每条SQL语句的执行计划PLAN_TABLE依赖于用户JOE模式。

例2：$tkprof ora_1234.trc trace.txt print=10 record=sql.txt

该命令将翻译跟踪文件ora_1234.trc并保存头10条SQL语句到输出文件trace.txt；创建一个文件sql.txt，用于保存原始跟踪文件的头10条SQL语句。

例3：$tkprof ora_1234.trc trace.txt insert=trace.sql sort=(fchrow)

该命令将翻译跟踪文件ora_1234.trc并保存到输出文件trace.txt；创建第二个文件trace.sql，用于保存生成TKPORF_TABLE 表的SQL命令。如果没有排序参数fchrow,TKPROF将会按照执行的顺序输出到trace.txt文件中去。

（3）TKPROF排序参数

TKPROF的每个排序参数都与SQL语句的三个过程相关：分析、执行和获取数据。

在分析阶段进行执行SQL的准备工作，如进行语句的检查、循环纠错、决定要使用对象的用户权利。与分析阶段相关的排序参数有：

选项   定义和用法

PRSCNT   语句被分析的次数。

PRSCPU   分析这条语句花费的CPU时间。

PRSELA   分析这条语句花费的总时间。

PRSDSK   分析这条语句时读磁盘的次数。

PRSMIS   分析这条语句时从SGA读回滚块的数量。

PRSCU   分析这条语句时从SGA读数据块的数量。

在SQL语句的执行阶段，执行语句中指定的动作。例如：从表中选择、插入、更新、删除数据。创建用户、表空间、数据文件等。与执行阶段相关的排序参数有：

选项   定义和用法

EXECNT   语句被执行的次数。

EXECPU   执行这条语句花费的CPU时间。

EXEELA   执行这条语句花费的总时间。

EXEDSK   执行这条语句时读磁盘的次数。

EXEMIS   执行这条语句时从SGA读回滚块的数量。

EXECU   执行这条语句时从SGA读数据块的数量。

如果在SQL语句中包括SELECT语句，SQL还将执行SQL语句中的最后一步，即fetch操作。fetch操作发生时SQL语句返回的结果集被送回用户。与fetch阶段相关的排序参数有：

选项   定义和用法

FCHCNT   语句获取时的行数。

FCHCPU   获取数据花费的CPU时间。

FCHELA   获取数据花费的总时间。

FCHDSK   获取数据时读磁盘的次数。

FCHQRY   获取数据时从SGA读回滚块的数量。

FCHROW   被语句获取的行数。

FCHCU   获取数据时从SGA读数据块的数量。

（4）用TKPROF翻译已格式化的跟踪文件

用户的跟踪文件被TKPROF格式化后，结果文件中的内容将会较好地用于性能分析。一个已经被TKPROF分析的格式化文件的例子如附图。

从这个跟踪文件可以发现，第三条语句被分析了一次，执行了五次，并执行了250此获取操作。这个过程耗费了CPU 0.18秒的时间，整个语句执行花费了0.20秒的时间。它需要从磁盘读取54个数据块，从SGA读取266个数据块并返回245行数据。在最后一行显示所有统计信息。

（5）标记需要调整的语句

使用TKPROF格式化的文件很容易看出需要调整的SQL语句。查找的依据是：

*耗费大量的CPU资源

*花费较长的时间进行SQL分析、执行和获取

*从磁盘读大量的数据块，而从SGA区读的数据很少

*访问大量的数据块但只返回一点数据

一旦找到这些语句就可以用执行计划工具来进一步决定为什么这些语句性能这么低了。

二. 对Tkprof命令输出的解释：

首先解释输出文件中列的含义：
CALL：每次SQL语句的处理都分成三个部分
Parse：这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute：这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。
Fetch：返回查询语句中所获得的记录，这步只有select语句会被执行。
COUNT:这个语句被parse、execute、fetch的次数。
CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
ELAPSED：这个语句所有消耗在parse、execute、fetch的总的时间。
DISK：从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
QUERY：在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
CURRENT：在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是在execute这步。