批量更新效能比較

目的:
    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/，如需轉載，請註明出處，否則將追究法律責任。