ve 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=1040