bulk collect limit性能比较
http://blog.sina.com.cn/s/blog_4bda1bf301010z7f.html
当采集大批量数据的时候,内存无法承担直接bulk collect into的限制,这个时候需要limit语法来限制获取行,限制内存消耗同时不至于影响太大性能。
一、limit=5
SQL>
SQL> declare
2
3
4
5
6
7
8
9
10
11
12
13
14
PL/SQL procedure successfully completed
Executed in 1.203 seconds
二、limit=15
SQL> declare
2
3
4
5
6
7
8
9
10
11
12
13
PL/SQL procedure successfully completed
Executed in 0.391 seconds
三、limit=50
SQL> declare
2
3
4
5
6
7
8
9
10
11
12
13
PL/SQL procedure successfully completed
Executed in 0.328 seconds
四、limit=100
SQL> declare
2
3
4
5
6
7
8
9
10
11
12
13
PL/SQL procedure successfully completed
Executed in 0.313 seconds
五、limit=200
SQL> declare
2
3
4
5
6
7
8
9
10
11
12
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
3
4
5
6
7
8
9
10
11
12
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
3
4
5
6
7
8
9
10
11
12
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
3
4
5
6
7
8
9
10
11
12
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
3
4
5
6
7
8
9
10
11
12
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
3
4
5
6
7
8
9
10
11
12
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
3
4
5
6
7
8
9
10
11
12
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
3
4
5
6
7
8
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
———– ————– ——– ——- ——–
OBJ#
DATAOBJ#
OWNER#
NAME
NAMESPACE
SUBNAME
TYPE#
CTIME
MTIME
STIME
STATUS
REMOTEOWNER VARCHAR2(30)
LINKNAME
FLAGS
OID$
SPARE1
SPARE2
SPARE3
SPARE4
SPARE5
SPARE6
表格记录的平均长度:
SQL> select avg(nvl(vsize(OBJ#
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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附近,这样内存消耗也比较少。