******************************
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文件，可以看到，
[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 (recursi