ITPub博客

首页 > Linux操作系统 > Linux操作系统 > APPEND,bulk collect,正常插入比较

APPEND,bulk collect,正常插入比较

原创 Linux操作系统 作者:yangzhangyue 时间:2013-08-13 13:04:32 0 删除 编辑
在优化某个库归档过多项目时,我建议开发人员用bulk collect,代替正常的批量插入
最后在检查他们优化的时候,发现他们使用append代替我建议的bulk collect,,同样也达到了目的。
       针对他们之间的产生归档量的效果,我做了简单的测试。
SQL> create table david_forall tablespace users as select * from dba_objects;

Table created.

SQL> create table david_forall2 tablespace users as select * from dba_objects where rownum=0;

Table created.

SQL> select count(*) from david_forall;

  COUNT(*)
----------
     89880

SQL> select count(*) from david_forall2;

  COUNT(*)
----------
         0

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                                                150
redo size                                                          10851424
undo change vector size                                               26288

Elapsed: 00:00:00.02
SQL>   begin
  2 
  3          insert /*+ append */  into david_forall2 select * from maclean_forall;
  4       commit;
  5 
  6    end;
  7    /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
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                                                168
redo size                                                          21653724
undo change vector size                                               38236

Elapsed: 00:00:00.01
SQL> declare
  2       type recstartyp is table of david_forall%rowtype index by BINARY_INTEGER;
  3       rec_tab recstartyp;
  4       cursor temp is select * from david_forall;
  5    begin
  6       open temp;
  7       fetch temp bulk collect into rec_tab;
  8       FORALL i in rec_tab.first..rec_tab.last
  9          insert  into david_forall2 values rec_tab(i);
 10       commit;
 11       close temp;
 12    end;
 13    /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15
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                                                272
redo size                                                          32286160
undo change vector size                                              383664

Elapsed: 00:00:00.00
SQL> declare
  2    
  3        cursor temp is select * from david_forall;
  4     begin
  5    
  6       for i in temp loop
  7       insert into david_forall2 values i;
  8       end loop;
  9       commit;
 10     end;
 11     /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.93
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                                                945
redo size                                                          66555352
undo change vector size                                             6490104

Elapsed: 00:00:00.00


SQL> select 10851424,21653724-10851424,32286160-21653724,66555352-32286160 from dual;

  10851424 21653724-10851424 32286160-21653724 66555352-32286160
---------- ----------------- ----------------- -----------------
  10851424          10802300          10632436          34269192

Elapsed: 00:00:00.00

可以看出/*+ APPEND */和bulk collect所产生的日志量是差不多的
/*+ APPEND */  redo  10802300         
bulk collect   redo  10632436         
正常插入        redo   34269192


为此,我们需要访问两个动态性能视图:
  V$MYSTAT,其中有会话的提交信息。
  V$STATNAME,这个视图能告诉我们 V$MYSTAT 中的每一行表示什么(所查看的统计名)。

   生成的 redo 越多,操作花费的时间就越长,整个系统也会越慢。你不光在影响你自己的会话,还会影响每一个会话。redo管理是数据库中的一个串行点。任何 Oracle 实例都只有一个 LGWR,最终所有事务都会归于 LGWR,要求这个进程管理它们的 redo,

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29033984/viewspace-768378/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-09

  • 博文量
    36
  • 访问量
    220662