用tkprof工具查看trace文件
[html]
sql> alter session set sql_trace=true;
或者
[html]
sql> alter system set sql_trace=true;
接下来，运行脚本
[html]
begin
for i in 7369..8000
loop
update test_emp set sal = ‘1000’ where empno=i;
end loop;
end;
关掉追踪，两种方法，匹配上面的打开方式
[html]
sql> alter session set sql_trace=false;
或者
[html]
sql> alter system set sql_trace=false;
想要查看trace文件，必须找到它，trace文件将在diagnostic_dest或者user_dump_dest这两种参数指定的目录下生成，两种方法：
1）通过查看user_dump_dest参数
[html]
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）通过查询
[html]
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.statisti = 0) d
18 /
trace_file
——————————————————————————–
/u01/app/oracle/admin/orcl/udump/orcl_ora_21451.trc
接下来用tkprof工具将trace文件转换成txt文件
[html]
[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.
已经生成
[html]
[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文件中的以下记录
[html]
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来做同样的事情
[html]
begin
for i in 7369..8000
loop
execute immediate ‘update test_emp set sal = 2000 where empno=’||i;
end loop;
end;
查看trace文件，可以看到，
[html]
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次
[html]
********************************************************************************
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)
………
[html]
********************************************************************************
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关键字，下面把程序改成如下：
[html]
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文件如下
[html]
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
这里看出程序块也是解析了一次，执行了一次，那么更新语句解析几次呢
[html]
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语句重用成功。