大表执行update set col1=col2优化咨询

clevernby

数据库版本 11.2.0.2.0 Linux x86-64
GI版本 11.2.0.2.0 Linux x86-64
OS版本 RHEL 5.5
这是一个2节点集群数据库
数据库启用归档日志、force logging、主键级别补充日志、fast_start_parallel_rollback=low
硬件配置：Dell R710 (Xeon E5620 2.4G  4核 超线程 * 2颗)  + 140G内存 + ps6000

update语句如下
update table1 set col2=col1;
其中Col1和Col2均为CHAR(8)
table1表共有4亿记录，大小约100G。该语句执行约2个半小时仍未结束。

此外，在业务上，今后col2和col1是有可能不一致的，而历史数据设置成一致。

希望大家多提供些优化思路
目前我想到的有
1) CTAS + 新建主键约束
2) 在运维窗口中使用并行DML

s.o.m

4亿数据量，2个半小时更新意味着什么？

ora-1555了怎么办？

如果直接sql语句执行所有日志激活了怎么办？

在追求性能的同时是否考虑到首先保证系统的稳定。

像这么大的表一般都是分区表，可以按分区进行并行更新，如果没有分区的话，可以借助ora_hash进行并行更新。

1. 
   
2. --------------------------------------------------------------------------------
   
3. --------------------------------------------------------------------------------
   
4. ----update table_name set col_name=1 where col_name=0;
   
5. --------------------------------------------------------------------------------
   
6. --------------------------------------------------------------------------------
   
7. CREATE OR REPLACE procedure owner_name.proc_modf_table_name_col (v_partid in varchar2) is
   
8. type rid_Array is table of rowid index by binary_integer;
   
9. v_rid rid_Array;
   
10. v_Count PLS_INTEGER := 0;
    
11. v_Activenum PLS_INTEGER := 0;
    
12. v_Cur sys_refcursor;
    
13. v_SQL varchar2(2000) := 'select rowid from owner_name.table_name partition('||v_partid||') where col_name = 0';
    
14. begin
    
15. open v_Cur for v_SQL;
    
16. loop
    
17. select sum(decode(a.status, 'CURRENT', 1, 'ACTIVE', 1, 0))
    
18. into v_Activenum
    
19. from v$log a, v$instance b
    
20. where a.thread# = b.instance_number;
    
21. if v_Activenum >= 10 then  ----根据具体情况设置
    
22. dbms_lock.sleep(400);
    
23. end if;
    
24. fetch v_Cur bulk collect
    
25. into v_rid limit 5000;
    
26. ----是否需要备份
    
27. ----forall i in 1 .. v_rid.count()
    
28. ----insert into owner_name_bak.bak_table_name (rid) values (v_rid(i));
    
29. forall i in 1 .. v_rid.count()
    
30. update owner_name.table_name set col_name= 1 where rowid = v_rid(i);
    
31. v_Count := v_Count + sql%rowcount;
    
32. commit;
    
33. exit when v_Cur%notfound;
    
34. end loop;
    
35. close v_Cur;
    
36. dbms_output.put_line(v_Count);
    
37. exception
    
38. when others then
    
39. rollback;
    
40. end;
    
41. /
    

复制代码

根据主机负载和IO情况控制更新并行度。

仅供参考！

Maclean Liu(刘相兵

ODM FINDING:

Intra-partion parallelism: the capability of having more than one slave
operate within a single partition.

Prior to 9i, all PDML on partitions used at most one slave per partition.
This effectively restricted the parallelism to at most the number of
partitions involved in the statement.

In 9iR1 we supported intra-partition parallel inserts thus lifting the
DOP restriction on parallel inserts.

In 9iR2 we supported intra-partition parallel update/delete/merge thus
lifting the DOP restriction on those types of statements. This support,
however, is only available on table segments that were created in 9i or
later as the table storage required changes to support deadlock avoidance.

The following will list tables which do not have this property:

  select u.name, o.name
  from sys.obj$ o, sys.tab$ t, sys.user$ u
  where
    o.obj# = t.obj# and
    o.owner# = u.user# and
    bitand(t.property,536870912) != 536870912;


For dictionary managed tablespaces there is still a restriction where
the DOP on any given partition cannot exceed Min Transaction Freelists (MTFL).
Please refer to Note 157250.1 'Freelist Management with Oracle 8i' if you
would like  more information about freelist management
We manage this        internally to make sure that we don't assign more than
MTFL slaves per partition but it can become user visible if for examble
you do a parallel update on a 2 partition table with DOP 100, and MTFL
is 20. The DOP would effectively be restricted to 40 (20 slaves per partition).

warmbreeze

What is Intra-partition parallelism [ID 241376.1]

Maclean Liu(刘相兵

我看到的资料 都介绍：UPDATE and DELETE operations can be parallelized only if the objects are partitioned. In addition, Oracle does not parallelize operations within a partition.

http://www.toadworld.com/KNOWLED ... D/PAR2/Default.aspx

这需要测试一下

warmbreeze

os:ole 5.7 32bit
db:10205

非分区表并行update 也可以
alter session force parallel dml;

update /*+ parallel(t,2) */ t set object_id=object_id+1;


select b.name, a.value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC#
and b.name like '%parallelized%'
and a.SID=1067

queries parallelized 0
DML statements parallelized 1
DDL statements parallelized 0
DFO trees parallelized 1

Maclean Liu(刘相兵

提供2种 均是在线操作的方式：


1、使用在线重定义 Online Redefinition col_mapping
可以参考         
了解Oracle在线重定义Online Redefinition http://www.askmaclean.com/archiv ... e-redefinition.html

2.         
利用rowid分块实现非分区表的并行update与delete
http://www.askmaclean.com/archiv ... E4%B8%8Edelete.html

这样做的几个优点：

1.  用户手动控制的并行执行，省去了Oracle Parallel并行控制的开销，使用得当的话比加parallel hint或者表上加并行度效率更高。
2. 将数据分割成小块(chunks)来处理，避免了ORA-1555错误
3. 用户可以根据主机负载和IO 动态地加减并行度



补充：　非分区表　并行update应当是无效的， 对于非分区表 并行 DML 只有INSERT有效 ，但是方法2 可以对非分区表 快速并行更新