【oracle】使用DBMS_PARALLEL_EXECUTE並行更新表

楊奇龍發表於2011-12-21
OracleParallel並行
在11.2 版本中使用DBMS_PARALLEL_EXECUTE包批次並行遞增式的更新表。
1 把資料集分割成小的塊
2 在每一個塊上以並行的方式應用update語句，在每個塊執行完成後，提交！
此更新技術有如下好處：
1 在執行update的時候，僅僅鎖住一個shunk而非鎖住整個表！
2 因為每個chunk 執行完畢就提交，所以當update操作失敗後，之前變更的並不會回滾！
3 減小回滾空間的使用
4 提高效能
DBMS_PARALLEL_EXECUTE 使用三種方法來將一個表的資料分割成chunk
CREATE_CHUNKS_BY_NUMBER_COL : 透過指定的欄位來切割表
CREATE_CHUNKS_BY_ROWID : 透過ROWID來切割表
CREATE_CHUNKS_BY_SQL : 透過使用者提供的sql語句來切割表
前期準備：
使用上述功能的使用者必須擁有CREATE JOB 許可權，執行DBMS_SQL的許可權，因為CHUNK_BY_SQL, RUN_TASK, 和RESUME_TASK 
conn /as sysdba
GRANT Create Session, Resource to yang identified by yang; 
GRANT CREATE JOB TO yang;

YANG@yangdb-rac3> INSERT /*+ APPEND */ INTO yangtab
  2  SELECT level,
  3  'Description for ' || level,
  4  CASE
  5  WHEN MOD(level, 5) = 0 THEN 10
  6  WHEN MOD(level, 3) = 0 THEN 20
  7  ELSE 30
  8  END
  9  FROM dual
 10  CONNECT BY level <= 500000;

500000 rows created.
YANG@yangdb-rac3> 
YANG@yangdb-rac3> commit;
Commit complete.      
YANG@yangdb-rac3> SELECT num_col, COUNT(*) FROM yangtab
  2  GROUP BY num_col
  3  ORDER BY num_col;
   NUM_COL   COUNT(*)
---------- ----------
        10     100000
        20     133333
        30     266667
        
1. 使用 CREATE_CHUNKS_BY_ROWID
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
END;
/
YANG@yangdb-rac3> DECLARE
  2    l_task VARCHAR2(30) := 'test_task';
  3    l_sql_stmt VARCHAR2(32767);
  4    l_try NUMBER;
  5    l_status NUMBER;
  6  BEGIN
  7  -- Create the TASK
  8    DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
  9  
 10  -- Chunk the table by the ROWID
 11    DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,
 12     table_owner => 'YANG',
 13     table_name => 'YANGTAB',
 14     by_row => TRUE,
 15     chunk_size => 10000);
 16  -- DML to be execute in parallel
 17     l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ yangtab t SET t.num_col = t.num_col + 10 WHERE rowid BETWEEN :start_id AND :end_id';
 18  -- Run the task
 19   DBMS_PARALLEL_EXECUTE.run_task(task_name => l_task,
 20     sql_stmt => l_sql_stmt,
 21     language_flag => DBMS_SQL.NATIVE,
 22     parallel_level => 10);
 23  
 24  -- If there is error, RESUME it for at most 2 times.
 25    l_try := 0;
 26    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
 27    WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
 28    Loop
 29     l_try := l_try + 1;
 30     DBMS_PARALLEL_EXECUTE.resume_task(l_task);
 31     l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
 32    END LOOP;
 33  -- Done with processing; drop the task
 34    DBMS_PARALLEL_EXECUTE.drop_task(l_task);
 35  END;
 36  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:26.27
YANG@yangdb-rac3> SELECT num_col, COUNT(*)
  2  FROM yangtab
  3  GROUP BY num_col
  4  ORDER BY num_col; 
   NUM_COL   COUNT(*)
---------- ----------
        20     100000
        30     133333
        40     266667
