首页 > 代码库 > oracle热点表online rename

oracle热点表online rename

对于在线的繁忙业务表的任何操作都可能带来意想不到的风险。
一张业务表,对partition key进行升位,其步骤是:

  1. rename原表
  2. 新建临时表
  3. 交换分区到临时表
  4. 升位临时表的字段的长度
  5. 交换临时表到第二张临时表
  6. rename第二种临时表为业务表

整个的操作过程如果顺利,预计在10s左右,如果放在文件中,速度会很快。
下面模拟繁忙表进行测试:

  

#!/bin/sh
. /home/oracle/.bash_profile
sqlplus -S /nolog<<EOF
        conn test/test 
        declare
                type arraylist is table of varchar2(20 byte);
                arr_account arraylist;
                ran number;
        error varchar2(4000);
        begin
                arr_account := arraylist();
                arr_account.extend(5);
                arr_account(1):=001;arr_account(2):=002;
                arr_account(3):=003;arr_account(4):=004; 
        arr_account(5):=005; ran :=dbms_random.value(1,5);
                while(1>0) loop
        begin
                        execute immediate insert into  test(col1,col2,col3) values(:1,:2,:3)
            using 1,arr_account(ran),dbms_random.string(|, 2000);
                        commit;
        exception when others then
            error:=SQLERRM;insert into log values(error);
            commit;
        end;
                DBMS_LOCK.SLEEP(0.5);
                end loop;
        end;
        /
EOF

现在打开100个线程进行并发的插入,因为每个insert都sleep 0.5秒钟,基本上1s钟插入的记录约等于线程数*2。

  

#!/bin/sh
for((i=1;i<=$1;i++))
do
        /home/oracle/insert.sh &
done
因为表比较繁忙,所以用loop来进行ddl操作: declare begin loop begin execute immediatealter table test rename to test_bak; exit; exception when others then null; end; end loop; end; /

完成rename后,发现整个db主机的cpu利用率有不小的升高。

07:43:27 PM       CPU     %user     %nice   %system   %iowait     %idle
07:43:29 PM       all      5.31      0.00      0.09      5.19     89.40
07:43:31 PM       all      5.44      0.00      0.13      4.85     89.59
07:43:31 PM       CPU     %user     %nice   %system   %iowait     %idle
07:43:33 PM       all      5.38      0.00      0.12      4.62     89.88
07:43:35 PM       all      6.12      0.00      0.25      4.34     89.29
07:43:35 PM       CPU     %user     %nice   %system   %iowait     %idle
07:43:37 PM       all      6.28      0.00      0.22      4.41     89.09

cpu升高了一个百分点:trace其中的一个session:

********************************************************************************
insert into  test(col1,col2,col3)values(:1,:2,:3) 
values
(:1,:2,:3)
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       44      0.00       0.01          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       44      0.00       0.01          0          0          0           0

Misses in library cache during parse: 44
Optimizer mode: ALL_ROWS
error during execute of EXPLAIN PLAN statement
ORA-00942: table or view does not exist
parse error offset: 88

这里在20s的时间里,进行了44次insert, 每次insert都进行了parse,这也是cpu会上升的原因,因为parse是cpu密集型的操作,oracle parse这个sql时,
发现其depend object不存在,所以在下次过来时,仍然需要解析。

所以:在对特别繁忙的表进行rename操作的时候,object不可用,会造成大量的解析,如果并发量比较大,伴随着大量的读,会操作db hang住。所以要特别小心。