Bulk_Collect_Performance 比较

上一篇讲到了调用集锦，这篇关注一下性能问题吧。

DECLARECURSOR c_tool_listISSELECT descr d1 FROM hardware;l_descr hardware.descr%type;
BEGINOPEN c_tool_list;LOOPFETCH c_tool_list INTO l_descr;EXITWHEN c_tool_list%notfound;END LOOP;CLOSE c_tool_list;
END;

 

上面的语句在小陈机子上运行时间： 

Elapsed: 00:00:09.714

下面的语句又需要多久呢？

DECLARECURSOR c_tool_listISSELECT descr d2 FROM hardware;
type t_descr_list
ISTABLE OF c_tool_list%rowtype;l_descr_list t_descr_list;
BEGINOPEN c_tool_list;FETCH c_tool_list bulk collect INTO l_descr_list;CLOSE c_tool_list;
END;

 

Elapsed: 00:00:00.667答案是：

两个语句比较，上面的自然是简洁上占了优势，也少写了代码。不过从性能上，完全后者逆袭呀。真是海水不可斗量，人不可貌相。

下面我们来看看 collection type 的性能比拼

SET timing ON
DECLARE
type t_va IS varray(1000) OF NUMBER;
type t_nt
ISTABLE OF NUMBER;
type t_aa
ISTABLE OF NUMBER INDEX BY pls_integer;va t_va;nt t_nt;aa t_aa;
BEGINFOR i IN 1 .. 10000LOOPSELECT rownum---- Comment in the collection type you want to test--bulk collect INTO va--bulk collect into nt--bulk collect into aaFROM dualCONNECT BY level <= 1000 ;END LOOP;
END;

 

l  varray(1000) OF NUMBER; 

l  TABLE OF NUMBER;

l  TABLE OF NUMBER INDEX BY pls_integer;

执行时间分别是：

l  Elapsed: 00:00:09.234

l  Elapsed: 00:00:09.272

l  Elapsed: 00:00:09.472

看来时间上相差不大。

然后介绍一下LIMIT用法,书中的内存一直在增加，而小流氓的一直没有增加，神马情况？

  

set serveroutput on;cl scr;DECLARE
type t_row_list
ISTABLE OF hardware.descr%type;l_rows t_row_list;l_pga_ceiling NUMBER(10);
type t_fetch_size
ISTABLE OF pls_integer;l_fetch_sizes t_fetch_size := t_fetch_size(5,10,50,100,500,1000,10000,100000,1000000);rc sys_refcursor;
BEGINSELECT valueINTO l_pga_ceilingFROM v$mystat m,v$statname sWHERE s.statistic# = m.statistic#AND s.name         = 'session pga memory max';dbms_output.put_line('Initial PGA: '||l_pga_ceiling);FOR i IN 1 .. l_fetch_sizes.countLOOPOPEN rc FOR SELECT descr FROM hardware;LOOPFETCH rc bulk collect INTO l_rows limit l_fetch_sizes(i);EXITWHEN rc%notfound;END LOOP;CLOSE rc;SELECT valueINTO l_pga_ceilingFROM v$mystat m,v$statname sWHERE s.statistic# = m.statistic#AND s.name         = 'session pga memory max';dbms_output.put_line('Fetch size: '||l_fetch_sizes(i));dbms_output.put_line('- PGA Max: '||l_pga_ceiling);END LOOP;
END;

 

再来看看处理1000*1000*1000条数据，书中作者的报内存溢出了。ORA-04030: out of process memory when trying to allocate 16396 bytes

.

当然小陈的本本也不例外。只是错误不一样了。所以一定要配合LIMIT来使用，所以一定要配合LIMIT来使用，所以一定要配合LIMIT来使用。重要的事情说三次！！！

ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT

cl scr;
DECLARE
type t_huge_set
ISTABLE OF NUMBER;l_the_server_slaminator t_huge_set;
BEGINSELECT rownum bulk collectINTO l_the_server_slaminatorFROM( SELECT level FROM dual CONNECT BY level <= 1000),( SELECT level FROM dual CONNECT BY level <= 1000),( SELECT level FROM dual CONNECT BY level <= 1000);
END;