今天想要分析重用带来的高效结果,于是试着用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_dest
NAME 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]$ ll
total 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.txt
TKPROF: Release 10.2.0.1.0 - Production on Fri Aug 23 10:21:04 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
已经生成
[oracle@RedHat orcl]$ cd udump
[oracle@RedHat udump]$ ll
total 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 0
Execute 1 0.02 0.01 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing 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 0
Execute 632 0.01 0.02 0 1897 18 14
Fetch 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 0
Execute 1 0.07 0.07 0 0 0 1
Fetch 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=7369
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 2 0 0
Execute 1 0.00 0.00 0 3 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 5 3 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing 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=7370
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 3 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing 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=8000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 3 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing 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 0
Execute 1 0.02 0.02 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.03 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
这里看出程序块也是解析了一次,执行了一次,那么更新语句解析几次呢
update test_emp set sal = 2000
where
empno =:emp_no
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 632 0.02 0.03 2 1898 15 14
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 633 0.03 0.03 2 1898 15 14
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing 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语句重用成功。