zt Oracle工具篇之Tkprof

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

用法：

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

[@more@]

参数说明： 基本的使用步骤

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

---------------------------------------------------------
3.

1) SQL> alter system set timed_statistics=true;

2) 用户级自跟踪：

SQL>ALTER SESSION SET SQL_TRACE=TRUE;

SQL>ALTER SESSION SET SQL_TRACE=FALSE;

用户级DBA跟踪：(例如sys跟踪test，需要用sysdba登录)

. a).SQL>select s.USERNAME,s.SID,s.SERIAL#,s.COMMAND from v$session s

where s.USERNAME='COLM' ;

b).SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,true);

c).SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,false);

ps：9为SID，7为SERIAL#

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

转载于:http://blog.itpub.net/82387/viewspace-1016537/