Shulou(Shulou.com)06/01 Report--

Application of 11g packet dbms_parallel_execute in massive data processing

1. 1 BLOG document structure map

I. 2 preface part 1. 2.1 introduction

Technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) Olympiad:

Application of ① 11g packet dbms_parallel_execute in massive data processing

Note: I use the * * background and red font to express the areas that require special attention in the code part of this BLOG. For example, in the following example, the maximum archive log number of thread 1 is 33 thread 2, and the maximum archive log number is 43.

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

-

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

I. 2.2 introduction to the experimental environment

11.2.0.1 RHEL6.5

I. 2.3 links to related reference articles

How to update a large table record in Oracle

Http://blog.itpub.net/26736162/viewspace-1684095/

Performing parallel updates using 11g dbms_parallel_execute (part two)

Http://blog.itpub.net/26736162/viewspace-1683913/

Performing parallel updates using 11g dbms_parallel_execute (part I)

Http://blog.itpub.net/26736162/viewspace-1683912/

I. 2.4 introduction to this article

A friend own_my has to deal with bulk data, but the script runs too slowly, so he searched the dbms_parallel_execute package on the Internet and told me that the package was very powerful, so I also learned that optimization has always been my favorite content. After referring to the blog of the great god realkid4, I also did the experiment myself. I feel very powerful and recorded here.

I. 3 Literacy of related knowledge points

Refer to the blog: http://blog.itpub.net/17203031/ of the Great God

I. 4 Experimental part 1. 4.1 Experimental objectives

Test the application of dbms_parallel_execute package in the process of massive data processing.

I. 4.2 Experimental process

[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.segmentations

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.segmentations

BYTES/1024/1024

-

4341

Elapsed: 00:00:00.41

15:50:31 SQL > declare

15:50:39 2 vc_task varchar2

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 name

15:50:39 9 dbms_parallel_execute.create_task (task_name = > vc_task);-- define a Task task manually

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);-- define 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);-- perform tasks to determine parallelism

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

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'

16:02:52 14 table_name = >'T'

16:02:52 15 table_column = > 'OBJECT_ID'

16:02:52 16 chunk_size = > 100000);-- define chunk

16:02:53 17 16:02:53 18 vc_sql: = 'update / * + ROWID (dda) * / t set DATA_OBJECT_ID=object_id+1 where object_id between: start_id and: end_id'

16:02:53 19-Run the task

16:02:53 20 dbms_parallel_execute.run_task (task_name = > vc_task)

16:02:53 21 sql_stmt = > vc_sql

16:02:53 22 language_flag = > dbms_sql.native

16:02:53 23 parallel_level = > 4)

16:02:53 24

16:02:53 25-Controller

16:02:53 26 n_try: = 0

16:02:53 27 n_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

16:02:53 28 while (n_try

< 2 and n_status != dbms_parallel_execute.FINISHED) loop 16:02:53 29 dbms_parallel_execute.resume_task(task_name =>

Vc_task)

16:02:53 30 n_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

16:02:53 31 end loop

16:02:53 32

16:02:53 33-Deal with Result

16:02:53 34 dbms_parallel_execute.drop_task (task_name = > vc_task)

16:02:53 35 end

16:02:53 36 /

^ Cdeclare

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at "SYS.DBMS_LOCK", line 201

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 44

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 390

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 417

ORA-06512: at line 20

Elapsed: 00:07:12.08

16:11:36 SQL >

16:11:36 SQL > EXEC dbms_parallel_execute.drop_task (task_name = > 'Task 2: By Number Col')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11

16:31:53 SQL > declare

16:32:05 2 vc_task varchar2

16:32:05 3 vc_sql varchar2 (1000)

16:32:05 4 vc_sql_mt varchar2 (1000)

16:32:05 5 n_try number

16:32:05 6 n_status number

16:32:05 7 begin

16:32:05 8-Define the Task

16:32:05 9 vc_task: = 'Task 3: By SQL'

16:32:05 10 dbms_parallel_execute.create_task (task_name = > vc_task)

16:32:05 11

16:32:05 12-Define the Spilt

16:32:05 13 vc_sql_mt: = 'select distinct object_id, object_id from t'

16:32:05 14 dbms_parallel_execute.create_chunks_by_SQL (task_name = > vc_task)

16:32:05 15 sql_stmt = > vc_sql_mt

16:32:05 16 by_rowid = > false)

16:32:05 17

