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

Question

代码段如下:
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 分段提交..数据库配置方面有啥能提高的
谢谢...各位多多言

Answer 1

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

Answer 2

(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表值不多

Answer 3

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表值不多??多谢..

Answer 4

bulk collect into 能提高一点效率

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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写得不对...
多谢

Answer 11

看不出分段有什么意义，直接用一条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;
/

Answer 12

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

Answer 13

在测试环境下的执行计划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 processed

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

(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来控制数据，这样只需要查询一次就可以完成你想要完成的事情了。

Answer 24

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;
/