存储过程优化---千万级数量..谢谢

代码段如下:  
  p_cur_date   :=sysdate  
  v_s1   :=   TO_CHAR   (p_cur_date,   'yyyymmddhh24miss');  
        v_sendtime   :=   SUBSTR   (v_s1,   1,   8)   ||   '235959';  
        v_sbegintime   :=   SUBSTR   (v_s1,   1,   8)   ||   '000000';                                       --   time  
        v_rn   :=   0;  
        v_tn   :=   50000;                                                                                   --每个事务的记录数  
  EXECUTE   IMMEDIATE   'alter   table   msg_his   nologging';  
   
        LOOP  
              INSERT   /*+   append   */INTO   msg_his  
                                      (OID,   p_oid,   from_who,   to_who,   direction,   title,   content,  
                                        TYPE,   TIME,   branch_code,   del_flag,   read_status,  
                                        deal_status,   finish_time,   oper_id,   move_time,   move_month)  
                    (SELECT   OID,   p_oid,   from_who,   to_who,   direction,   title,   content,  
                                    TYPE,   TIME,   branch_code,   del_flag,   read_status,   deal_status,  
                                    finish_time,   oper_id,   c1,   c2  
                          FROM   (SELECT       OID,   p_oid,   from_who,   to_who,   direction,   title,  
                                                        content,   TYPE,   TIME,   branch_code,   del_flag,  
                                                        read_status,   deal_status,   finish_time,   oper_id,  
                                                        v_s1   c1,   v_s2   c2,   ROWNUM   rn  
                                              FROM   message  
                                            WHERE   TIME   >=   v_sbegintime  
                                                AND   TIME   <=   v_sendtime  
                                                AND   (TYPE   =   '12'   OR   TYPE   =   '13')  
                                                AND   ROWNUM   <=   (v_rn   +   1)   *   v_tn  
                                      ORDER   BY   TIME)  
                        WHERE   rn   >   v_rn   *   v_tn);  
   
              DBMS_OUTPUT.put_line   (       'insert   round   is   '  
                                                          ||   v_rn  
                                                          ||   '   and   count   is   '  
                                                          ||   SQL%ROWCOUNT  
                                                        );  
   
              IF   (SQL%ROWCOUNT   <   v_tn)  
              THEN  
                    COMMIT;  
                    EXIT;  
              END   IF;  
   
              COMMIT;  
              v_rn   :=   v_rn   +   1;  
        END   LOOP;  
  将表msg_his改为nologging及用append形式发现速度提高不大    
  用到素引TIME  
  通过分段提交..速度也没明显变化  
   
        LOOP  
              DELETE             message  
                          WHERE   TIME   <   TO_CHAR   (p_cur_date   -   v_n1,   'yyyymmddhh24miss')  
                              AND   ROWNUM   <=   v_tn;  
   
              IF   (SQL%ROWCOUNT   <   v_tn)  
              THEN  
                    COMMIT;  
                    EXIT;  
              END   IF;  
   
              COMMIT;  
        END   LOOP;  
  此处删除耗时较多...用索引time   分段提交..  
   
  数据库配置方面有啥能提高的  
  谢谢...各位多多言  
  问题点数：100、回复次数：30Top