16:32:05 18 vc_sql: = 'update / * + ROWID (dda) * / t set DATA_OBJECT_ID=object_id+1 where object_id between: start_id and: end_id'

16:32:05 19-Run the task

16:32:05 20 dbms_parallel_execute.run_task (task_name = > vc_task)

16:32:05 21 sql_stmt = > vc_sql

16:32:05 22 language_flag = > dbms_sql.native

16:32:05 23 parallel_level = > 4)

16:32:05 24

16:32:05 25-Controller

16:32:05 26 n_try: = 0

16:32:05 27 n_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

16:32:05 28 while (n_try

< 2 and n_status != dbms_parallel_execute.FINISHED) loop 16:32:05 29 dbms_parallel_execute.resume_task(task_name =>

Vc_task)

16:32:05 30 n_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

16:32:05 31 end loop

16:32:05 32

16:32:05 33-Deal with Result

16:32:05 34 dbms_parallel_execute.drop_task (task_name = > vc_task)

16:32:05 35 end

16:32:05 36 /

^ Cdeclare

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE_INTERNAL", line 634

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 163

ORA-06512: at line 14

Elapsed: 00:01:09.08

16:33:14 SQL > EXEC dbms_parallel_execute.drop_task (task_name = > 'Task 3: By SQL')

PL/SQL procedure successfully completed.

I. 4.2.1 related dictionary view query I. create_chunks_by_rowid process

SELECT * FROM DBA_PARALLEL_EXECUTE_TASKS

SELECT * FROM DBA_PARALLEL_EXECUTE_CHUNKS

SELECT count (1) FROM DBA_PARALLEL_EXECUTE_CHUNKS

Select status, count (*) from user_parallel_execute_chunks group by status

Select D.ownerMagneD.jobkeeper nameJournal D.JOBBQSTYLERED JOBBZTYPEE D.JOBBECTION action from dba_scheduler_jobs d where d.ownerkeeper LHR'

Alarm log:

Wed Jun 03 15:53:48 2015

Archived Log entry 1202 added for thread 1 sequence 2669 ID 0x6779dfc4 dest 1:

Thread 1 advanced to log sequence 2671 (LGWR switch)

Current log# 4 seq# 2671 mem# 0: / app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_4_bpxd8g7v_.log

Wed Jun 03 15:53:49 2015

Archived Log entry 1203 added for thread 1 sequence 2670 ID 0x6779dfc4 dest 1:

Wed Jun 03 15:53:57 2015

Thread 1 advanced to log sequence 2672 (LGWR switch)

Current log# 5 seq# 2672 mem# 0: / app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_5_bpxdbwdz_.log

Wed Jun 03 15:53:58 2015

Archived Log entry 1204 added for thread 1 sequence 2671 ID 0x6779dfc4 dest 1:

Thread 1 advanced to log sequence 2673 (LGWR switch)

Current log# 1 seq# 2673 mem# 0: / app/oracle/oradata/CNYDB/redo01.log

Wed Jun 03 15:54:04 2015

Archived Log entry 1205 added for thread 1 sequence 2672 ID 0x6779dfc4 dest 1:

Thread 1 advanced to log sequence 2674 (LGWR switch)

Current log# 6 seq# 2674 mem# 0: / app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_6_bpxdcjx2_.log

Wed Jun 03 15:54:05 2015

Archived Log entry 1206 added for thread 1 sequence 2673 ID 0x6779dfc4 dest 1:

From the alarm log, we can see that the redo switch is very fast and there is no time for archiving, so we still need to do experiments in our spare time.

2. Create_chunks_by_number_col process

SELECT * FROM DBA_PARALLEL_EXECUTE_CHUNKS

SELECT * FROM DBA_PARALLEL_EXECUTE_TASKS

Select status, count (*) from dba_parallel_execute_chunks group by status

Select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$%'

Select D.ownerMagneD.jobkeeper nameJournal D.JOBBQSTYLERED JOBBZTYPEE D.JOBBECTION action from dba_scheduler_jobs d where d.ownerkeeper LHR'

I. 4.3 summary of experiments

As can be seen from the experiment, using the dbms_parallel_execute.create_chunks_by_rowid method, 40 million data about 4G size table update about 4 minutes, this speed is OK, the other two ways to update the speed is too slow, there is no test, for details, please refer to here: http://blog.itpub.net/26736162/viewspace-1683912/, http://blog.itpub.net/26736162/viewspace-1683913/.

I.4.4 Experimental script-4.4.1 create_chunks_by_rowid mode

