使用sql trace工具和tkprof来跟会话-阿里云开发者社区

使用sql trace工具和tkprof
1.给跟踪文件设置初始化参数
2.对你所有跟踪的会话和程序启用sql trace功能.这个步骤会将这个程序使用的sql语句的统计输出到一个跟踪文件中.
3.运行tkprof来翻译第二步输出的跟踪文件成为一个可读的文件袋.这一步也可以创建一个sql脚本在数据库中存储统计
4.解释说明第三步生成的输出文件.
5.可以在第三步运行一个sql脚本将统计存储在数据库

给跟踪文件设置初始化参数
当对会话启用sql trace功能后,oracle会对这个会话跟踪的sql语句生成一个跟踪文件.跟踪文件包括统计信息.当对整个实例启用sql trace时,oracle会对每一个进程生成一个跟踪文件.在启用sql trace功能前需要做以下检查.
1.检查timed_statistics,max_dump_file_size和user_dump_dest初始化参数
timed_statistics:这个参数是用来是否启用或禁用收集时间统计,象cpu和运行时间,通过sql trace功能也能收集动态性能表中的各种统计.这个参数缺省值是false禁用收集时间统计.当为true时启用时间统计收集,启用时间统计收集会对低级别的操作造成额外的调用时间,这是一个动态参数.也是一个会话级别的参数.

max_dump_file_size:当在实例级别启用sql trace功能时每一个调用都会生成一个操作系统文本格式的文本文件.这些文件的大小是由这个参数的参数值决定的.这个参数缺省值是500,如果你发现一个跟踪输出被截段了,那么在生成另一个跟踪文件之前可以增加这个参数值.这是一个动态参数，也是一会话级别的参数.

user_dump_dest:这个参数是给跟踪文件指定一个输出目录.这个参数的缺省值是系统目录.这个参数可以使用alter system set user_dump_dest=newdir语句来修改，这是一个动态参数,也是一个会话级别的参数.

设计一种方法来识别输出的跟踪文件
要确定你知道怎样通过名字来识别跟踪文件.oracle通过user_dump_dest参数来写这些跟踪文件到你指定的这个目录下.然而这个目录下会很快就会使用生成的名字产生几百个跟踪文件,使用这些跟踪文件很难找到他建军这些跟踪文件和会话或过程.在你的程序中可以使用象select 'program_name' from dual的语句加入到跟踪文件中.那么就能跟踪这些文件是由哪个过程创建的.
你也能设置tracefile_identifier初始化参数来指定一个客户标识符作业跟踪文件名的一部分.例如,可以简单加一个my_trace_id到这个跟踪文件名进行简单的识别.
alter session set tracefile_identifier='my_trace_id';

如果操作系统保留的多个版本的文件,那么要确保版本限制高足以容纳你所期望sql trace所生成的跟踪文件数据量.

生成的跟踪文件除了你自己还可能由一个操作系统用户进行操作.在你使用tkprof来格式化它前之前这个操作系统用户必须确保你能使用这些跟踪文件.

启用sql trace功能
对会话启用sql trace可以使用下面的任何一种方式来执行
1.dbms_session.set_sql_trace
2.alter session set sql_trace=true;
注意:因为运行sql trace功能会增加系统开销，仅仅只有当调整sql语句时才启用sql trace,当调整完以后禁用sql trace建议使用 dbms_session或dbms_monitor包来对会话或实例启用sql跟踪来代替alter 语句.
为了对会话禁用sql trace可以执行以下语句
alter session set sql_trace=false;

当应用程序从oracle断开后对这个会话的sql trace也会自动禁用

也能通过设置sql_trace初始化参数来对整个实例来启用sql trace功能.
sql_trace=true
在更新这个参数后实例要重新启动.当实例启用sql trace会收集所有会话的统计.可以设置sql_trace=false来禁用sql trace

使用tkprof来格式化跟踪文件
tkprof能访问通过sql trace功能产生的跟踪文件生成一个格式的输出文件.tkprof也能用来生成执行计划.
在sql trace功能已经生成了一些跟踪文件夹后你可以执行以下操作
1.在每个单独的跟踪文件上执行tkprof,为每一个会话的生成一个格式输出文件.
2.能联合所有的跟踪文件，运行tkprof对整个实例生成一个格式化输出文件.
3.运行trcsess命令行工具来联合多个跟踪文件的信息然后运行tkprof生成格式化的结果.
在跟踪文件中tkprof不支持commit和rollback命令.

tkprof格式化输出的例子如下
select c.*,
       (select d.corp_join_no
          from bs_corp_insure d
         where d.corp_id = b.corp_id
           and d.insr_detail_code = c.insr_detail_code) corp_join_no_dw
from bs_corp_pres b, bs_pres_insur c
where b.indi_id = c.indi_id
   and b.corp_id = c.corp_id and b.corp_id=:"SYS_B_0"

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         17          0           2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.01       0.00          0         17          0           2

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 57

