使用DBMS_PARALLEL_EXECUTE進行快速更新
問題:
如何在儘量短的時間內完成大批次資料的更新？如漂白資料時，需要將客戶表（約一億行）中的手機號設定為常量。
比如：投產時資料遷移
可以考慮的方法：
1，並行dml
一個事務，需要較大的回滾表空間
進度難以預測
並行dml bug較多
SQL> alter session enable parallel dml;
Session altered
Executed in 0.047 seconds
SQL> update /*+ parallel(e 20) */ CUST e SET e.mobile = '123';
107007800 rows updated
Executed in 183.285 seconds
SQL> commit;
Commit complete
Executed in 5.179 seconds
SQL> alter session disable parallel dml;
Session altered
Executed in 0.063 seconds
SID TYPE ID1 LMODE REQUEST BLOCK
---------- ---- ---------- ---------- ---------- ----------
109 TM 99947 1 0 0
109 TM 99947 4 0 0
154 TM 99947 1 0 0
154 TM 99947 4 0 0
166 TM 99947 6 0 1 --發起並行更新的會話
169 TM 99947 0 3 0 --對該表dml操作，被阻塞
181 TM 99947 1 0 0
181 TM 99947 4 0 0
184 TM 99947 1 0 0
184 TM 99947 4 0 0
187 TM 99947 1 0 0
187 TM 99947 4 0 0
190 TM 99947 1 0 0
190 TM 99947 4 0 0
193 TM 99947 1 0 0
193 TM 99947 4 0 0
196 TM 99947 1 0 0
196 TM 99947 4 0 0
199 TM 99947 1 0 0
199 TM 99947 4 0 0
202 TM 99947 1 0 0
202 TM 99947 4 0 0
205 TM 99947 1 0 0
205 TM 99947 4 0 0
208 TM 99947 1 0 0
208 TM 99947 4 0 0
211 TM 99947 1 0 0
211 TM 99947 4 0 0
214 TM 99947 1 0 0
214 TM 99947 4 0 0
217 TM 99947 1 0 0
217 TM 99947 4 0 0
220 TM 99947 1 0 0
220 TM 99947 4 0 0
223 TM 99947 1 0 0
223 TM 99947 4 0 0
226 TM 99947 1 0 0
226 TM 99947 4 0 0
229 TM 99947 1 0 0
229 TM 99947 4 0 0
232 TM 99947 1 0 0
232 TM 99947 4 0 0
42 rows selected
2，應用並行
靈活
步驟複雜
主要步驟：
*分片（chunk）
*多個分片並行更新
版本:
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
軟硬體環境:
SQL> select stat_name, value
2 from v$osstat
3 where stat_name in ('NUM_CPU_CORES', 'NUM_CPUS', 'PHYSICAL_MEMORY_BYTES');
STAT_NAME VALUE
---------------------------------------------------------------- ----------
NUM_CPUS 96
NUM_CPU_CORES 24
PHYSICAL_MEMORY_BYTES 7730941132
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 22144M
sga_target big integer 22144M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 7372M
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> select bytes/1e9,autoextensiable from dba_data_files where tablespace_name='UNDOTBS1';
BYTES/1E9 AUTOEXT
--------------------
8.55113728 YES
測試表:
SQL> select bytes/1e9 from user_segments where segment_name='CUST';
BYTES/1E9
----------
14.4388915
SQL> select /*+ parallel(t 8) */ count(*) from cust t;
COUNT(*)
----------
107007800
分片並行更新:
DECLARE
l_sql_stmt VARCHAR2(1000);
l_try NUMBER;
l_status NUMBER;
BEGIN
-- Create the TASK
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
-- Chunk the table by ROWID
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'BOCNET', 'CUST', false, 1000);
-- Execute the DML in parallel
l_sql_stmt := 'update /*+ ROWID (e) */ CUST e
SET e.mobile = ''123''
WHERE rowid BETWEEN :start_id AND :end_id';
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
parallel_level => 20);
-- If there is an error, RESUME it for at most 2 times.
L_try := 0;
L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
L_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
END LOOP;
-- Done with processing; drop the task
DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
END;
/
==>
PL/SQL procedure successfully completed
Executed in 391.235 seconds
在執行期間,在第二個會話中執行:
SQL>
SQL> select event,count(*) from v$session where wait_class<>'Idle'
2 group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file scattered read 9
log file parallel write 1
db file parallel write 10
control file sequential read 1
log buffer space 11
SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read 1
db file scattered read 4
latch: redo allocation 15
LGWR wait for redo copy 1
latch: object queue header operation 1
SQL> set time on
10:58:55 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file scattered read 3
latch: redo allocation 17
latch free 1
10:58:57 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
log file switch completion 19
log file parallel write 1
db file parallel write 3
log buffer space 1
11:00:19 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
latch: undo global data 1
db file scattered read 3
enq: HW - contention 3
Disk file operations I/O 1
enq: US - contention 4
latch: redo allocation 1
log file parallel write 1
db file parallel write 1
control file sequential read 1
buffer busy waits 6
10 rows selected
11:01:39 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
latch: cache buffers lru chain 4
db file scattered read 8
latch: redo allocation 6
Disk file operations I/O 2
LGWR wait for redo copy 1
11:02:13 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
ASM file metadata operation 1
db file scattered read 6
enq: US - contention 2
enq: HW - contention 2
log file parallel write 1
buffer busy waits 9
6 rows selected
11:02:57 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
enq: HW - contention 1
log file parallel write 1
undo segment extension 2
log file switch (checkpoint incomplete) 10
buffer busy waits 7
11:03:19 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read 1
db file scattered read 2
enq: US - contention 5
enq: HW - contention 1
log file parallel write 1
db file parallel write 2
control file sequential read 1
undo segment extension 1
buffer busy waits 9
9 rows selected
11:03:46 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read 1
db file scattered read 5
enq: US - contention 6
latch: redo allocation 2
enq: HW - contention 1
log file parallel write 1
Data file init write 1
buffer busy waits 4
8 rows selected
11:04:42 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
在執行期間,在第三個會話中執行:
SQL> select sum(used_ublk) from v$transaction;
SUM(USED_UBLK)
--------------
7938
SQL> r
SUM(USED_UBLK)
--------------
6875
SQL> set time on
10:59:03 SQL> r
SUM(USED_UBLK)
--------------
8027
11:00:15 SQL> r
SUM(USED_UBLK)
--------------
8413
11:00:46 SQL> r
SUM(USED_UBLK)
--------------
13125
11:00:50 SQL> select count(*),sum(used_ublk) from v$transaction;
COUNT(*) SUM(USED_UBLK)
---------- --------------
20 10701
11:01:35 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
20 9650
11:03:15 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
21 5391
11:03:42 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
20 6826
11:04:37 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
0
在執行期間,在第四個會話中執行:
SQL> select status,count(*) from user_parallel_execute_chunks where task_name='mytask' group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 19
UNASSIGNED 1635
PROCESSED 515
SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 1475
PROCESSED 674
SQL> set time on
10:59:10 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 1031
PROCESSED 1118
10:59:47 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 825
PROCESSED 1324
11:00:10 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 796
PROCESSED 1353
11:03:12 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 279
PROCESSED 1870
11:03:32 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 18
UNASSIGNED 219
PROCESSED 1932
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 61
PROCESSED 2088
11:04:52 SQL> r
STATUS COUNT(*)
-------------------- ----------
11:06:01 SQL>
如何在儘量短的時間內完成大批次資料的更新？如漂白資料時，需要將客戶表（約一億行）中的手機號設定為常量。
比如：投產時資料遷移
可以考慮的方法：
1，並行dml
一個事務，需要較大的回滾表空間
進度難以預測
並行dml bug較多
SQL> alter session enable parallel dml;
Session altered
Executed in 0.047 seconds
SQL> update /*+ parallel(e 20) */ CUST e SET e.mobile = '123';
107007800 rows updated
Executed in 183.285 seconds
SQL> commit;
Commit complete
Executed in 5.179 seconds
SQL> alter session disable parallel dml;
Session altered
Executed in 0.063 seconds
SID TYPE ID1 LMODE REQUEST BLOCK
---------- ---- ---------- ---------- ---------- ----------
109 TM 99947 1 0 0
109 TM 99947 4 0 0
154 TM 99947 1 0 0
154 TM 99947 4 0 0
166 TM 99947 6 0 1 --發起並行更新的會話
169 TM 99947 0 3 0 --對該表dml操作，被阻塞
181 TM 99947 1 0 0
181 TM 99947 4 0 0
184 TM 99947 1 0 0
184 TM 99947 4 0 0
187 TM 99947 1 0 0
187 TM 99947 4 0 0
190 TM 99947 1 0 0
190 TM 99947 4 0 0
193 TM 99947 1 0 0
193 TM 99947 4 0 0
196 TM 99947 1 0 0
196 TM 99947 4 0 0
199 TM 99947 1 0 0
199 TM 99947 4 0 0
202 TM 99947 1 0 0
202 TM 99947 4 0 0
205 TM 99947 1 0 0
205 TM 99947 4 0 0
208 TM 99947 1 0 0
208 TM 99947 4 0 0
211 TM 99947 1 0 0
211 TM 99947 4 0 0
214 TM 99947 1 0 0
214 TM 99947 4 0 0
217 TM 99947 1 0 0
217 TM 99947 4 0 0
220 TM 99947 1 0 0
220 TM 99947 4 0 0
223 TM 99947 1 0 0
223 TM 99947 4 0 0
226 TM 99947 1 0 0
226 TM 99947 4 0 0
229 TM 99947 1 0 0
229 TM 99947 4 0 0
232 TM 99947 1 0 0
232 TM 99947 4 0 0
42 rows selected
2，應用並行
靈活
步驟複雜
主要步驟：
*分片（chunk）
*多個分片並行更新
版本:
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
軟硬體環境:
SQL> select stat_name, value
2 from v$osstat
3 where stat_name in ('NUM_CPU_CORES', 'NUM_CPUS', 'PHYSICAL_MEMORY_BYTES');
STAT_NAME VALUE
---------------------------------------------------------------- ----------
NUM_CPUS 96
NUM_CPU_CORES 24
PHYSICAL_MEMORY_BYTES 7730941132
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 22144M
sga_target big integer 22144M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 7372M
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> select bytes/1e9,autoextensiable from dba_data_files where tablespace_name='UNDOTBS1';
BYTES/1E9 AUTOEXT
--------------------
8.55113728 YES
測試表:
SQL> select bytes/1e9 from user_segments where segment_name='CUST';
BYTES/1E9
----------
14.4388915
SQL> select /*+ parallel(t 8) */ count(*) from cust t;
COUNT(*)
----------
107007800
分片並行更新:
DECLARE
l_sql_stmt VARCHAR2(1000);
l_try NUMBER;
l_status NUMBER;
BEGIN
-- Create the TASK
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
-- Chunk the table by ROWID
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'BOCNET', 'CUST', false, 1000);
-- Execute the DML in parallel
l_sql_stmt := 'update /*+ ROWID (e) */ CUST e
SET e.mobile = ''123''
WHERE rowid BETWEEN :start_id AND :end_id';
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
parallel_level => 20);
-- If there is an error, RESUME it for at most 2 times.
L_try := 0;
L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
L_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
END LOOP;
-- Done with processing; drop the task
DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
END;
/
==>
PL/SQL procedure successfully completed
Executed in 391.235 seconds
在執行期間,在第二個會話中執行:
SQL>
SQL> select event,count(*) from v$session where wait_class<>'Idle'
2 group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file scattered read 9
log file parallel write 1
db file parallel write 10
control file sequential read 1
log buffer space 11
SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read 1
db file scattered read 4
latch: redo allocation 15
LGWR wait for redo copy 1
latch: object queue header operation 1
SQL> set time on
10:58:55 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file scattered read 3
latch: redo allocation 17
latch free 1
10:58:57 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
log file switch completion 19
log file parallel write 1
db file parallel write 3
log buffer space 1
11:00:19 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
latch: undo global data 1
db file scattered read 3
enq: HW - contention 3
Disk file operations I/O 1
enq: US - contention 4
latch: redo allocation 1
log file parallel write 1
db file parallel write 1
control file sequential read 1
buffer busy waits 6
10 rows selected
11:01:39 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
latch: cache buffers lru chain 4
db file scattered read 8
latch: redo allocation 6
Disk file operations I/O 2
LGWR wait for redo copy 1
11:02:13 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
ASM file metadata operation 1
db file scattered read 6
enq: US - contention 2
enq: HW - contention 2
log file parallel write 1
buffer busy waits 9
6 rows selected
11:02:57 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
enq: HW - contention 1
log file parallel write 1
undo segment extension 2
log file switch (checkpoint incomplete) 10
buffer busy waits 7
11:03:19 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read 1
db file scattered read 2
enq: US - contention 5
enq: HW - contention 1
log file parallel write 1
db file parallel write 2
control file sequential read 1
undo segment extension 1
buffer busy waits 9
9 rows selected
11:03:46 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read 1
db file scattered read 5
enq: US - contention 6
latch: redo allocation 2
enq: HW - contention 1
log file parallel write 1
Data file init write 1
buffer busy waits 4
8 rows selected
11:04:42 SQL> r
EVENT COUNT(*)
---------------------------------------------------------------- ----------
在執行期間,在第三個會話中執行:
SQL> select sum(used_ublk) from v$transaction;
SUM(USED_UBLK)
--------------
7938
SQL> r
SUM(USED_UBLK)
--------------
6875
SQL> set time on
10:59:03 SQL> r
SUM(USED_UBLK)
--------------
8027
11:00:15 SQL> r
SUM(USED_UBLK)
--------------
8413
11:00:46 SQL> r
SUM(USED_UBLK)
--------------
13125
11:00:50 SQL> select count(*),sum(used_ublk) from v$transaction;
COUNT(*) SUM(USED_UBLK)
---------- --------------
20 10701
11:01:35 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
20 9650
11:03:15 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
21 5391
11:03:42 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
20 6826
11:04:37 SQL> r
COUNT(*) SUM(USED_UBLK)
---------- --------------
0
在執行期間,在第四個會話中執行:
SQL> select status,count(*) from user_parallel_execute_chunks where task_name='mytask' group by status;
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 19
UNASSIGNED 1635
PROCESSED 515
SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 1475
PROCESSED 674
SQL> set time on
10:59:10 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 1031
PROCESSED 1118
10:59:47 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 825
PROCESSED 1324
11:00:10 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 796
PROCESSED 1353
11:03:12 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 279
PROCESSED 1870
11:03:32 SQL> r
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 18
UNASSIGNED 219
PROCESSED 1932
STATUS COUNT(*)
-------------------- ----------
ASSIGNED 20
UNASSIGNED 61
PROCESSED 2088
11:04:52 SQL> r
STATUS COUNT(*)
-------------------- ----------
11:06:01 SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-762381/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【oracle】使用DBMS_PARALLEL_EXECUTE並行更新表OracleParallel並行
- 使用11g dbms_parallel_execute執行並行更新（下）Parallel並行
- 使用11g dbms_parallel_execute執行並行更新（上）Parallel並行
- 使用dbms_parallel_execute來完成DML的並行Parallel並行
- 使用APT對redhat進行更新(轉)APTRedhat
- 如何使用 CatBoost 進行快速梯度提升梯度
- 使用更新補丁對Android Studio進行更新。Android
- 使用Excel進行快速模糊查詢的方法Excel
- 資料庫使用flashback進行快速回退資料庫
- 使用RMAN進行快速Dataguard資料庫建立資料庫
- PHP進階，使用互動模式進行快速測試實驗？PHP模式
- 使用JMeter進行負載測試快速入門JMeter負載
- 快速使用 vscode 進行 Java 程式設計VSCodeJava程式設計
- Oracle11.2新特性之使用DBMS_PARALLEL_EXECUTE包實現並行OracleParallel並行
- <<快速入手Rust>>1.使用playlaygroud進行簡單快速的線上rust開發Rust
- 教你如何在SDK開發使用美團Robust進行熱更新
- 用c#進行快速排序C#排序
- 快速進行安裝工作(轉)
- 使用Spring Reactive MongoDB進行自定義更新查詢 -Yuri MednikovSpringReactMongoDB
- 使用遊標迴圈進行SQL更新插入的SQL語句SQL
- 利用 Bootstrap 進行快速 Web開發bootWeb
- python:利用asyncio進行快速抓取Python
- 如何快速對影像進行分幅？
- 11G R2中的並行執行，dbms_parallel_execute並行Parallel
- REST：使用PATCH進行部分更新 - mscharhagREST
- APPCAN 如何快速的進行除錯？APPPCA除錯
- 使用SemanticKernel 進行智慧應用開發（2023-10更新）
- 瀏覽器漏洞正被利用，谷歌、火狐使用者儘快進行更新瀏覽器谷歌
- 11g DBMS_PARALLEL_EXECUTEParallel
- 使用RMAN增量更新備份實現快速還原
- Laravel_Model_Cache（針對快速更新的數值屬性進行快取優化的外掛）Laravel快取優化
- 建站教程：利用Bootstrap進行快速Web開發bootWeb
- 如何快速開始進行echart元件開發元件
- 業務需求：使用遊標對查詢出來的記錄進行更新過程中，不允許其它人進行更改
- 使用Redis進行限流Redis
- Deadmin 更新部分文件介紹快速使用方法
- 【Android】使用Bugly快速接入Tinker熱更新功能Android
- ****阿里雲使用+快速運維總結(不斷更新)阿里運維