合 Oracle11g包dbms_parallel_execute在海量数据处理过程中的应用
Tags： Oracledbms_parallel_execute
实验环境介绍
11.2.0.1 RHEL6.5
本文简介
一个朋友own_my要处理批量数据，但是脚本跑的太慢了，于是网上搜到了dbms_parallel_execute这个包，用完后给我说这个包非常强大，于是我也学习学习，关于优化一直是我喜欢的内容，在参考了大神realkid4 的blog后，我自己也做了做实验，感觉很强大，记录在此。
实验部分
实验目标
测试dbms_parallel_execute包在海量数据处理过程中的应用。
实验过程
[oracle@etlhost206 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 3 13:40:34 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CONN LHR/lhr
Connected.
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> insert into t select * from t;
76369 rows created.
SQL> insert into t select * from t;
152738 rows created.
SQL> insert into t select * from t;
305476 rows created.
SQL> COMMIT;
Commit complete.
SQL> insert into t select * from t;
610952 rows created.
SQL> insert into t select * from t;
1221904 rows created.
SQL> insert into t select * from t;
2443808 rows created.
SQL> insert into t select * from t;
4887616 rows created.
SQL> COMMIT;
Commit complete.
SQL> insert into t select * from t;
9775232 rows created.
SQL> COMMIT;
Commit complete.
SQL> insert into t select * from t;
19550464 rows created.
SQL> COMMIT;
Commit complete.
SQL> select bytes/1024/1024 from dba_segments a where a.segment_name='T';
BYTES/1024/1024
---------------
4341
SQL> SELECT COUNT(1) FROM T;
COUNT(1)
----------
39100928
SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 8
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 8
SQL> set timing on
SQL> set time on;
15:50:01 SQL>
15:50:02 SQL> show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
15:50:09 SQL> select bytes/1024/1024 from dba_segments a where a.segment_name='T';
BYTES/1024/1024
---------------
4341
Elapsed: 00:00:00.41
15:50:31 SQL> declare
15:50:39 2 vc_task varchar2(100);
15:50:39 3 vc_sql varchar2(1000);
15:50:39 4 n_try number;
15:50:39 5 n_status number;
15:50:39 6 begin
15:50:39 7 --Define the Task
15:50:39 8 vc_task := 'Task 1: By Rowid'; --Task名称
15:50:39 9 dbms_parallel_execute.create_task(task_name => vc_task); --手工定义一个Task任务；
15:50:39 10
15:50:39 11 --Define the Spilt
15:50:39 12 dbms_parallel_execute.create_chunks_by_rowid(task_name => vc_task,
15:50:39 13 table_owner => 'LHR',
15:50:39 14 table_name => 'T',
15:50:39 15 by_row => true,
15:50:39 16 chunk_size => 10000); --定义Chunk
15:50:39 17
15:50:39 18 vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where rowid between :start_id and :end_id';
15:50:40 19 --Run the task
15:50:40 20 dbms_parallel_execute.run_task(task_name => vc_task,
15:50:40 21 sql_stmt => vc_sql,
15:50:40 22 language_flag => dbms_sql.native,
15:50:40 23 parallel_level => 4); --执行任务，确定并行度
15:50:40 24
15:50:40 25 --Controller
15:50:40 26 n_try := 0;
15:50:40 27 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
15:50:40 28 while (n_try \< 2 and n_status != dbms_parallel_execute.FINISHED) loop
15:50:40 29 dbms_parallel_execute.resume_task(task_name => vc_task);
15:50:40 30 n_status := dbms_parallel_execute.task_status(task_name => vc_task);
15:50:40 31 end loop;
15:50:40 32
15:50:40 33 --Deal with Result
15:50:40 34 dbms_parallel_execute.drop_task(task_name => vc_task);
15:50:40 35 end;
15:50:40 36 /
PL/SQL procedure successfully completed.
Elapsed: 00:03:50.78
15:58:05 SQL>
15:58:06 SQL> create index idx_t_id on t(object_id) nologging parallel 4;
Index created.
Elapsed: 00:01:35.12
16:00:05 SQL> alter index idx_t_id noparallel;
Index altered.
Elapsed: 00:00:00.07
16:00:15 SQL>
16:02:51 SQL> declare
16:02:52 2 vc_task varchar2(100);
16:02:52 3 vc_sql varchar2(1000);
16:02:52 4 n_try number;
16:02:52 5 n_status number;
16:02:52 6 begin
16:02:52 7 --Define the Task
16:02:52 8 vc_task := 'Task 2: By Number Col';
16:02:52 9 dbms_parallel_execute.create_task(task_name => vc_task);
16:02:52 10
16:02:52 11 --Define the Spilt
16:02:52 12 dbms_parallel_execute.create_chunks_by_number_col(task_name => vc_task,
16:02:52 13 table_owner => 'LHR',
