使用sql_trace查看sql语句性能

作为一个B/S开发者，或多或少都得和数据库打交道，而对数据库的操作归根到底都是query语句，所有到最后都是为了查询，那么查看sql性能又成了我们开发中的一件趣事。下面简单介绍下sql_trace的使用：

alter session set sql_trace =true ;--打开sql_trace  
 
select * from (select * from t order by id) where rownum <= 10;--执行要查看性能的sql语句  
 
alter session set sql_trace =true ;--关闭sql_trace 

对，上面的这几句话就可以了，你只要把它打开，执行sql，关闭就OK啦。你别以为这样就万事大吉了，测试已经完成，但是我们还没有看到结果呢。别急，测试结果可不是在sql-plus中可以看到的，这里我们要用到另外一个oracle自带工具：TKPROF 。它的作用就是格式化sql_trace的测试结果。

sql_trace生成的数据是以文件形式存储，该文件的具体位置可以用下面的sql查找出来

connect / as sysdba--以管理员登入(PS:我的数据库直接执行抛出表不存在的异常，所以用管理员登入)  
 
SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||  
       p.spid || '.trc' trace_file_name  
  from (select p.spid  
          from sys.v$mystat m, sys.v$session s, sys.v$process p  
         where m.statistic# = 1  
           and s.sid = m.sid  
           and p.addr = s.paddr) p,  
       (select t.instance  
          from sys.v$thread t, sys.v$parameter v  
         where v.name = 'thread' 
           and (v.value = 0 or t.thread# = to_number(v.value))) i,  
       (select value from sys.v$parameter where name = 'user_dump_dest') d 
connect / as sysdba--以管理员登入(PS:我的数据库直接执行抛出表不存在的异常，所以用管理员登入)

SELECT d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
       p.spid || '.trc' trace_file_name
  from (select p.spid
          from sys.v$mystat m, sys.v$session s, sys.v$process p
         where m.statistic# = 1
           and s.sid = m.sid
           and p.addr = s.paddr) p,
       (select t.instance
          from sys.v$thread t, sys.v$parameter v
         where v.name = 'thread'
           and (v.value = 0 or t.thread# = to_number(v.value))) i,
       (select value from sys.v$parameter where name = 'user_dump_dest') d

 

然后再dos下执行

D:/oracle/product/10.1.0/Db_1/BIN>tkprof D:/oracle/product/10.1.0/admin/super/udump/super_ora_4504.trc D:/tttt.txt 
D:/oracle/product/10.1.0/Db_1/BIN>tkprof D:/oracle/product/10.1.0/admin/super/udump/super_ora_4504.trc D:/tttt.txt

 

tttt.txt就是最终结果，打开后可以开到类似下面的数据

********************************************************************************  
 
select *   
from  
 (select * from t order by id) where rownum <= 10  
 
 
call     count       cpu    elapsed       disk      query    current        rows  
------- ------  -------- ---------- ---------- ---------- ----------  ----------  
Parse        1      0.00       0.00          0          0          0           0  
Execute      1      0.00       0.00          0          0          0           0  
Fetch        2      0.06       0.19        944        949          0          10  
------- ------  -------- ---------- ---------- ---------- ----------  ----------  
total        4      0.06       0.20        944        949          0          10  
 
Misses in library cache during parse: 1  
Optimizer mode: ALL_ROWS  
Parsing user id: 71    
 
Rows     Row Source Operation  
-------  ---------------------------------------------------  
     10  COUNT STOPKEY (cr=949 pr=944 pw=0 time=199638 us)  
     10   VIEW  (cr=949 pr=944 pw=0 time=199613 us)  
     10    SORT ORDER BY STOPKEY (cr=949 pr=944 pw=0 time=199589 us)  
 100000     TABLE ACCESS FULL T (cr=949 pr=944 pw=0 time=836305 us)  
 
******************************************************************************** 
********************************************************************************

select *
from
 (select * from t order by id) where rownum <= 10

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.06       0.19        944        949          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.20        944        949          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71 

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  COUNT STOPKEY (cr=949 pr=944 pw=0 time=199638 us)
     10   VIEW  (cr=949 pr=944 pw=0 time=199613 us)
     10    SORT ORDER BY STOPKEY (cr=949 pr=944 pw=0 time=199589 us)
 100000     TABLE ACCESS FULL T (cr=949 pr=944 pw=0 time=836305 us)

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

 

最后转载下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 的总和为所访问的逻辑缓冲区的总数 
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 的总和为所访问的逻辑缓冲区的总数