批量更新,MERGE语句性能最好,因为它可以多块读,并且可以并行执行,但是缺点就是消耗比较多的UNDO,一旦down机死事物恢复较慢。
ORDER BY ROWID 在 buffer cache 不够大的情况下性能较好好(没Merge快,因为Merge可以多块读,走ROWID只能单块读)。
优点就是可以批量提交。缺点就是不能并行更新。
不 ORDER BY ROWID 在 buffer cache足够大(能确保被更新的表不被page out) 的情况下性能较好。
create table a as select * from dba_objects;
create table b as select * from dba_objects;
insert into b select * from b; --- 直到插入60W数据
SQL> SELECT COUNT(*) FROM B;
COUNT(*)
----------
616864
SQL> SELECT SUM(BYTES)/1024/1024 "SIZE(MB)" FROM DBA_SEGMENTS WHERE SEGMENT_NAME='B';
SIZE(MB)
----------
72
create index idx_a on a(object_name,object_id);
create index idx_b on b(object_id);
比如要执行这个update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);
可以用MERGE代替
-------------------------MERGE版本,使用MERGE一定要确保MERGE into 的表走全表扫描----------------
alter session set db_file_multiblock_read_count=128;
如果要更新的表很大,alter session enable parallel dml;
alter session set workarea_size_policy=manual;
alter session set sort_area_size=xxx;
alter session set hash_area_size=xxx;
merge /*+ USE_HASH(C,H) FULL(C) */ into b c
using (select /*+INDEX(A) USE_HASH(A) */ a.object_name, a.object_id
from a
where a.object_id in (select /*+ use_hash(b) index(b) */ object_id from b)) h
on (c.object_id = h.object_id)
when matched then
update set c.object_name = h.object_name;
select * from table(dbms_xplan.display);
也可以写PL/SQL
-------------------------PL/SQL版本---------------------------------
DECLARE
CURSOR CUR_B IS
SELECT a.object_id, a.object_name, b.ROWID ROW_ID
FROM A, B
WHERE A.object_id = B.object_id
ORDER BY B.ROWID;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR ROW_B IN CUR_B LOOP
UPDATE b SET object_name = ROW_B.object_name WHERE ROWID = ROW_B.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 10000) THEN
COMMIT;
dbms_output.put_line('Updated: ' ||V_COUNTER || ' lines.');
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
/
下面这个版本是批量处理的版本
------------------------批量处理版本--------------------------------
declare
maxrows number default 100000;
row_id_table dbms_sql.urowid_table;
--currcount_table dbms_sql.number_Table;
object_name_table dbms_sql.varchar2_Table;
cursor cur_b is
SELECT /*+ index(a) use_hash(a,b) index(b) */
a.object_name, b.ROWID ROW_ID
FROM A, B
WHERE A.object_id = B.object_id
ORDER BY B.ROWID;
v_counter number;
begin
v_counter := 0;
open cur_b;
loop
EXIT WHEN cur_b%NOTFOUND;
FETCH cur_b bulk collect
into object_name_table, row_id_table limit maxrows;
forall i in 1 .. row_id_table.count
update b
set object_name = object_name_table(i)
where rowid = row_id_table(i);
commit;
end loop;
end;
/
关于ORDER BY ROWID提升速度的验证
-------------------Buffer cache 不够大---------------------------------------
buffer cache 40Mb,B表有72Mb 所以不够存放下 B
keep pool 52Mb--keep idx_a,idx_b
SQL> alter system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 00.00
SQL> merge into b c
2 using (select a.object_name, a.object_id
3 from a
4 where a.object_id in (select object_id from b)) h
5 on (c.object_id = h.object_id)
6 when matched then
7 update set c.object_name = h.object_name;
616851 行已合并。
已用时间: 00: 00: 12.51
SQL> alter system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 00.00
SQL> declare
2 maxrows number default 100000;
3 row_id_table dbms_sql.urowid_table;
4 --currcount_table dbms_sql.number_Table;
5 object_name_table dbms_sql.varchar2_Table;
6 cursor cur_b is
7 SELECT /*+ index(a) use_hash(a,b) index(b) */
8 a.object_name, b.ROWID ROW_ID
9 FROM A, B
10 WHERE A.object_id = B.object_id
11 ORDER BY B.ROWID; ------有ORDER BY ROWID
12 v_counter number;
13 begin
14 v_counter := 0;
15 open cur_b;
16 loop
17 EXIT WHEN cur_b%NOTFOUND;
18 FETCH cur_b bulk collect
19 into object_name_table, row_id_table limit maxrows;
20 forall i in 1 .. row_id_table.count
21 update b
22 set object_name = object_name_table(i)
23 where rowid = row_id_table(i);
24 commit;
25 end loop;
26 end;
27 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 31.71
SQL> alter system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 01.87
SQL> declare
2 maxrows number default 100000;
3 row_id_table dbms_sql.urowid_table;
4 --currcount_table dbms_sql.number_Table;
5 object_name_table dbms_sql.varchar2_Table;
6 cursor cur_b is
7 SELECT /*+ index(a) use_hash(a,b) index(b) */
8 a.object_name, b.ROWID ROW_ID
9 FROM A, B
10 WHERE A.object_id = B.object_id;
11 v_counter number;
12 begin
13 v_counter := 0;
14 open cur_b;
15 loop
16 EXIT WHEN cur_b%NOTFOUND;
17 FETCH cur_b bulk collect
18 into object_name_table, row_id_table limit maxrows;
19 forall i in 1 .. row_id_table.count
20 update b
21 set object_name = object_name_table(i)
22 where rowid = row_id_table(i);
23 commit;
24 end loop;
25 end;
26 /
PL/SQL 过程已成功完成。
已用时间: 00: 01: 25.64
MERGE只需要13秒,ORDER BY ROWID 的PL/SQL 需要32秒,而没ORDER BY ROWID 的PL/SQL 需要1分26秒
如果buffer cache不够大(不能容纳下A,B),不order by rowid 要花1分25秒,order by rowid只花了32秒
可见,ORDER BY ROWID 在BUFFER CACHE不够大的情况下,对于速度的提升是非常明显的,因为buffer cache不够大,block可能经常被page out。
order by rowid 会连续更新临近的block,这样就确保读入的block尽可能的不被page out。
为什么Merge 比 用ROWID 去更新快呢?因为MERGE可以多块读,做MERGE的时候设置参数db_file_multiblock_read_count=128
根据ROWID去更新,只能一次读一个block
---------------------BUFFER CACHE 足够大,多次执行,取运行最快的时间---------------------------------------
SQL> alter system set db_keep_cache_size=150m;
系统已更改。
SQL> alter table a storage(buffer_pool keep);
表已更改。
SQL> alter table b storage(buffer_pool keep);
表已更改。
SQL> select /*+ full(a) */ count(*) from a;
COUNT(*)
----------
50526
已用时间: 00: 00: 00.16
SQL> select /*+ full(b) */ count(*) from b;
COUNT(*)
----------
616864
已用时间: 00: 00: 01.08
SQL> SELECT o.OWNER,o.object_type,o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
2 FROM DBA_OBJECTS o, V$BH bh
3 WHERE o.DATA_OBJECT_ID = bh.OBJD
4 AND o.owner='SCOTT'
5 GROUP BY o.owner,o.object_type, o.OBJECT_NAME
6 ORDER BY COUNT(*)desc ,2 ;
OWNER OBJECT_TYPE OBJECT_NAME NUMBER_OF_BLOCKS
------------------------------ ------------------- ------------------------------ ----------------
SCOTT TABLE B 8467
SCOTT INDEX IDX_B 1394
SCOTT TABLE A 695
SCOTT INDEX IDX_A 288
SQL> declare
2 maxrows number default 100000;
3 row_id_table dbms_sql.urowid_table;
4 --currcount_table dbms_sql.number_Table;
5 object_name_table dbms_sql.varchar2_Table;
6 cursor cur_b is
7 SELECT /*+ index(a) use_hash(a,b) index(b) */
8 a.object_name, b.ROWID ROW_ID
9 FROM A, B
10 WHERE A.object_id = B.object_id
11 ORDER BY B.ROWID;
12 v_counter number;
13 begin
14 v_counter := 0;
15 open cur_b;
16 loop
17 EXIT WHEN cur_b%NOTFOUND;
18 FETCH cur_b bulk collect
19 into object_name_table, row_id_table limit maxrows;
20 forall i in 1 .. row_id_table.count
21 update b
22 set object_name = object_name_table(i)
23 where rowid = row_id_table(i);
24 commit;
25 end loop;
26 end;
27 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 11.83
SQL> declare
2 maxrows number default 100000;
3 row_id_table dbms_sql.urowid_table;
4 --currcount_table dbms_sql.number_Table;
5 object_name_table dbms_sql.varchar2_Table;
6 cursor cur_b is
7 SELECT /*+ index(a) use_hash(a,b) index(b) */
8 a.object_name, b.ROWID ROW_ID
9 FROM A, B
10 WHERE A.object_id = B.object_id;
11 v_counter number;
12 begin
13 v_counter := 0;
14 open cur_b;
15 loop
16 EXIT WHEN cur_b%NOTFOUND;
17 FETCH cur_b bulk collect
18 into object_name_table, row_id_table limit maxrows;
19 forall i in 1 .. row_id_table.count
20 update b
21 set object_name = object_name_table(i)
22 where rowid = row_id_table(i);
23 commit;
24 end loop;
25 end;
26 /
PL/SQL 过程已成功完成。
已用时间: 00: 00: 09.71
SQL> merge into b c
2 using (select a.object_name, a.object_id
3 from a
4 where a.object_id in (select object_id from b)) h
5 on (c.object_id = h.object_id)
6 when matched then
7 update set c.object_name = h.object_name;
616851 行已合并。
已用时间: 00: 00: 08.54
ORDER BY ROWID 的PL/SQL 用了11秒,没有ORDER BY ROWID的PL/SQL用了9秒,而Merge最快,只花了8秒多。
(反复测试,以最快时间为准,添加了3个logfile group 每组500Mb,减少logfile对测试的影响)。
由此可见,如果buffer cache够大,不order by rowid 反比order by rowid更快(因为少了排序)
大数据updata
----转帖请注明作者和出处: fromeast http://www.itpub.net/thread-1052077-1-1.html
最近一直在折腾大表的更新问题,今天终于有了突破。兴奋之余发个帖子跟大家分享一下心得,并且讨论一下是否还可能进一步提高处理速度。
问题是这样的:一张5亿条记录的表,没有分区。由于增加了一个冗余字段,需要根据另外一张表(4.8亿条)更新这个大表。下面是具体的描述:
环境:HP-UX 11i+Oracle9.2.0.8+RAID
要更新的表:T1 (id1 number, id2 number, curr_count number,.....) --id1唯一 5亿条记录 >60GB
更新数据来源:T2 (id2 number, curr_count number) --id2唯一 4.8亿
更新逻辑:T2中的每一条记录,都到T1中找到对应的记录(T2.id2=T1.id2),更新T1.curr_count=T2.curr_count
限制条件:只能在线更新(应用程序一直在访问这个表,所以不能用INSERT SELECT),不能占用太多系统资源,要求3天之内更新完毕。
原来的做法:
declare
cursor cur_t2 is
select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2;
v_counter number;
begin
v_counter := 0;
for row_t2 in cur_t2 loop
update T1 set curr_count=row_t2.curr_count
where rowid=row_t2.row_id;
v_counter := v_counter + 1;
if (v_counter>=1000) then
commit;
v_counter := 0;
end if;
end loop;
commit;
end;
/
问题:更新太慢,260 rows/s,全部更新完毕需要22天!
经过调查发现是UPDATE语句执行的效率太低,进一步的跟踪发现,UPDATE至少90%的时间是在等待db file sequential read这个事件。按说都ROWID了,为什么还有这么多磁盘等待?再看disk reads,明白了,原来UPDATE语句产生了大量的物理读,当然慢了。想必T1表太大了,Data Buffer装不下,并且有其他的表跟它竞争,所以刚更新一条数据,从磁盘读取了一个数据块到内存,很快就被挤去出了,下次更新这个块上的其他数据时,还得再从磁盘读取。这样Data Buffer Cache的效率就很低,基本没有利用上。
怎么解决呢?最好是能按数据块的顺序更新,这样某个数据块里的第一行数据更新后,数据块内的其他行就不用再从磁盘里读取了(不太可能那么快就被挤出内存),物理读降低了,速度肯定能加快。可是怎样按数据块的顺序更新呢?我想到了ROWID的结构是data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串),那么ROWID的顺序应该就是数据块的顺序了。于是我修改了PLSQL:
alter table T1 storage(buffer_pool keep); -- keep buffer pool size = 6GB
declare
cursor cur_t2 is
select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2
order by T1.rowid;
v_counter number;
begin
v_counter := 0;
for row_t2 in cur_t2 loop
update T1 set curr_count=row_t2.curr_count
where rowid=row_t2.row_id;
v_counter := v_counter + 1;
if (v_counter>=1000) then
commit;
v_counter := 0;
end if;
end loop;
commit;
end;
/
alter table T1 storage(buffer_pool default);
这回更新的速度大为加快:10000 rows/s。分析跟踪文件表明db file sequential reads和磁盘读取变的很少。按照这个速度20个小时之内就能全部更新完了。
心得:处理的数据量并没有减少,只是改变一下处理的顺序,也可以极大地提高性能。
====================================================================
*后记4:已上生产
*后记3:试验了KEEP的影响
(1)重新运行试验1(不order by rowid)
开始的语句改成:alter table T1 storage(buffer_pool keep);
处理速度:73~74行/秒
(2)重新运行试验2(order by rowid):
开始的语句改成:alter table T1 storage(buffer_pool default);
处理速度:1万条/秒
结论:从本次测试可以印证先前的推断——把表的buffer_pool属性设为keep与否,对处理速度的影响很小,以至于可以忽略。处理速度加快的原因,是因为order by rowid,按块顺序处理数据,很大程度上减少了物理读。
*后记2:关于order by rowid的资料:
http://rdc.taobao.com/blog/dba/html/199_oracle_rowid_order.html
这篇文章说order by rowid导致大量的查询物理读。其实在本文第二个测试中也是这样的——CURSOR的打开时间比不ORDER BY ROWID时间要长,因为多了SORT。可是这样是值得的,因为后续有大量的UPDATE,节省的物理读是很可观的。
*后记1:修改了几处错误:
where T1.id1=T2.id2 => where T1.id2=T2.id2
parallel_index(IX_T2_id2,16) => parallel_index(IX_T1_id2,16)