Rows     Row Source Operation
------- ---------------------------------------------------
      2 TABLE ACCESS BY INDEX ROWID BS_CORP_INSURE (cr=6 pr=0 pw=0 time=40 us)
      2   INDEX RANGE SCAN IDX_BS_CORP_INSURE_1 (cr=4 pr=0 pw=0 time=26 us)(object id 230781)
      2 TABLE ACCESS BY INDEX ROWID BS_PRES_INSUR (cr=11 pr=0 pw=0 time=143 us)
      4   NESTED LOOPS (cr=9 pr=0 pw=0 time=307 us)
      1    INDEX RANGE SCAN PK_BS_CORP_PRES (cr=3 pr=0 pw=0 time=44 us)(object id 230787)
      2    BITMAP CONVERSION TO ROWIDS (cr=6 pr=0 pw=0 time=66 us)
      1     BITMAP AND (cr=6 pr=0 pw=0 time=63 us)
      1      BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=28 us)
      2       INDEX RANGE SCAN INDX_BS_PRES_INSUR_INDI_ID (cr=3 pr=0 pw=0 time=13 us)(object id 231131)
      1      BITMAP CONVERSION FROM ROWIDS (cr=3 pr=0 pw=0 time=21 us)
      2       INDEX RANGE SCAN INDX_BS_PRES_INSUR_CORP_ID (cr=3 pr=0 pw=0 time=16 us)(object id 231129)

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        5      0.01       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           3
Fetch        2      0.00       0.00          0         17          0           3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       13      0.01       0.00          0         17          0           6

Misses in library cache during parse: 1

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

    5 user SQL statements in session.
    0 internal SQL statements in session.
    5 SQL statements in session.
********************************************************************************
Trace file: /oracle/admin/RLZY/udump/rlzy_ora_16831_my_trace_id.trc
Trace file compatibility: 10.01.00
Sort options: default

       0 session in tracefile.
       5 user SQL statements in trace file.
       0 internal SQL statements in trace file.
       5 SQL statements in trace file.
       3 unique SQL statements in trace file.
      57 lines in trace file.
      39 elapsed seconds in trace file.

对于这个语句,tkprof的输出包括以下信息
1.sql语句的文本
2.表格形式的sql跟踪统计
3.解析和执行这个语句库缓存丢失的情况
4.调用解析该语句的用户
5.通过explain plan生成的执行计划.

tkprof还提供了用户级别语句和递归语句调用的摘要信息.

使用tkprof的语法
tkprof是在操作系统命令提示下执行的,它的语法如下
tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

在上面的语法中只有filename1(输入文件名)和filename2输出文件名是必须指定的.
tkprof参数如下
filename1:指定输入文件(由sql trace功能生成的包括了统计的文件),这个文件可以是一个跟踪文件,也可以是多个会话的跟踪文件合并成的一个文件.
filename2:指定tkprof格式化的输出文件名
waits:指示在这个跟踪文件中是否有任何等待事件的记录摘要，这参数的值有yes或no 缺省的是yes.
sorts:在将格式化的内容写入输出文件之前可以指定排序选项将跟踪的语句按降序排列.如果排序选项指定了多个值，那么就会按这些排序项的合计值来进行降序排序.如果你没有指定这个参数，那么tkprof会按输出文件中的列出的参数顺序来排序,下面是排序选项的列表.
[
1:prscnt 解析时间
2:prscpu 解析使用的cpu时间
3:prsela 解析运行时间
4:prsdsk 在解析时从磁盘执行的物理读取数
5:prsqry 在解析时一至读取数据块数
6:prscu 在解析时当前模式下数据块读取数
7:prsmis 在解析时库缓存丢失数
8:execnt 执行次数
9:exeela 执行时间
10:exedsk 执行时从磁盘执行物理读取数
11:exeqry 执行时一至读取数据块数
12:execu 执行时当前模式下读取数据块数
13:exerow 执行时处理的行数
14:exemis 执行时库缓存丢失数
15:fchcnt 获取数据数
16:fchcpu 获取数据花费的cpu时间
17:fchela 获取数据花费的运行时间
18:fchdsk 获取数据时从磁盘读取数据数
19:fchqry 获取数据时一至性读取数据块数
20:fchcu 获取数据时当前模式下读取数据块数
21:fchrow 获取数据的行数
22:userid 解析用户游标的用户ID
]
print:只从输出文件中显示排序后的第一个sql语句,如果没有指定这个参数,那么tkprof将会显示出所有的sql语句.这个参数不会影响可选的sql脚本.这个脚本总是会生成所有跟踪的sql语句的数据.
aggregate:如果指定aggregate=no时，那么tkprof不会对相同的sql文本的多个用户进行聚合操作.
insert:在数据库中创建一个脚本来存储这个跟踪文件的统计.tkprof使用name filename3来创建这个脚本.这个脚本将会创建一个表并向这个表中给每一个跟踪的sql语句插入一条统计记录.
sys:通过用户sys来启用或禁用是否将sql语句或递归sql语句写入到输出文件中.这个缺省值是yes，那么tkprof会将所有的sql语句写入到输出文件中.如果为no时那么tkprof将会忽略它们.这个参数不会影响可选的脚本,这个sql脚本总是会插入所有跟踪sql语句的统计包括递归sql语句.
table:指定表的方案和表名那么tkprof在将它们写入到输出文件中之前会将这个执行计划临时存储在这个表中.如果指定的表已经存在了,那么tkprof删除表中的所有记录.使用它来explain plan 解析语句.然后删除这些行.如果这个表不存在,那么tkprof创建表,然后删除表.

