关闭

利用tkprof查看trace文件

64人阅读 评论(0) 收藏 举报

一 SQL TRACE 使用方法:
1.初始化sql trace
参数:
timed_statistics=true 允许sql trace 和其他的一些动态性能视图收集与时间有关的参数、
SQL>alter session set titimed_statistics=true
max_dump_file_size=500 指定跟踪文件的大小
SQL> alter system set max_dump_file_size=500;
user_dump_dest 指定跟踪文件的路径
SQL> alter system set user_dump_dest=/oracle/oracle/diag/rdbms/orcl/orcl/trace;

2.为一个session 启动sql trace

2.命令方式
alter session set sql_trace=true
2.2 通过存储过程启动sqltrace
select sid,serial#,osuser from v$session;
SID SERIAL# OSUSER
168 1 oracle

execute  dbms_system.set_sql_trace_in_session (168 ,1,true);

详见http://www.oracle-base.com/articles/8i/DBMS_System.php
3.停止一个sql trace 会话
3.1 命令方式
alter session set sql_trace=false
3.2 存储过程的方式
execute dbms_system.set_sql_trace_in_session (168 ,1,false);
4. 为整个实例启动SQL trace (一般消耗系统性能较高,不会用)
alter system set sql_trace=true scope=spfile
从新启动数据库
5. 停止一个实例的sql trace
alter system set sql_trace=false scope=spfile

启动sql trace 之后收集的信息包括
1.解析、执行、返回数据的次数
2.cpu和执行命令的时间
3.物理读和逻辑读的次数
4.系统处理的记录数
5.库缓冲区错误
二 TKPROF的使用
tkprof 的目的是将sql trace 生成的跟踪文件转换成用户可以理解的格式
1. 格式
tkprof inputfile outputfile [optional | parameters ]
参数和选项:
explain=user/password 执行explain命令将结果放在SQL trace的输出文件中
table=schema.table 指定tkprof处理sql trace文件时临时表的模式名和表名
insert=scriptfile 创建一个文件名为scriptfile的文件,包含了tkprof存放的输出sql语句
sys=[yes/no] 确定系统是否列出由sys用户产生或重调的sql语句
print=number 将仅生成排序后的第一条sql语句的输出结果
record=recordfile 这个选项创建一个名为recorderfile的文件,包含了所有重调用的sql语句
sort=sort_option 按照指定的方法对sql trace的输出文件进行降序排序
sort_option 选项
prscnt 按解析次数排序
prscpu 按解析所花cpu时间排序
prsela 按解析所经历的时间排序
prsdsk 按解析时物理的读操作的次数排序
prsqry 按解析时以一致模式读取数据块的次数排序
prscu 按解析时以当前读取数据块的次数进行排序
execnt 按执行次数排序
execpu 按执行时花的cpu时间排序
exeela 按执行所经历的时间排序
exedsk 按执行时物理读操作的次数排序
exeqry 按执行时以一致模式读取数据块的次数排序
execu 按执行时以当前模式读取数据块的次数排序
exerow 按执行时处理的记录的次数进行排序
exemis 按执行时库缓冲区的错误排序
fchcnt 按返回数据的次数进行排序
fchcpu 按返回数据cpu所花时间排序
fchela 按返回数据所经历的时间排序
fchdsk 按返回数据时的物理读操作的次数排序
fchqry 按返回数据时一致模式读取数据块的次数排序
fchcu 按返回数据时当前模式读取数据块的次数排序
fchrow 按返回数据时处理的数据数量排序
三 sql trace 的输出结果
count:提供OCI过程的执行次数
CPU: 提供执行CPU所花的时间单位是秒
Elapsed:提供了执行时所花的时间。单位是秒。这个参数值等于用户响应时间
Disk:提供缓存区从磁盘读取的次数
Query:以一致性模式从缓存区获得数据的次数
Current:以当前模式从缓存区获得数据的次数
ROWs: 返回调用或执行调用时,处理的数据行的数量。

四:举例:
sql>alter session set sql_trace=true
SQL>select * from dba_users;
SQL>show parameter user_dump_dest
user_dump_dest string /oracle/oracle/diag/rdbms/orcl/orcl/trace
SQL>exit
cd /oracle/oracle/diag/rdbms/orcl/orcl/trace
tkprof orcl_ora_11066.trc /oracle/oracle/trace1.out sys=yes

gedit trace.out

1. query+current/rows 平均每行所需的block数,太大的话(超过20)SQL语句效率太低
2. Parse count/Execute count parse count应尽量接近1,如果太高的话,SQL会进行不必要的reparse。
要检查Pro*C程序的MAXOPENCURSORS是不是太低了,或不适当的使用的RELEASE_CURSOR选项
3. rows Fetch/Fetch Fetch Array的大小,太小的话就没有充分利用批量Fetch的功能,
增加了数据在客户端和服务器之间的往返次数。在Pro*C中可以用prefetch=NN,Java/JDBC中可调用SETROWPREFETCH,
在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)  
4. disk/query+current 磁盘IO所占逻辑IO的比例,太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)  
5. elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源  
6. cpu elapsed 太大表示执行时间过长,或消耗了大量的CPU时间,应该考虑优化
7. 执行计划中的Rows 表示在该处理阶段所访问的行数 

 

PS:

1.使用tkprof格式化trace文件
Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
输入文件 格式化后的输出文件
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
用于指定在将执行规划写进输出文件之前,TKPROF用于临时存放执行规划所用表的架构和名称
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements. 只列出输出文件中的第一个INTEGER的SQL语句,若忽略,则TKPROF将列出所有跟踪的SQL语句
aggregate=yes|no 若用户指定AGGREGATE=NO,TKPROF将不会对相同SQL文本的多个用户进行汇总
insert=filename List SQL statements and data inside INSERT statements. SQL脚本的一种,用于将跟踪文件的动机信息存储到数据库中
sys=no TKPROF does not list SQL statements run as user SYS. 于启动或禁止将用户SYS所发布的SQL语句列表到输出文件之中,也包括递归SQL(为执行用户的SQL语句,ORACLE还必须执行一些附加语句)语句在内。默认为YES
record=filename Record non-recursive statements found in the trace file. 对于跟踪文件中的所用非递归SQL语句,TKPROF 将以指定的名称来创建某个SQL脚本。用于对跟踪文件中的用户时间进行重放
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options: 在将被跟踪的SQL语句列表输出到跟踪文件之前,先将其按照指定排序选项的降序关系对其进行排序;若指定了多种排序选项,那么根据排序选项所指定值的和的降序关系对其进行排序;若忽略此参数,那么TKPROF将按照使用次序把语句列表到输出文件中。

 

2.查詢.trc文件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';


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:8037次
    • 积分:254
    • 等级:
    • 排名:千里之外
    • 原创:11篇
    • 转载:28篇
    • 译文:3篇
    • 评论:0条