1 楼zzd8310（东北笑笑生)(经常被模仿，从未被超越！||我之所以把昵称写这么长。就是想看看昵称到底能写多长，真是长）回复于 2006-10-24 08:33:11 得分 5

帮顶Top

2 楼yjingoxl（の—靖雪☆）回复于 2006-10-24 09:34:50 得分 0

多谢...Top

3 楼riversx（）回复于 2006-10-24 09:56:36 得分 5

有可能是select的执行时间长导致整个语句的时间长，先分析select语句的执行时间。Top

4 楼beckhambobo（beckham）回复于 2006-10-24 12:27:16 得分 10

(SELECT       OID,   p_oid,   from_who,   to_who,   direction,   title,  
                                                        content,   TYPE,   TIME,   branch_code,   del_flag,  
                                                        read_status,   deal_status,   finish_time,   oper_id,  
                                                        v_s1   c1,   v_s2   c2,   ROWNUM   rn  
                                              FROM   message  
                                            WHERE   TIME   >=   v_sbegintime  
                                                AND   TIME   <=   v_sendtime  
                                                AND   (TYPE   =   '12'   OR   TYPE   =   '13')  
                                                AND   ROWNUM   <=   (v_rn   +   1)   *   v_tn  
                                      ORDER   BY   TIME)  
   
  1、time字段有没建索引  
  2、TIME   >=   v_sbegintime     AND   TIME   <=   v_sendtime每次循环大概有多少条记录  
  3、查看分析，千万不要走type索引，type值估计存在message表值不多Top

5 楼yjingoxl（の—靖雪☆）回复于 2006-10-24 16:19:39 得分 0

1.time字段建立了索引  
  2.TIME   >=   v_sbegintime     AND   TIME   <=   v_sendtime是查当天记录   循环次数是ROWNUM   <=   (v_rn   +   1)   *   v_tn   和外层WHERE   rn   >   v_rn   *   v_tn控制   也就是   v_tn   :=   50000;   条  
   
  3.type没有索引,  
  type值估计存在message表值不多??  
   
  多谢..  
   
  Top

6 楼xiaoxiao1984（笨猫儿）回复于 2006-10-24 17:43:50 得分 10

bulk   collect   into   能提高一点效率  
  Top

7 楼xiaoxiao1984（笨猫儿）回复于 2006-10-24 17:45:57 得分 5

估计message表中的type值只有有限的几个，如果只有有限的几个的话，尽量不要建立索引，没有作用Top

8 楼hem（何明）回复于 2006-10-26 10:23:44 得分 5

我觉得不要用分段，分段的查询很慢  
  设置大一点的temp空间   Undo空间  
  nologging应该会快不少，除非你的系统IO不会出现争用。  
  并行插入不知道会不会有效。Top

9 楼Delphi_Li（Delphi Li）回复于 2006-10-26 10:33:31 得分 5

你的Order   By是多余的！！！Oracle插入数据不是顺序的，所以没有必要使用Orader   By，去掉这句，至少能提高40%以上（大数据量情况下）！！！Top

10 楼xiongfuxi（）回复于 2006-10-26 11:39:59 得分 5

每次提交的数据量  
  如果不要求每次都一样，可以用其它的方法来细分每次提交的数据量，  
  如用TIME细分。  
  每次用rownum   >a   and   rownum<X这样性能非常不好。因为TIME     上有索引，如果在time   上细分段，再提交，速度应该会快很多。  
  Top

11 楼yjingoxl（の—靖雪☆）回复于 2006-10-26 22:18:20 得分 0

 
  如果不用order   by在数据库变化的情况下   能保证数据是一直的吗    
  有没有可能   第一次   第二次都查到记录A  
   
  用时间分段在分布比较不均匀的情况下   要是一个时间段的记录太多(2000W)是否需要再分呢  
  试试   先...  
  谢谢  
  Top

12 楼yjingoxl（の—靖雪☆）回复于 2006-10-26 22:25:50 得分 0

SQL1:  
  declare  
  begin  
  for   i   in   1..10   loop  
  DELETE   from   message   where   rownum<=10000;  
  commit;  
  end   loop;  
  end;  
  和  
  SQL2:  
  declare  
  type   rtab   is   table   of   rowid   index   by   BINARY_INTEGER;  
  v_rid   rtab  
  begin  
  for   i   in   1..10   loop;  
  select   rowid   into   v_rid   from   message   where   rownum<=10000;  
  forall   n   in   1..v_rid.count  
          delete   from   message   where   rowid=v_rid(n);  
  commit;  
  end   loop;  
   
  end;  
  照书上说SQL2比SQL1快  
  但实际测试   速度差不多   有时还慢..  
  是否是sql2写得不对...  
  多谢Top

13 楼KingSunSha（弱水三千）回复于 2006-10-27 03:56:21 得分 5

看不出分段有什么意义，直接用一条sql解决  
   
  BEGIN  
        p_cur_date   :=   SYSDATE;  
        v_s1   :=   TO_CHAR   (   p_cur_date,   'yyyymmddhh24miss'   );  
        v_sendtime   :=   SUBSTR   (   v_s1,   1,   8   )   ||   '235959';  
        v_sbegintime   :=   SUBSTR   (   v_s1,   1,   8   )   ||   '000000';                                                                               --   time  
   
        INSERT             /*+   APPEND   */INTO   msg_his  
                                (   OID,   p_oid,   from_who,   to_who,   direction,   title,   content,   TYPE,   TIME,   branch_code  
                                ,   del_flag,   read_status,   deal_status,   finish_time,   oper_id,   move_time,   move_month   )  
              SELECT   OID,   p_oid,   from_who,   to_who,   direction,   title,   content,   TYPE,   TIME,   branch_code  
                        ,   del_flag,   read_status,   deal_status,   finish_time,   oper_id,   c1,   c2  
                  FROM   MESSAGE  
                WHERE   TIME   >=   v_sbegintime   AND   TIME   <=   v_sendtime   AND   TYPE   IN   (   '12',   '13'   );  
  END;  
  /Top

14 楼twenty_three（god is a girl）回复于 2006-10-27 08:37:18 得分 5

学习了Top

15 楼ColinGan（浪子）回复于 2006-10-27 10:59:16 得分 10

1.   msg_his的索引是否很多?如果不多或者没有，那么此表不太会存在性能问题  
  2.   message的索引,order   by将消耗大量的CPU,如果可能,尽量不用,  
        type是否有索引?  
        如果上面的问题解决了还是很慢,那么你可以检查一下这个表的参数,看看这个表是不是碎片太多?看看是不是行迁移太多?  
  3.   对于千万级的表,索引及表参数将是关键,  
  4.   如果还是很慢,那么请你把此语句的执行计划拿出来看看，看使用了哪些索引?看看语句的连接模式?hash   join?  
   
  实际上，说了这么多废话的核心就是:   执行计划  
   
  建议楼主把执行计划拿出来大家帮忙看看，基本上能解决问题的.  
   
  提示一下:如果执行计划中所以使用不正确(实际上所以存在),那么请看看数据库的优化模式,RBO?CBO?如果是RBO那么看看是否曾经对该表进行过分析?又或者分析的日期离现在太久了  
  Top

16 楼xiaoyetao08（人尚未成为人）回复于 2006-10-27 15:54:22 得分 5

是啊  
  楼上是终于说了点关键问题．Top

17 楼yjingoxl（の—靖雪☆）回复于 2006-10-31 16:51:09 得分 0

在测试环境下的执行计划  
   
  Execution   Plan  
  ----------------------------------------------------------  
        0             INSERT   STATEMENT   Optimizer=CHOOSE   (Cost=112   Card=60   Bytes=33  
                      840)  
   
        1         0       VIEW   (Cost=112   Card=60   Bytes=33840)  
        2         1           COUNT   (STOPKEY)  
        3         2               TABLE   ACCESS   (BY   INDEX   ROWID)   OF   'BC_MESSAGE'   (Cost=11  
                      2   Card=1359   Bytes=123669)  
   
        4         3                   INDEX   (RANGE   SCAN)   OF   'BC_MESSAGE_TIME'   (NON-UNIQUE)  
                        (Cost=16   Card=2717)  
   
   
   
   
   
  Statistics  
  ----------------------------------------------------------  
                      0     recursive   calls  
                    29     db   block   gets  
                      9     consistent   gets  
                      6     physical   reads  
                5112     redo   size  
                  619     bytes   sent   via   SQL*Net   to   client  
                1556     bytes   received   via   SQL*Net   from   client  
                      3     SQL*Net   roundtrips   to/from   client  
                      1     sorts   (memory)  
                      0     sorts   (disk)  
                    10     rows   processedTop

18 楼yjingoxl（の—靖雪☆）回复于 2006-10-31 16:56:17 得分 0

msg_his索引不多   就有个time上的索引  
  type没有建索引....  
   
  执行计划如上   帮忙分析哈...谢谢  
   
  Top

19 楼hem（何明）回复于 2006-11-09 16:47:29 得分 0

不要分段，一次导入，不要使用索引,强制全表扫描，/*+append   full(MESSAGE)   */   也可以设置并行读取   parallel(MESSAGE,2)Top

20 楼hem（何明）回复于 2006-11-09 16:48:19 得分 0

不要分段，一次导入，不要使用索引,强制全表扫描，/*+append   full(MESSAGE)   */   也可以设置并行读取   parallel(MESSAGE,2)   不知道并行写入的效果如果   parallel(msg_his,2)    
  Top

21 楼bobfang（匆匆过客）回复于 2006-11-09 17:47:43 得分 5

可以考虑用cursor+fetch   ...   bulk   collect   into   ...+forall来做。  
  Top

22 楼hem（何明）回复于 2006-11-10 16:55:24 得分 0

fetch   ...   bulk   collect   into   ...+forall   能否写得详细点Top

23 楼killideadd（）回复于 2006-11-19 13:49:24 得分 0

msg_his如果有索引的话最好先删除，  
  select   语句加上全表扫描和并行。Top

24 楼shuch（code）回复于 2006-11-20 17:04:26 得分 0

楼主，能否考虑不用分段？  
  采用并行写入。  
  还有，频繁的向数据库提交事务，会造成大量的写日志消耗资源。  
  在你的执行计划中，可以看到你的日志消耗是其他消耗的总和的几倍。  
  5112     redo   sizeTop

25 楼yjingoxl（の—靖雪☆）回复于 2006-11-20 23:24:40 得分 0

谢谢   各位分析...  
        试试先...Top

26 楼geigers（苦也）回复于 2006-11-22 16:21:17 得分 0

请先贴出来你的服务器的硬件配置和你的Oracle版本以及内存分配。  
  然后，逐条察看你的Sql语句的执行计划和trace。  
  看执行计划在于察看索引的使用情况，察看trace在于了解sql语句的硬件资源耗费情况，只有这几项结合起来再加上自己的经验才能判断性能的瓶颈在哪里。Top

27 楼geigers（苦也）回复于 2006-11-22 16:58:36 得分 0

针对你的数据库操作，首先你要明确是insert慢还是select慢。insert慢可能是commit不合理，或者硬盘写入存在瓶颈，或者表约束过多（或不合理）导致，或者存在表级锁；select慢，可能是索引使用不当，硬盘读存在问题，或者存在表级锁。等等，原因很多，需要认真分析。Top

28 楼geigers（苦也）回复于 2006-11-22 17:06:49 得分 10

(SELECT   OID,   p_oid,   from_who,   to_who,   direction,   title,   content,  
                                    TYPE,   TIME,   branch_code,   del_flag,   read_status,   deal_status,  
                                    finish_time,   oper_id,   c1,   c2  
                          FROM   (SELECT       OID,   p_oid,   from_who,   to_who,   direction,   title,  
                                                        content,   TYPE,   TIME,   branch_code,   del_flag,  
                                                        read_status,   deal_status,   finish_time,   oper_id,  
                                                        v_s1   c1,   v_s2   c2,   ROWNUM   rn  
                                              FROM   message  
                                            WHERE   TIME   >=   v_sbegintime  
                                                AND   TIME   <=   v_sendtime  
                                                AND   (TYPE   =   '12'   OR   TYPE   =   '13')  
                                                AND   ROWNUM   <=   (v_rn   +   1)   *   v_tn  
                                      ORDER   BY   TIME)  
  还有，你的这部分sql用到了一个循环里面，实在很不好，每次到这里都需要查询一次。建议使用游标循环，用fetch来控制数据，这样只需要查询一次就可以完成你想要完成的事情了。Top

29 楼bobfang（匆匆过客）回复于 2006-11-24 18:05:59 得分 10

fetch   ...   bulk   collect   into   ...+forall   的例子  
  --create   table   t_test1   as   select   table_name,owner   from   dba_tables   where   0=1;  
  set   serveroutput   on   size   100000  
  set   timing   on  
  declare  
      type   typ1   IS   TABLE   OF   t_test1%ROWTYPE   INDEX   BY   BINARY_INTEGER;  
      rec_tab   typ1;  
      CURSOR   c1   IS   SELECT   table_name,   owner   from   dba_tables;  
      i   number;  
      j   number;  
      k   number;  
  begin  
      OPEN   c1;  
      loop  
          FETCH   c1   BULK   COLLECT   INTO   rec_tab   LIMIT   2699;  
          i   :=   c1%ROWCOUNT;  
          exit   when   not   rec_tab.EXISTS(1);  
          forall   j   in   rec_tab.FIRST..rec_tab.LAST  
              insert   into   t_test1   values   rec_tab(j);  
          k   :=   SQL%ROWCOUNT;  
          dbms_output.put_line('insert   '||k||'   rows');  
          commit;  
          exit   when   c1%notfound;  
      end   loop;  
      close   c1;  
  end;  
  /  
  Top

30 楼Yans（跟贴是一种友谊）回复于 2007-04-26 10:44:03 得分 0

markTop

相关问题

• 帖主：yjingoxl
• zzd8310
• riversx
• beckhambobo
• xiaoxiao1984
• xiaoxiao1984
• hem
• Delphi_Li
• xiongfuxi
• KingSunSha
• twenty_three
• ColinGan
• xiaoyetao08
• bobfang
• geigers
• bobfang

• Oracle类图书