指定的用户必须能够对这个表使用insert,select和delete语句.如果这个表不存在,那么用户必须能使用create table 和drop table 语句.
这个选项允许在explain时多个个同时使用相同的用户运行tkprof,这些个人能指定不同的表名在临时存储计划表时避免各个用户相互影响.
如果使用explain参数时没有指定table参数,那么tkprof使用prof$plan_table.如果使用table参数没有使用explain参数时，那么 tkprof会忽略这个table参数
如果没有执行计划表存在,tkprof会创建一个prof$plan_table在最后删除它.

explain:在跟踪文件中判断每一个sql语句的执行计划并将这些执行计划写到输出文件中.tkprof判断每一个sql语句的执行计划是通过指定用户名和密码参数连接到oracle数据库后使用explain plan语句来生成的.指定的用户必须有create session系统权限.tkprof 如果使用explain选项时能长时间的处理一个很大的跟踪文件.

record:使用指定的filename4这个参数创建一个sql脚本文件将所有的不是递归的sql语句写入到跟踪文件中.从跟踪文件中回放用户事件.
width:一个整数值用来控制某些tkprof输出的宽度象执行计划.这个参数是用来后期处理tkprof的输出的.

例子
一.先设置timed_statistics,max_dump_file_size和user_dump_dest初始化参数
timed_statistics=true
max_dump_file_size=UNLIMITED
user_dump_dest=/oracle/admin/RLZY/udump
二.启用会话跟踪:alter session set sql_trace=true
三.给生成的跟踪文件加入标识符用于与区分其它的文件:alter session set tracefile_identifier='my_trace_id';
四.在这个会话中执行查询语句
select c.*,
       (select d.corp_join_no
          from bs_corp_insure d
         where d.corp_id = b.corp_id
           and d.insr_detail_code = c.insr_detail_code) corp_join_no_dw
from bs_corp_pres b, bs_pres_insur c
where b.indi_id = c.indi_id
   and b.corp_id = c.corp_id and b.corp_id=349;

INDI_JOIN_NO INSR_DETAIL_CODE       CORP_ID CORP_JOIN_NO       INDI_ID BEGIN_DATE END_DATE    ALI_PAY_MONS TO_PAY_MONS FAC_PAY_MONS IF_INDI_ACCO HANDLE_MAN           HANDLE_DATE INDI_JOIN_STA LOST_PAY_SUM PAY_MODE_CODE TRANALI_PAY_MONS INDI_CHARGE_STA LOST_ENJOY_SUM DEAL_BEGIN_DATE FREEZE_STA FREEZE_REASON                                                                    TOPAY_BEGIN_PRD LACK_MONTHS MOD_TIMESTAMP                                                                    CORP_JOIN_NO_DW
------------- ---------------- ------------- ------------- ------------- ----------- ----------- ------------ ----------- ------------ ------------ -------------------- ----------- ------------- ------------ ------------- ---------------- --------------- -------------- --------------- ---------- -------------------------------------------------------------------------------- --------------- ----------- -------------------------------------------------------------------------------- ---------------
       121255                3           349          2484         12338 2009-11-1                                                                1 转数据               2011-4-8 10             1                                                           1                                         0                                                                                                                                                                                                          2484
        12323                2           349           593         12338 2009-11-1       and b.corp_id = c.corp_id and b.corp_id=349;

五.检查生成的跟踪文件,在/oracle/admin/RLZY/udump目录下生成了一个叫rlzy_ora_16831_my_trace_id.trc的文件
六.使用tkprof来格式化跟文件rlzy_ora_16831_my_trace_id.trc
$ tkprof /oracle/admin/RLZY/udump/rlzy_ora_16831_my_trace_id.trc
output = /oracle/admin/RLZY/udump/jy_02_07.trc
insert= /oracle/admin/RLZY/udump/jy_02_07.sql
七.检查输出文件jy_02_07.trc的内容如下

TKPROF: Release 10.2.0.1.0 - Production on Tue Feb 7 16:42:51 2012

Trace file: /oracle/admin/RLZY/udump/rlzy_ora_16831_my_trace_id.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
********************************************************************************

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

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

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 57
********************************************************************************

select :"SYS_B_0"
from
dual

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

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 57

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

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

select c.*,
       (select d.corp_join_no
          from bs_corp_insure d
         where d.corp_id = b.corp_id
           and d.insr_detail_code = c.insr_detail_code) corp_join_no_dw
from bs_corp_pres b, bs_pres_insur c
where b.indi_id = c.indi_id
   and b.corp_id = c.corp_id and b.corp_id=:"SYS_B_0"

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 57

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

Misses in library cache during parse: 0

八.检查输出文件jy_02_07.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
);
set sqlterminator off
set sqlterminator on
创建一个tkprof_table的语句