oracle forall与游标,BULK和FORALL对比普通游标的性能

BULK和FORALL对比普通游标的性能

表testbl是一个简单的300W的表，大小为88M，

create table TESTBL

(

ID   INTEGER,

NAME VARCHAR2(20)

)

没有索引，ID列为自增，NAME列为常量+自增；

表testbl3和表testbl结构一样，数据来自两次INSERT INTO TESTBL，增加索引在ID列。

进行如下语句测试

语句1 记为A语句：

declare

cursor city_cur is

select id, name from testbl;

TYPE table_forall IS TABLE OF testbl%ROWTYPE;

v_table table_forall;

begin

open city_cur;

LOOP

FETCH city_cur BULK COLLECT

INTO v_table limit 1000000;

forall i in 1 .. v_table.COUNT

--update testbl2 set name = v_table.name where id = v_table.id;

--INSERT INTO testbl3 VALUES v_table (i);

update testbl3 set name=v_table(i).name||'myt' where id=v_table(i).id;

dbms_output.put_line(sql%rowcount||'行记录被更新！');

commit;

exit when city_cur%notfound;

END LOOP;

close city_cur;

end;

语句2 记为B语句：

declare

cursor city_cur is

select id, name from testbl;

begin

for my_cur in city_cur loop

update testbl3 set name = my_cur.name where id = my_cur.id;

if mod(city_cur%rowcount, 100000) = 0 then

commit;

end if;

end loop;

end;

其实他们干的一样的事情。只是前者运用了BULK COLLECT FORALL，后者只是普通的循环。

先运行语句A，统计其会话信息如下，其运行时间为350S左右，然后运行语句B，时间大约550S，资源对比如下：

资源消耗：

NAME                                                                 A语句                B语句

----------------------------------------------------------------         ----------             ----------

session logical reads                                               9726235         15603570

physical read total IO requests                                       36005      41501

physical read total multi block requests                                 78      83

physical read total bytes                                         374661120     437616640

logical read bytes from cache                                   7967731712  1278244454

physical reads                                                        45735           50564

physical reads cache                                                  45735        50564

physical read IO requests                                             36005      40005

physical read bytes                                               374661120      414220288

physical reads cache prefetch                                          9730     10559

redo entries                                                        6258198          6255506

redo size                                                        1804760988          1768850388

redo buffer allocation retries                                          180          110

redo log space requests                                                 212         134

redo log space wait time                                               1596        723

redo ordering marks                                                   83989         83497

redo subscn max counts                                                84024      83548

redo synch time                                                           3

redo synch time (usec)                                                33624       1918

redo synch writes                                                         2             2

undo change vector size                                           666179848    665073032

no work - consistent read gets                                      3097280    3060394

rollback changes - undo records applied                                  80     84

IMU undo allocation size                                                171592      1570224

可见实际上在数据库级别的各种指标两者相差不是很大，但是注意一点logical read bytes from cache，bulk比普通的要大得多，

这也正是缓存在PGA中的缘故，我也观察到使用BULK的时候PGA使用量如下：

Begin End

PGA use (MB): 89.4 451.3

可以看到PGA的使用量并非数据量，因为我的数据库只有88M而已，而且使用LIMIT，但是当我减少LIMIT 的量时PGA的使用会减少，可以根据V$PROCESS看到变化

另外一个值得注意的地方时是不是使用FORALL过后UPDATE语句只执行LIMIT后的数量？也就是我的表为300W我每次LIMIT 100W，那么UPDATE只执行3次(300W/100W)，

如果这样真实太好了，那么testbl3表即使很大，并且没有所以，但是只是执行3次，那么对表TESTTB3的扫描将远远小于普通LOOP(有多少条扫描多少次)的性能。

这个问题在AWRRPT中可以看到：

Elapsed Time (s) Executions Elapsed  Time per   Exec (s) %Total %CPU %IO SQL Id                   SQL Module      SQL Text

366.33           1                    366.33                   98.05           83.17 2.65 gkzyyp1y76vww       PL/SQL Developer declare cursor city_cur is se...

362.30           3                     120.77                   96.97          83.58 2.22 7zc27qh9husqz         PL/SQL Developer UPDATE TESTBL3 SET NAME=:B1 ||.

可以看到这里执行次数为3，在看看普通LOOP的AWRRPT

Elapsed Time (s) Executions Elapsed            Time per Exec (s) %Total %CPU %IO SQL Id              SQL Module SQL Text

403.65           3,000,000 0.00                      81.52                 90.70  0.68            ggvw4cdt9ngxt PL/SQL     Developer UPDATE TESTBL3 SET NAME = :B2 ...

1.80                                  1                        1.80                  0.36   78.82 20.29 g49jdtkm33qa0 PL/SQL     Developer SELECT ID, NAME FROM TESTBL

可以看到执行次数为300次。

这是否说明使用FORALL的情况下，可以大大减少执行语句的次数而大大提高性能呢？

其实并非如此,语句如下：

UPDATE TESTBL3 SET NAME = :B2 WHERE ID = :B1

一个简单的尝试，如果ID列有索引，并且值唯一的或者选择率很高的请款下，不管TESTBL3表有多大，UPDATE语句也会很快完成，因为走了INDEX RANGE SCAN。

这种情况下不可能每次执行120S。

如果删除ID列上的索引，我们会发现语句很慢很慢。慢到无语，如下：

Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id              SQL Module         SQL Text

1,915.92         1        1,915.92                                 87.79  98.34 17.43 7zc27qh9husqz   PL/SQL Developer UPDATE TESTBL3 SET NAME=:B1 ||...

1,624.06         0                                                     74.42  98.55 17.27 05bvxz8hfm08y   PL/SQL Developer declare cursor city_cur is se...

可以看到执行一次居然跑了1900S，这个按常理不过只是一次全表扫描，更新2行数据而已，全表扫描一个150M的表不会如此之慢。

如下：

SQL>  UPDATE TESTBL3 SET NAME ='woaini' WHERE ID = 1;

4 rows updated.

Elapsed: 00:00:00.28

Execution Plan

----------------------------------------------------------

Plan hash value: 612867515

-------------------------------------------------------------------

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)|

-------------------------------------------------------------------

|   0 | UPDATE STATEMENT   |         |   242 |  6050 |  6018   (1)|

|   1 |  UPDATE            | TESTBL3 |       |       |            |

|*  2 |   TABLE ACCESS FULL| TESTBL3 |   242 |  6050 |  6018   (1)|

-------------------------------------------------------------------

所以这里虽然执行次数只有3次，实际内部应该还有循环，每次执行100W次UPDATE。

1、bulk，FORALL减少只是SQL和PLSQL引擎交互的时间。

2、bulk会增加PGA的使用，使用量远大于LIMIT出来的数据量。

3、FORALL进行UPDATE的时候如果被修改表非常大，并且没有索引的情况下，FORALL和FOR的时间基本相等，因为SQL和PLSQL引擎交互的时间相比之下不那么明显了。

4、BULK LIMIT会明显的影响的PGA的使用

5、对于如果想UPDATE 2个表的情况，如果都是大表，建议使用MERGE，可以大大节省时间，而避免使用游标。

关于MERGE的注意事项如下连接：

http://blog.itpub.net/7728585/viewspace-1413986/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1415903/，如需转载，请注明出处，否则将追究法律责任。