用TKPROF工具查看trace文件，了解sql重用提高软解析，提高性能

来源：互联网发布：linux架设暗黑2 编辑：程序博客网时间：2024/05/24 05:02

今天想要分析重用带来的高效结果，于是试着用TKPROF工具来看trace文件，记录下来

名词解释：TKPROF：瞬态内核事件探查器(transient kernel profiler) .

网上找到的TKPROF工具的详细用法http://www.cnblogs.com/songdavid/articles/2075798.html

要想生成trace文件，必须打开追踪：两种方式

SQL> alter session set sql_trace=true;

或者

SQL> alter system set sql_trace=true;

接下来，运行脚本

begin  for i in 7369..8000  loop    update test_emp set sal = '1000' where empno=i;  end loop;end;

关掉追踪，两种方法，匹配上面的打开方式

SQL> alter session set sql_trace=false;

或者

SQL> alter system set sql_trace=false;

想要查看trace文件，必须找到它，trace文件将在diagnostic_dest或者user_dump_dest这两种参数指定的目录下生成，两种方法：

1）通过查看user_dump_dest参数

SQL> show parameter user_dump_destNAME             TYPE   VALUE------------------------------------ ----------- ------------------------------user_dump_dest           string   /u01/app/oracle/admin/orcl/udu             mp[oracle@RedHat ~]$ cd /u01/app/oracle/admin/orcl/udump[oracle@RedHat udump]$ lltotal 36-rw-r-----  1 oracle oinstall  1154 Aug 23 10:09 orcl_ora_20965.trc-rw-r-----  1 oracle oinstall  7688 Aug 23 10:09 orcl_ora_20970.trc-rw-r-----  1 oracle oinstall 23813 Aug 23 10:09 orcl_ora_21451.trc

2）通过查询

SELECT    a.VALUE       || b.symbol       || c.instance_name       || '_ora_'       || d.spid       || '.trc' trace_file  FROM (SELECT VALUE          FROM v$parameter         WHERE NAME = 'user_dump_dest') a,       (SELECT SUBSTR (VALUE, -6, 1) symbol          FROM v$parameter         WHERE NAME = 'user_dump_dest') b,       (SELECT instance_name          FROM v$instance) c,       (SELECT spid          FROM v$session s, v$process p, v$mystat m 17           WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d 18  /TRACE_FILE--------------------------------------------------------------------------------/u01/app/oracle/admin/orcl/udump/orcl_ora_21451.trc

接下来用TKPROF工具将trace文件转换成txt文件

[oracle@RedHat ~]$ tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_21451.trc  /u01/app/oracle/admin/orcl/udump/orcl_ora_21451.txtTKPROF: Release 10.2.0.1.0 - Production on Fri Aug 23 10:21:04 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.

已经生成

[oracle@RedHat orcl]$ cd udump[oracle@RedHat udump]$ lltotal 60-rw-r-----  1 oracle oinstall  1276 Aug 23 10:19 orcl_ora_20965.trc-rw-r-----  1 oracle oinstall  7688 Aug 23 10:09 orcl_ora_20970.trc-rw-r-----  1 oracle oinstall 23813 Aug 23 10:09 orcl_ora_21451.trc-rw-r--r--  1 oracle oinstall 22541 Aug 23 10:21 orcl_ora_21451.txt

查看trace文件中的以下记录

begin  for i in 7369..8000  loop    update test_emp set sal = '1000' where empno=i;  end loop;end;call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.02       0.01          0          0          0           1Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.02       0.02          0          0          0           1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 54  ********************************************************************************UPDATE TEST_EMP SET SAL = '1000' WHERE EMPNO=:B1 call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute    632      0.01       0.02          0       1897         18          14Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total      633      0.01       0.02          0       1897         18          14

可以看出，对整个语句解析了一次，执行了一次，对更新语句解析了一次，执行了632次，这里就体现了语句的重用，在应用中如果重用多的话，就会避免大量的硬解析，那么会大大提高数据库的性能

接下来研究通过execute immediate命令是用动态SQL来做同样的事情

begin  for i in 7369..8000  loop    execute immediate 'update test_emp set sal = 2000 where empno='||i;  end loop;end;

查看trace文件，可以看到，

begin  for i in 7369..8000  loop    execute immediate 'update test_emp set sal = 1000 where empno='||i;  end loop;end;call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.07       0.07          0          0          0           1Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.08       0.08          0          0          0           1

这个程序块被解析了1次，执行的一次，但是更新语句却被解析了632次

********************************************************************************update test_emp set sal = 1000 where empno=7369call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.01       0.01          0          2          0           0Execute      1      0.00       0.00          0          3          3           1Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.01       0.01          0          5          3           1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 54     (recursive depth: 1)Rows     Row Source Operation-------  ---------------------------------------------------      0  UPDATE  TEST_EMP (cr=3 pr=0 pw=0 time=233 us)      1   TABLE ACCESS FULL TEST_EMP (cr=3 pr=0 pw=0 time=58 us)********************************************************************************update test_emp set sal = 1000 where empno=7370call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          1          0           0Execute      1      0.00       0.00          0          3          0           0Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.00       0.00          0          4          0           0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 54     (recursive depth: 1)Rows     Row Source Operation-------  ---------------------------------------------------      0  UPDATE  TEST_EMP (cr=3 pr=0 pw=0 time=32 us)      0   TABLE ACCESS FULL TEST_EMP (cr=3 pr=0 pw=0 time=28 us)

.........

********************************************************************************update test_emp set sal = 2000 where empno=8000call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          1          0           0Execute      1      0.00       0.00          0          3          0           0Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.00       0.00          0          4          0           0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 54     (recursive depth: 1)Rows     Row Source Operation-------  ---------------------------------------------------      0  UPDATE  TEST_EMP (cr=3 pr=0 pw=0 time=22 us)      0   TABLE ACCESS FULL TEST_EMP (cr=3 pr=0 pw=0 time=19 us)********************************************************************************commit

那么，我们在使用动态SQL的时候怎么来避免大量硬解析呢，其实可以用USEING关键字，下面把程序改成如下：

begin  for i in 7369..8000  loop    execute immediate 'update test_emp set sal = 2000 where empno =:emp_no' using i ;  end loop;end;

查看trace文件如下

begin  for i in 7369..8000  loop    execute immediate 'update test_emp set sal = 2000 where empno =:emp_no' using i ;  end loop;end;call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.02       0.02          0          0          0           1Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      0.03       0.03          0          0          0           1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 54

这里看出程序块也是解析了一次，执行了一次，那么更新语句解析几次呢

update test_emp set sal = 2000 where empno =:emp_nocall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute    632      0.02       0.03          2       1898         15          14Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total      633      0.03       0.03          2       1898         15          14Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 54     (recursive depth: 1)Rows     Row Source Operation-------  ---------------------------------------------------      0  UPDATE  TEST_EMP (cr=1896 pr=0 pw=0 time=13022 us)     14   TABLE ACCESS FULL TEST_EMP (cr=1896 pr=0 pw=0 time=10400 us)

可以看出来，更新语句是解析了一次，执行了632次，说明这里 sql语句重用成功。