bulk collect limit性能比较

bulk collect limit性能比较

http://blog.sina.com.cn/s/blog_4bda1bf301010z7f.html

当采集大批量数据的时候，内存无法承担直接bulk collect into的限制，这个时候需要limit语法来限制获取行，限制内存消耗同时不至于影响太大性能。

一、limit=5

SQL>
SQL> declare
2 — Local variables here
3 –type t_row is table of mc$obj%rowtype;
4 l_row mc$obj%rowtype;
5 cursor row_cur is select * from mc$obj where rownum<50000;
6 begin
7 — Test statements here
8 open row_cur;
9 loop
10 exit when row_cur%notfound;
11 fetch row_cur into l_row;
12 end loop;
13 end;
14 /

PL/SQL procedure successfully completed

Executed in 1.203 seconds

二、limit=15

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 15;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.391 seconds

三、limit=50

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 50;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.328 seconds

四、limit=100

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 100;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed

Executed in 0.313 seconds

五、limit=200

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 200;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.313 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.312 seconds

六、limit=500

SQL>
SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 500;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.297 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.296 seconds

七、limit=1000

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 1000;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.297 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.297 seconds

八、limit=2000

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 2000;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.297 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.312 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.297 seconds

九、limit=5000

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 5000;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.313 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.297 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.297 seconds

十、limit=10000

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 10000;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.312 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.313 seconds

十一：no limit

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 open row_c;
8 loop
9 exit when row_c%notfound;
10 fetch row_c bulk collect into l_row limit 50000;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed

Executed in 0.39 seconds

SQL> /

PL/SQL procedure successfully completed

Executed in 0.375 seconds

SQL> declare
2 — Local variables here
3 type t_row is table of mc$obj%rowtype;
4 l_row t_row;
5 cursor row_c is select * from mc$obj where rownum<50000;
6 begin
7 select * bulk collect into l_row from mc$obj where rownum<50000;
8 end;
9 /

PL/SQL procedure successfully completed

Executed in 0.375 seconds

SQL>
SQL> /

PL/SQL procedure successfully completed

Executed in 0.36 seconds

从上面的例子可以看出， limit在500左右性能最好，在15以下性能最差，在15之上到500左右，性能逐步增加，但规模不大。而在500和5000之间几乎没有什么变化，超过10000之后性能由开始下降，达到全部的50000之后性能最差。

表格的数据结构：

SQL> desc mc$obj
Name Type Nullable Default Comments
———– ————– ——– ——- ——–
OBJ# NUMBER
DATAOBJ# NUMBER Y
OWNER# NUMBER
NAME VARCHAR2(30)
NAMESPACE NUMBER
SUBNAME VARCHAR2(30) Y
TYPE# NUMBER
CTIME DATE
MTIME DATE
STIME DATE
STATUS NUMBER
REMOTEOWNER VARCHAR2(30) Y
LINKNAME VARCHAR2(128) Y
FLAGS NUMBER Y
OID$ RAW(16) Y
SPARE1 NUMBER Y
SPARE2 NUMBER Y
SPARE3 NUMBER Y
SPARE4 VARCHAR2(1000) Y
SPARE5 VARCHAR2(1000) Y
SPARE6 DATE Y

表格记录的平均长度：

SQL> select avg(nvl(vsize(OBJ# ),1)+
2 nvl(vsize(DATAOBJ# ),1)+
3 nvl(vsize(OWNER# ),1)+
4 nvl(vsize(NAME ),1)+
5 nvl(vsize(NAMESPACE ),1)+
6 nvl(vsize(SUBNAME ),1)+
7 nvl(vsize(TYPE# ),1)+
8 nvl(vsize(CTIME ),1)+
9 nvl(vsize(MTIME ),1)+
10 nvl(vsize(STIME ),1)+
11 nvl(vsize(STATUS ),1)+
12 nvl(vsize(REMOTEOWNER),1)+
13 nvl(vsize(LINKNAME ),1)+
14 nvl(vsize(FLAGS ),1)+
15 nvl(vsize(OID$ ),1)+
16 nvl(vsize(SPARE1 ),1)+
17 nvl(vsize(SPARE2 ),1)+
18 nvl(vsize(SPARE3 ),1)+
19 nvl(vsize(SPARE4 ),1)+
20 nvl(vsize(SPARE5 ),1)+
21 nvl(vsize(SPARE6 ),1)) from mc$obj where rownum<50000;

AVG(NVL(VSIZE(OBJ#),1)+NVL(VSI
——————————
74.7153143062861

我们以75字节一行计算。

500行值为：500*75 = 37500 Bytes

15行值为：15*75= 1125

从以上计算，我们基本可以看出：当数据规模在sdu（32K）附近的时候具有最好的性能。

而当数据规模达到或者接近mtu(1500字节）时候，性能增加幅度不会很大。

通过以上比较，我们可以知道bulk collect into的最佳性能应该在32k~64K附近，这样内存消耗也比较少。

<input type="hidden" name="wtitle" value="bulk collect limit性能