bulk into 在大数量操作中使用原理?

zengmuansha

听说bulk into 能减少SQL和PLSQL之间的切换! 可本公司采用这样的写法

cur cur_mms_send is
--返回数量有500万行

select provcode from table_a
open cur_mms_send;
loop
   fetch cur_detail bulk collect into
     l_ary_provcode_tmp1
    limit 10000;
   
   forall i in
1..l_ary_provcode_tmp1.count
insert
into sms_down
    (
        provcode
       )
    values
    (      l_ary_provcode_tmp1(i)    );    commit;
   exit
when cur_mms_send%notfound or cur_mms_send%notfound is
null;            
   end loop;  
   close cur_mms_send;

上面本来可以用一条语句实现
Insert into sms_down
select provcode from table_a ;
而且也没有发生PLSQL 和SQL的切换.

据另个同事说 1 这样因为LIMIT可以减少对PGA的使用量. 2 forall .....  insert into ... commit; 可以把大事务分批提交,减少对UNDO空间使用量.

不知说得是否正确?

Maclean Liu(刘相兵

1.

bulk collect 主要用在批量 填充 队列变量，对于INSET 一般建议用nologging +append 优化

例如：
  Cur_Num number := DBMS_Sql.Open_Cursor();
  rc Sys_Refcursor;

  cursor e is select Employee_ID, First_Name, Last_Name
                from Employees;
  type Emps_t is table of e%rowtype;
  Emps Emps_t;
begin
  DBMS_Sql.Parse(
    c=>Cur_Num, Language_Flag=>DBMS_Sql.Native, Statement=>
      'select Employee_ID, First_Name, Last_Name
         from Employees
         where Department_ID = :d and Salary > :s and ...');

  DBMS_Sql.Bind_Variable(Cur_Num, ':d', Department_ID);
  DBMS_Sql.Bind_Variable(Cur_Num, ':s', Salary);
  ...
  Dummy := DBMS_Sql.Execute(Cur_Num);
  -- Switch to ref cursor and native dynamic SQL
  rc := DBMS_Sql.To_Refcursor(Cur_Num);

  fetch rc bulk collect into Emps;
  close rc;
  ...


以上利用  bulk collect 批量填充了Emps这张PL/SQL 表



2.

An ORA-22813 when using BULK COLLECT is typically expected behavior indicating that you have exceeded the amount of free memory in the PGA.  As collections are processed by PL/SQL they use the PGA to store their memory structures.  Depending on the LIMIT size of the BULK COLLECT and additional processing of the collected data you may exceed the free memory of the PGA.  While intuitively you may think that increasing the PGA memory and increasing the LIMIT size will increase performance, the following example shows you that this is not true in this case.  So, by reviewing this example you should be able to strike a balance between a reasonable LIMIT size and the size of the PGA while maintaining a high level of performance using BULK COLLECT.


合理设置limit size确实可以 避免PGA overflow  避免出现ORA-22813 错误


[oracle@vrh8 ~]$ oerr ora 22813
22813, 00000, "operand value exceeds system limits"
// *Cause:  Object or Collection value was too large. The size of the value
//          might have exceeded 30k in a SORT context, or the size might be
//          too big for available memory.
// *Action:  Choose another value and retry the operation.

Maclean Liu(刘相兵

Bulk Collect/FORALL的性能测试

http://www.oracledatabase12g.com ... B5%8B%E8%AF%95.html

1. FORALL执行使用 原本需要大量循环完成的工作在一次Execute内完成，节省了大量的CPU TIME(大约78%)。
2. append对 forall INSERT起不到减少redo的作用，但是FORALL INSERT本身对比普通loop insert节省了大量redo (大约71%) 和 大量的undo (大约95%)的undo
3. 使用FOR ALL的代价是消耗大量的PGA内存
4. 大量循环LOOP，从Pl/SQL引擎到SQL引擎的切换的消耗是巨大的，几乎占用了以上测试中50%的CPU TIME ， 而FOR ALL/Bulk collect恰恰可以避免这种切换。



PS: 直接使用INSERT /*+ APPEND */ INTO SELECT的性能测试:







SQL> set timing on;
SQL> select  st.name,ss.value from v$mystat ss, v$statname st
  2  where ss.statistic# = st.statistic#                 
  3  and st.name in ('redo size','undo change vector size','CPU used by this session');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                  1
redo size                                                                 0
undo change vector size                                                   0

Elapsed: 00:00:00.00
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
SQL>
SQL> insert /*+ append */  into maclean_forall2 select * from maclean_forall;

815200 rows created.

Elapsed: 00:00:02.27
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_25621.trc
SQL>
SQL>
SQL> select  st.name,ss.value from v$mystat ss, v$statname st
  2  where ss.statistic# = st.statistic#                 
  3  and st.name in ('redo size','undo change vector size','CPU used by this session');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                103
redo size                                                            260168
undo change vector size                                               57544


insert /*+ append */  into maclean_forall2 select * from maclean_forall


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.99       2.18      21619      12238      13865      815200
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.00       2.19      21619      12239      13865      815200

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  LOAD AS SELECT  (cr=12238 pr=21619 pw=11206 time=2187590 us)
815200   TABLE ACCESS FULL MACLEAN_FORALL (cr=11267 pr=10263 pw=0 time=6521656 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       155        0.00          0.00
  flashback buf free by RVWR                     28        0.12          1.15
  db file scattered read                        701        0.00          0.04
  direct path write                               2        0.00          0.00
  direct path sync                                1        0.02          0.02
  control file sequential read                    8        0.00          0.00
  control file parallel write                     4        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        7.32          7.32

直接SQL INSERT..SELECT 的性能还是要好过bulk collect/FORALL的， 正如Tom kyte所说:”如果可能，尽量利用一条SQL语句完成工作。如果无法用一条SQL语句完成，就通过PL/SQL实现（不过，尽可能少用PL/SQL！）。如果在PL/SQL中也无法做到（因为它缺少一些特性，如列出目录中的文件），可以试试使用Java存储过程来实现。如果Java还办不到，那就C外部存储过程中实现。如果速度要求很高，或者要使用采用C编写的一个第三方API，就常常使用这种方法。如果在C外部例程中还无法实现，你就该好好想想有没有必要做这个工作了。”

zengmuansha

多谢 多谢! 先消化下

zengmuansha

为什么 使用FOR ALL的代价是消耗大量的PGA内存 ? 它消耗PGA哪个部分?

像上面 采用了LIMITE 方法 相对直接Insert into 语句 对UNDO空间来说 占用量比较小,因为采用分批提交,UNDO占用了会很快释放掉?

FORALL 为什么大量减少REDO呢?

zengmuansha

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                103
redo size                                                            260168
undo change vector size                                               57544

insert /*+ append */  into maclean_forall2 select * from maclean_forall


直接的会少那么多UNDO 和REDO  太不可思议了

renjixinchina

undo 很少是因为从高水位以后写入 rollback 的时候直接抹去高水位线以后的数据
redo 很少是因为没有记日志 代价是recover 后数据丢失

justinleisure

学习了，顶！

ora_ted

学习，学习