目的:
1,批量更新几种方案对比(undo,执行时间)
2,非分区表是否支持并行验证
--环境
-----------------
硬件环境:
uatdb1:/oracle/app/oracle$prtconf
uatdb1:/oracle/app/oracle$ prtconf
System Model: IBM,9119-595
Machine Serial Number: 83F13CE
Processor Type: PowerPC_POWER5
Number Of Processors: 6
Processor Clock Speed: 1900 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 21 uatdb1
Memory Size: 12288 MB
Good Memory Size: 12288 MB
Platform. Firmware level: Not Available
Firmware Version: IBM,SF240_358
Console Login: enable
Auto Restart: true
Full Core: false
OS版本:
uatdb1:/oracle/app/oracle$ uname -a
AIX uatdb1 3 5 00CF13CE4C00
Oracle 版本:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production
SQL> show parameter target
pga_aggregate_target big integer 1G
sga_target big integer 8G
0.准备:
0.1 users_chnl_a为需要更新的目标表:
drop table users_chnl_a;
create table users_chnl_a
as
select * from users_chnl;
alter table users_chnl_a
add constraint PK_users_chnl_a primary key (USER_ID, CHNL);
begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'USERS_CHNL_A',degree => 8,cascade => true);
end;
/
select table_name,num_rows from user_tables where table_name='USERS_CHNL_A';
TABLE_NAME NUM_ROWS
USERS_CHNL_A 22292951
0.2 更新参照表users_chnl_pwd:
create table users_chnl_pwd nologging
as
select user_id,chnl,password,login_name,lpad('1',32,'1') npassword
from users_chnl
where 1=2;
--rec_id 唯一序列号,FLAG 处理标记
alter table users_chnl_pwd add(rec_id integer,flag integer);
--需记录该日期,在第三步使用
prompt 需记录该日期,在第三步使用
select to_char(trunc(sysdate),'YYYYMMDD HH24MISS') "to_date" from dual;
alter session enable parallel dml;
insert /*+ parallel(t1 8) */ into users_chnl_pwd t1
select /*+ parallel(t2 8) */ user_id,chnl,password,login_name,lpad('1',32,'1') npassword,SEQ_users_chnl_recid.nextval,'0'
from users_chnl t2
where crt_date<=trunc(sysdate);
commit;
alter session disable parallel dml;
create unique index uidx_users_chnl_pwd on users_chnl_pwd(user_id,chnl) parallel 8;
create unique index uidx_users_chnl_pwd_recid on users_chnl_pwd(rec_id) parallel 8;
begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'USERS_CHNL_PWD',degree => 8,cascade => true);
end;
/
select table_name,num_rows from user_tables where table_name='USERS_CHNL_PWD';
TABLE_NAME NUM_ROWS
USERS_CHNL_PWD 22334054
--1,方案1,直接更新:
create unique index uidx_users_chnl_npwd on users_chnl_pwd(user_id,chnl,npassword) parallel 8;
alter session enable parallel dml;
update /*+ parallel(u 8) */ (select /*+ parallel(uc 8) parallel(ucp 8) */
uc.password,
ucp.npassword
from users_chnl_a uc,users_chnl_pwd ucp
where uc.user_id=ucp.user_id
and uc.chnl=ucp.chnl
) u set u.password=npassword;
commit;
alter session disable parallel dml;
==>1800 seconds
--回滚段跟踪:
SELECT sum(used_ublk)*8/1024,sum(used_urec)
FROM v$transaction
WHERE addr = ANY
(SELECT taddr
FROM v$session
WHERE saddr = ANY
(SELECT saddr FROM gv$px_session WHERE qcsid = 375));
2.639GB 22292959
---------执行计划:
explain plan for
update /*+ parallel(u 8) */ (select /*+ parallel(uc 8) parallel(ucp 8) */
uc.password,
ucp.npassword
from users_chnl_a uc,users_chnl_pwd ucp
where uc.user_id=ucp.user_id
and uc.chnl=ucp.chnl
) u set u.password=npassword;
select * from table(dbms_xplan.display);
Plan hash value: 3364305002
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 21M| 1718M| | 28890 (1)| 00:05:47 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 21M| 1718M| | 28890 (1)| 00:05:47 | Q1,04 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | USERS_CHNL_A | | | | | | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 21M| 1718M| | 28890 (1)| 00:05:47 | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 21M| 1718M| | 28890 (1)| 00:05:47 | Q1,03 | P->P | RANGE |
| 6 | UPDATE | USERS_CHNL_A | | | | | | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 21M| 1718M| | 28890 (1)| 00:05:47 | Q1,03 | PCWP | |
| 8 | PX SEND HASH (BLOCK ADDRESS)| :TQ10002 | 21M| 1718M| | 28890 (1)| 00:05:47 | Q1,02 | P->P | HASH (BLOCK|
|* 9 | HASH JOIN BUFFERED | | 21M| 1718M| 140M| 28890 (1)| 00:05:47 | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 22M| 871M| | 9921 (1)| 00:02:00 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 22M| 871M| | 9921 (1)| 00:02:00 | Q1,00 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 22M| 871M| | 9921 (1)| 00:02:00 | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL | USERS_CHNL_A | 22M| 871M| | 9921 (1)| 00:02:00 | Q1,00 | PCWP | |
| 14 | PX RECEIVE | | 22M| 873M| | 4888 (2)| 00:00:59 | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10001 | 22M| 873M| | 4888 (2)| 00:00:59 | Q1,01 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 22M| 873M| | 4888 (2)| 00:00:59 | Q1,01 | PCWC | |
| 17 | INDEX FAST FULL SCAN | UIDX_USERS_CHNL_NPWD | 22M| 873M| | 4888 (2)| 00:00:59 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("UC"."USER_ID"="UCP"."USER_ID" AND "UC"."CHNL"="UCP"."CHNL")
--2,方案2,使用rowid更新
2.0创建待更新记录表:
drop table temp_users_chnl_pwd;
create table temp_users_chnl_pwd nologging
as
select uc.rowid rd,uc.password npassword
from users_chnl_a uc
where 1=2;
alter session enable parallel dml;
insert /*+ parallel(tucp 8) */ into temp_users_chnl_pwd tucp
select /*+ parallel(uc 8) parallel(ucp 8) */
uc.rowid rd,ucp.npassword
from users_chnl_a uc,users_chnl_pwd ucp
where uc.user_id=ucp.user_id
and uc.chnl=ucp.chnl
;
commit;
alter session disable parallel dml;
--600 seconds
待更新表rowid排序:
create table temp_users_chnl_pwd_a nologging
as
select * from temp_users_chnl_pwd
where 1=2;
alter session enable parallel dml;
insert /*+ parallel(tucp 8) */ into temp_users_chnl_pwd_a tucp
select /*+ parallel(t 8)*/ *
from temp_users_chnl_pwd t
order by rd
;
commit;
alter session disable parallel dml;
--600 seconds
--2.1批量更新(FORALL)
DECLARE
con_row_limit CONSTANT PLS_INTEGER := 100000;
l_batch INT := 0;
l_count int := 0;
l_rindex BINARY_INTEGER;
l_slno BINARY_INTEGER;
CURSOR cur IS
SELECT rd, npassword FROM temp_users_chnl_pwd;
TYPE rowidtab IS TABLE OF temp_users_chnl_pwd.rd%TYPE INDEX BY PLS_INTEGER;
l_rowidtab rowidtab;
TYPE pwdtab IS TABLE OF temp_users_chnl_pwd.npassword%TYPE INDEX BY PLS_INTEGER;
l_pwdtab pwdtab;
BEGIN
l_rindex := dbms_application_info.set_session_longops_nohint;
OPEN cur;
LOOP
--分批
FETCH cur BULK COLLECT
INTO l_rowidtab, l_pwdtab LIMIT con_row_limit;
EXIT WHEN l_rowidtab.COUNT = 0;
l_count := l_count + l_rowidtab.count;
--更新
FORALL i IN l_rowidtab.FIRST .. l_rowidtab.LAST
UPDATE users_chnl_a
SET password = l_pwdtab(i)
WHERE ROWID = l_rowidtab(i);
COMMIT;
--批次
l_batch := l_batch + 1;
dbms_application_info.set_session_longops(rindex => l_rindex,
slno => l_slno,
sofar => l_batch *
con_row_limit,
target_desc => 'USERS_CHNL');
dbms_output.put_line('Processed:'||l_count);
END LOOP;
COMMIT;
CLOSE cur;
dbms_output.put_line('---OK! Processed:'||l_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(dbms_utility.format_error_stack);
COMMIT;
CLOSE cur;
END;
/
待处理记录没有针对rowid排序:
==>7800 seconds
待处理记录针对rowid排序:
==>541 seconds
--2.2 单个更新
DECLARE
con_row_limit CONSTANT PLS_INTEGER := 100000;
l_count INT := 0;
l_rindex BINARY_INTEGER;
l_slno BINARY_INTEGER;
BEGIN
l_rindex := dbms_application_info.set_session_longops_nohint;
FOR x IN (SELECT rd, npassword FROM temp_users_chnl_pwd_a) LOOP
l_count := l_count + 1;
UPDATE users_chnl_a SET password = x.npassword WHERE ROWID = x.rd;
IF (MOD(l_count, con_row_limit) = 0) THEN
COMMIT;
dbms_application_info.set_session_longops(rindex => l_rindex,
slno => l_slno,
sofar => l_count,
target_desc => 'USERS_CHNL');
dbms_output.put_line('Processed:' || l_count);
END IF;
END LOOP;
COMMIT;
dbms_output.put_line('---OK! Processed:' || l_count);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(dbms_utility.format_error_stack);
COMMIT;
END;
/
待处理记录没有针对rowid排序:
==>8400 seconds
待处理记录针对rowid排序:
==>1080 seconds
结果比较:
执行时间(Seconds) 回滚段(undo size,MByte)
直接更新 1800 2,700
中间表-单笔更新-无rowid排序 9000 3
中间表-单笔更新-rowid排序 1680 3
中间表-批量更新-无rowid排序 8400 3
中间表-批量更新-rowid排序 1141 3
结论:
使用“中间表-批量更新-rowid排序”方式更新方式最快,而且所需回滚段空间很少,进度可以监控,可有效控制风险。
3. 非分区表dml操作是否可以并行问题:
3.1 会话没有启用并行:
update /*+ parallel(t 8) */ users_chnl_a t set password=password;
==>1270 sec
select * from v$px_session
可以看到1*8+1个服务器进程
3.2 会话启用并行:
alter session enable parallel dml;
update /*+ parallel(t 8) */ users_chnl_a t set password=password;
commit;
alter session disable parallel dml;
--107.078 sec
--63 sec
--136 sec
select * from v$px_session
可以看到2*8+1个服务器进程
结论:
非分区表支持平行更新,但需要在会话内启用并行dml.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-710488/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-710488/