使用 SQL TRACE 和 TKPROF

使用 SQL TRACE 和 TKPROF

特定顺序的步骤对使用 SQL TRACE和 TKPROF 正确地诊断 SQL 语句的性能
是必要的
1 第一步是确保使用适当的初始化参数这些参数可以在例程级别上设置某些参数也可在会话级设置
2 必须在例程级别上或会话级别上调用 SQL TRACE 一般情况下最好在会话级别上调用
3 运行要诊断的应用程序或 SQL 语句
4 关闭 SQL TRACE 在操作系统级别上正确关闭跟踪文件时这是必要的
5 使用 TKPROF 设置在跟踪会话期间生成的跟踪文件的格式除非输出文件是带格式的否则很难解释结果
6 使用 TKPROF 的输出诊断 SQL 语句的性能

 

初始化参数
init.ora 文件中的两个参数控制 SQL TRACE 实用工具输出文件的大小和目的地
max_dump_file_size = n
如果指定 K 或 M 该参数以字节为单位度量否则该数字代表操作系统块缺省值10,000 个操作系统块
当跟踪文件超过该参数值定义的大小时下述消息会显示在文件的结尾
*** Trace file full ***
下述参数确定跟踪文件的目的地
user_dump_dest = directory
必须设置第三个参数以获得计时信息
timed_statistics = TRUE
计时统计的分辨率为百分之一秒
使用 ALTER SESSION 命令可以在会话级别上动态设置 TIMED_STATISTICS参数

启用和禁用 SQL Trace

• 例程级别:
SQL_TRACE = {TRUE|FALSE}
• 会话级别:
SQL> alter session set SQL_TRACE = {true|false};
SQL> execute DBMS_SESSION.SET_SQL_TRACE
2 ({true|false});
SQL> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
2 (session_id, serial_id, {true|false});

 

SQL TRACE
使用例程级别上或会话级别上的不同方法可以启用或禁用 SQL TRACE
例程级别
在例程级别上设置 SQL_TRACE 参数是启用跟踪的一种方法然而这要求关闭例程然后在不再需要跟踪时重新启动这也会影响重要的性能命中因为将跟踪例程的所有会话
会话级别
会话级跟踪导致较少的全面性能命中因为将跟踪具体的会话启用或禁用
SQL TRACE 的三种方法为
• 使用 ALTER SESSION 命令该命令导致会话持续期间一直进行跟踪或在该值被设置为 FALSE 之前一致进行跟踪
• 使用会话的 DBMS_SESSION.SET_SQL_TRACE 过程
• 使用 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION 过程以启用对非当前用户的会话级跟踪

用 TKPROF 格式化跟踪文件

$ tkprof tracefile.trc output.txt [options]

 

使用 TKPROF 命令
使用 TKPROF 将跟踪文件的格式设置为可读的输出
tkprof tracefile outputfile [sort=option] [print=n]
[explain=username/password] [insert=filename] [sys=NO]
[record=filename] [table=schema.tablename]
跟踪文件在 USER_DUMP_DEST 参数指定的目录中创建并且输出放置在输出文件名指定的目录中
SQL TRACE 也收集递归 SQL 语句的统计您不能直接影响服务器执行的递归
SQL 的数量所以这些数字本身不是特别有用使用 TKPROF 命令选项
SYS=NO 以取消涉及递归 SQL 的输出在指定 EXPLAIN 参数时TKPROF 使用提供的用户名和口令登录到数据库,然后为跟踪的每个 SQL 语句计算出访问路径并将其包括在输出中因为TKPROF 登录到数据库它使用 TKPROF 运行时可用的信息而不是在跟踪统计产生时可用的信息举例来说如果在跟踪语句以后创建或删除一个索引这些信息是不同的TKPROF 也报告库高速缓存未命中这表示在库高速缓存中没有发现该语句的次数

TKPROF 选项

选项                                          说明
TRACEFILE                               跟踪输出文件的名称
OUTPUTFILE                              已设置格式的文件的名称
SORT=option                             语句的排序顺序
PRINT=n                                 打印前 n 个语句
EXPLAIN=user/password                   以指定的用户名运行 EXPLAIN PLAN
INSERT=filename                         生成 INSERT 语句
SYS=NO                                  忽略作为用户 sys 运行的递归 SQL 语句
AGGREGATE=[Y|N]                         如果指定 AGGREGATE = NO TKPROF 不聚集相同

                                        SQL 文本的多个用户
RECORD=filename                         记录在跟踪文件中发现的语句
TABLE=schema.tablename                  将执行计划放入指定的表而不是缺省的PLAN_TABLE

 

可以在操作系统中键入 tkprof 以获得所有可用选项和输出的列表
注 排序选项有

 

排序                                    选项说明
prscnt execnt fchcnt             调用分析执行提取的次数
prscpu execpu fchcpu             分析执行提取所占用的 CPU 时间
prsela exela fchela              分析执行提取所占用的时间
prsdsk exedsk fchdsk             分析执行提取期间的磁盘读取次数
prsqry exeqry fchqry             分析执行提取期间用于持续读取的缓冲区数
prscu execu fchcu                分析执行提取期间用于当前读取的缓冲区数
prsmis exemis                    分析执行期间库高速缓存未命中的次数
exerow fchrow                    分析执行期间处理的行数
userid                           分析游标的用户的用户 ID

 

TKPROF 统计数据
• Count: 执行调用数
• CPU: CPU 的使用秒数
• Elapsed: 总共用去的时间
• Disk: 物理读取次数
• Query: 持续读取的逻辑读取数
• Current: 当前模式下的逻辑读取数
• Rows: 已处理行数

TKPROF 统计信息

统计             含义
Count     分析或执行语句的次数以及为语句发出的提取调用数
CPU       每个阶段的处理时间以秒为单位如果在共享池中找到该语句对于分析阶段为 0
Elapsed   占用时间以秒为单位通常不是非常有用因为其它进程影响占用时间
Disk      从数据库文件读取的物理数据块如果该数据被缓冲则该统计可能很低
Query     为持续读取检索的逻辑缓冲区通常用于 SELECT 语句
Current   在当前模式下检索的逻辑缓冲区通常用于 DML 语句

Rows      外部语句所处理的行对于 SELECT 语句在提取阶段显示它对于 DML 语句在执行阶段显示它

 

Query 和Current 的总和为所访问的逻辑缓冲区的总数