2. 使用 CREATE_CHUNKS_BY_NUMBER_COL
YANG@yangdb-rac3> CREATE OR REPLACE PROCEDURE process_update (p_start_id IN NUMBER, p_end_id IN NUMBER) AS
  2    BEGIN
  3      UPDATE /*+ ROWID (dda) */ yangtab t 
  4        SET t.num_col = t.num_col + 10
  5        WHERE id BETWEEN p_start_id AND p_end_id;
  6    END;
  7  /
Procedure created.
YANG@yangdb-rac3> DECLARE
  2    l_task VARCHAR2(30) := 'test_task';
  3    l_sql_stmt VARCHAR2(32767);
  4    l_try NUMBER;
  5    l_status NUMBER;
  6  BEGIN
  7  -- Create the TASK
  8    DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
  9  
 10  -- Chunk the table by the ROWID
 11    DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,
 12     table_owner => 'YANG',
 13     table_name => 'YANGTAB',
 14     by_row => TRUE,
 15     chunk_size => 10000);
 16  -- DML to be execute in parallel
 17     l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ yangtab t SET t.num_col = t.num_col + 10 WHERE rowid BETWEEN :start_id AND :end_id';
 18  -- Run the task
 19   DBMS_PARALLEL_EXECUTE.run_task(task_name => l_task,
 20     sql_stmt => l_sql_stmt,
 21     language_flag => DBMS_SQL.NATIVE,
 22     parallel_level => 10);
 23  
 24  -- If there is error, RESUME it for at most 2 times.
 25    l_try := 0;
 26    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
 27    WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
 28    Loop
 29     l_try := l_try + 1;
 30     DBMS_PARALLEL_EXECUTE.resume_task(l_task);
 31     l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
 32    END LOOP;
 33  -- Done with processing; drop the task
 34    DBMS_PARALLEL_EXECUTE.drop_task(l_task);
 35  END;
 36  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.18
YANG@yangdb-rac3> SELECT num_col, COUNT(*)
  2  FROM yangtab
  3  GROUP BY num_col
  4  ORDER BY num_col; 
   NUM_COL   COUNT(*)
---------- ----------
        30     100000
        40     133333
        50     266667
Elapsed: 00:00:00.12
3. 使用 CREATE_CHUNKS_BY_SQL.
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
END;
/
YANG@yangdb-rac3> DECLARE
2   l_chunk_sql VARCHAR2(1000);
3   l_sql_stmt VARCHAR2(1000);
4   l_try NUMBER;
5   l_status NUMBER;
6  BEGIN
7-- Create the TASK
8  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('test_task');
9-- Chunk the table by NUM_COL
10  l_chunk_sql := 'SELECT DISTINCT num_col, num_col FROM yangtab';
11  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('test_task', l_chunk_sql, false);
12-- Execute the DML in parallel
13-- the WHERE clause contain a condition on num_col, which is the chunk
14-- column. In this case, grouping rows is by num_col.
15  l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ yangtab t SET t.num_col = t.num_col + 10 WHERE num_col BETWEEN :start_id AND :end_id';
16  DBMS_PARALLEL_EXECUTE.RUN_TASK('test_task', l_sql_stmt, DBMS_SQL.NATIVE,
17  parallel_level => 10);
18-- If there is error, RESUME it for at most 2 times.
19 L_try := 0;
20 L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
21 WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
22 Loop
23   L_try := l_try + 1;
24   DBMS_PARALLEL_EXECUTE.RESUME_TASK('test_task');
25   L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
26 END LOOP;
27-- Done with processing; drop the task
28 DBMS_PARALLEL_EXECUTE.DROP_TASK('test_task');
29 end;
30 /
PL/SQL procedure successfully completed.
YANG@yangdb-rac3> SELECT num_col, COUNT(*)
  2  FROM yangtab
  3  GROUP BY num_col
  4  ORDER BY num_col; 
   NUM_COL   COUNT(*)
---------- ----------
        42     100000
        52     133333
        62     266667

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-713736/，如需轉載，請註明出處，否則將追究法律責任。

相關文章