Declare

Vc_task varchar2 (100)

Vc_sql varchar2 (1000)

N_try number

N_status number

Begin

-- Define the Task

Vc_task: = 'Task 1: By Rowid';-- Task name

Dbms_parallel_execute.create_task (task_name = > vc_task);-- define a Task task manually

-- Define the Spilt

Dbms_parallel_execute.create_chunks_by_rowid (task_name = > vc_task)

Table_owner = > 'LHR'

Table_name = >'T'

By_row = > true

Chunk_size = > 10000);-- define Chunk

Vc_sql: = 'update / * + ROWID (dda) * / t set DATA_OBJECT_ID=object_id+1 where rowid between: start_id and: end_id'

-- Run the task

Dbms_parallel_execute.run_task (task_name = > vc_task)

Sql_stmt = > vc_sql

Language_flag = > dbms_sql.native

Parallel_level = > 4);-- perform tasks to determine parallelism

-- Controller

N_try: = 0

N_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

While (n_try

< 2 and n_status != dbms_parallel_execute.FINISHED) loop dbms_parallel_execute.resume_task(task_name =>

Vc_task)

N_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

End loop

-- Deal with Result

Dbms_parallel_execute.drop_task (task_name = > vc_task)

End

/

I. 4.4.2 create_chunks_by_number_col

Declare

Vc_task varchar2 (100)

Vc_sql varchar2 (1000)

N_try number

N_status number

Begin

-- Define the Task

Vc_task: = 'Task 2: By Number Col'

Dbms_parallel_execute.create_task (task_name = > vc_task)

-- Define the Spilt

Dbms_parallel_execute.create_chunks_by_number_col (task_name = > vc_task)

Table_owner = > 'LHR'

Table_name = >'T'

Table_column = > 'OBJECT_ID'

Chunk_size = > 10000);-- define chunk

Vc_sql: = 'update / * + ROWID (dda) * / t set DATA_OBJECT_ID=object_id+1 where object_id between: start_id and: end_id'

-- Run the task

Dbms_parallel_execute.run_task (task_name = > vc_task)

Sql_stmt = > vc_sql

Language_flag = > dbms_sql.native

Parallel_level = > 4)

-- Controller

N_try: = 0

N_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

While (n_try

< 2 and n_status != dbms_parallel_execute.FINISHED) loop dbms_parallel_execute.resume_task(task_name =>

Vc_task)

N_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

End loop

-- Deal with Result

Dbms_parallel_execute.drop_task (task_name = > vc_task)

End

/

I. 4.4.3 create_chunks_by_SQL

Declare

Vc_task varchar2 (100)

Vc_sql varchar2 (1000)

Vc_sql_mt varchar2 (1000)

N_try number

N_status number

Begin

-- Define the Task

Vc_task: = 'Task 3: By SQL'

Dbms_parallel_execute.create_task (task_name = > vc_task)

-- Define the Spilt

Vc_sql_mt: = 'select distinct object_id, object_id from t'

Dbms_parallel_execute.create_chunks_by_SQL (task_name = > vc_task)

Sql_stmt = > vc_sql_mt

By_rowid = > false)

Vc_sql: = 'update / * + ROWID (dda) * / t set DATA_OBJECT_ID=object_id+1 where object_id between: start_id and: end_id'

-- Run the task

Dbms_parallel_execute.run_task (task_name = > vc_task)

Sql_stmt = > vc_sql

Language_flag = > dbms_sql.native

Parallel_level = > 4)

-- Controller

N_try: = 0

N_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

While (n_try

< 2 and n_status != dbms_parallel_execute.FINISHED) loop dbms_parallel_execute.resume_task(task_name =>

Vc_task)

N_status: = dbms_parallel_execute.task_status (task_name = > vc_task)

End loop

-- Deal with Result

Dbms_parallel_execute.drop_task (task_name = > vc_task)

End

/

One. 5 About Me

. .

The author: wheat seedlings, only focus on the technology of database, pay more attention to the application of technology.

ITPUB BLOG: http://blog.itpub.net/26736162

Address: http://blog.itpub.net/26736162/viewspace-1684396/

Pdf version of this article: http://yunpan.cn/QCwUAI9bn7g7w extraction code: af2d

If you add QQ to QQ:642808185, please indicate the title of the article you are reading

Creation time and place: 2015-06-03 10 00 ~ 2015-06-03 18:00 at × × trading center

. .

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.