oracle中bulk,bulk into 在大数量操作中使用原理? - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗...

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外部例程中还无法实现，你就该好好想想有没有必要做这个工作了。”