签到成功

知道了

CNDBA社区CNDBA社区

Oracle 利用 rowid 提升 update 性能

2016-11-25 16:02 1354 0 原创 Oracle 性能优化
作者: dave

 

关于ROWID的介绍参考我的Blog

http://www.cndba.cn/Dave/article/1535
http://www.cndba.cn/Dave/article/1535

            Oracle Rowid 介绍

            http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx

 http://www.cndba.cn/Dave/article/1535

关于大表Update 的一个讨论,参考itpub

            http://www.itpub.net/viewthread.php?tid=1052077

 

. 在虚拟机上 使用rowid 进行update 测试

            使用rowid 进行update能提高速度,是因为通过rowid 能够迅速的进行定位,不用全表进行扫描。

 

-- 查看表dave 记录数

SYS@dave2(db2)> select count(*) from dave;

  COUNT(*)

----------

   3080115 --300万数据

 

-- 创建测试表dba

SYS@dave2(db2)> create table dba as select * from dave;

Table created.

 

--dave 表去更新DBA

SYS@dave2(db2)> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);

 

3080115 rows updated.

 

Elapsed: 00:16:12.81 -- 整个更新花了16分钟

 

--update 期间查看session 执行时间:

SQL>select sid,target,time_remaining,elapsed_seconds,message,sql_id from v$session_longops where sid=138;

 http://www.cndba.cn/Dave/article/1535

 

select * from v$lock where sid=138;

 

select * from v$session_wait where sid=138;

 

-- 使用rowid 进行更新

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id

     ORDER BY b.ROWID;  ---如果表的数据量不是很大,可以不用 order by rowid

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;http://www.cndba.cn/Dave/article/1535

  COMMIT;

END;

 

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:14:54.07 -- 执行花了14分钟,速度提高不是很多。

 

            在这个更新中,使用了ORDER BY b.ROWID 进行了排序,每个数据块里面都有多条记录,这样按rowid 进行排序,那么这样每次访问数据块的时候就会相同,就会减小block 在调用的次数,从而提高效率。

 

            因为我这是虚拟机上的测试环境,所以内存分配的并不合适,I/O 也不行。

 

--我们把order by 去掉,在更新看看

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id;

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;http://www.cndba.cn/Dave/article/1535

 http://www.cndba.cn/Dave/article/1535

 

PL/SQL procedure successfully completed.

Elapsed: 00:20:24.43

-- 居然用了21分钟,看来对大表还是很有必要进行order by rowid的。

 

. 在测试服务器上测试

            折腾了半天没有折腾出效果来。将数据dump 出来,在imp 到测试服务器,300w的数据,dump 文件有300M

 

--在测试服务器上直接update

SQL> update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id);

 

update dba ta set prov_code=(select area_code from dave tb where ta.id=tb.id)

       *

ERROR at line 1:

ORA-01013: user requested cancel of current operation

 

Elapsed: 00:20:45.04

 

一直的处理中. 被迫取消。 查看了一下session的状态:

SQL>select sid,target,time_remaining,elapsed_seconds,message,sql_id from v$session_longops where sid=197;

            等了20分钟,才8blocks,要处理到39521blocks,不知道要到那个猴年马月了。 居然比我虚拟机上测试的还慢。

 

-- 在测试服务器上使用rowid + order by

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id

     ORDER BY b.ROWID;  ---如果表的数据量不是很大,可以不用 order by rowidhttp://www.cndba.cn/Dave/article/1535

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;

 

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:04:45.98

-- 总算看到效果了,4分多钟搞定,如果在生产库上,这个操作应该还会快一点。

 

-- 在测试服务器上使用rowid

 

DECLARE

  CURSOR cur IS

    SELECT

     a.area_code, b.ROWID ROW_ID

      FROM dave a, dba b

     WHERE a.id = b.id;

  V_COUNTER NUMBER;

BEGIN

  V_COUNTER := 0;http://www.cndba.cn/Dave/article/1535

  FOR row IN cur LOOP

    UPDATE dba

       SET prov_code = row.area_code

     WHERE ROWID = row.ROW_ID;http://www.cndba.cn/Dave/article/1535

    V_COUNTER := V_COUNTER + 1;

    IF (V_COUNTER >= 1000) THEN

      COMMIT;

      V_COUNTER := 0;

    END IF;

  END LOOP;

  COMMIT;

END;

 

 

PL/SQL procedure successfully completed.

Elapsed: 00:09:06.73 -- 花了9分钟

 

         通过以上测试,验证了对于大表的update,除了使用rowid,还需要根据rowid 排序一下。

   

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群:62697716();   DBA2 群:62697977()   DBA3 群:62697850()  

DBA 超级群:63306533();  DBA4 群: 83829929  DBA5群: 142216823   

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2235
    原创
  • 2
    翻译
  • 535
    转载
  • 185
    评论
  • 访问:6446033次
  • 积分:4223
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