Oracle并行操作——并行DML操作

对大部分的OLTP系统而言，并行DML（PDML）的应用场景不多。大多数的PDML操作集中在下面几个场景下：



ü       系统移植，从旧系统中导入原始数据和基础数据；

ü       数据仓库系统Data Warehouse定期进行大批量原始数据导入和清洗；

ü       借助一些专门的工具，如sql loader，进行数据海量导入；



本篇主要介绍并行DML操作的一些细节和注意方面。



1、环境准备



Oracle并行操作前提两个条件，其一是盈余的软硬件资源，其二是海量的大数据量操作。



//操作系统和DB环境

SQL> select * from v$version where rownum<2;



BANNER

--------------------------------------------------------------------------------------------

Oracle Database11gEnterpriseEdition Release11.2.0.1.0 - Production



SQL> show parameter cpu_count;



NAME                                TYPE                  VALUE

------------------------------------ ---------------------- ------------------------------

cpu_count                           integer               4

SQL>



//数据环境

SQL> select count(*) from t;



COUNT(*)

----------

10039808



Executed in 4.072 seconds







2、并行统计量收集



为了实现CBO的正常工作，我们通常要保证Oracle数据字典中保留有关于数据表完全的统计信息描述。统计信息包括数据行数、取值分布、离散程度等等指标。收集统计量是一项比较重要的工作。当数据表很大的时候，即使使用了比例抽样的方法，进行汇总统计的数据量也是很大。所以这种场合下，是可以应用到并行技术的。



在目前的Oracle版本中，通常是使用dbms_stats包进行统计量收集。相对于过去的analyze table xxx命令，dbms_stats包对于统计量收集更加完全，应对分区状况更好。在dbms_stats方法中，存在参数degree，表示并行度，可以直接指定希望的收集并行度。





--收集统计量，指定并行度

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,degree => 7);

PL/SQL procedure successfully completed



Executed in15.32seconds





系统使用15.32s的时间完成了收集。



在收集过程中，我们观察v$px_session和v$px_process两个视图的状态。检查并行伺服进程池的状况。



SQL> select * from v$px_process;



SERVER_NAME STATUS          PID SPID           SID   SERIAL#

----------- --------- ---------- ------------------------ ---------- ----------

P006       INUSE          100 19070982            35     50729

P001       INUSE           65 13107452           178     35585

P002       INUSE           73 9633888            184     25268

P003       INUSE           85 22478986           223     33339

P000       INUSE           63 18743314           500     16029

P004       INUSE           95 14221380           509     26446

P005       INUSE           99 23068708           510     20895



7 rows selected





系统依据并行度要求，分配了7个进程进行操作。



//并行会话信息

SQL> select * from v$px_session;



SADDR  SID   SERIAL#     QCSID QCSERIAL#     DEGREE REQ_DEGREE

---------------- ---------- ---------- ---------- ---------- ---------- ----------

070000007D2BA680       500     16029       324     26152          7  7

070000007FE7EC70       178     35585       324     26152          7  7

070000007FE6D5D0       184     25268       324     26152          7  7

070000007FDFC2C0       223     33339       324     26152          7  7

070000007D2A0490       509     26446       324     26152          7  7

070000007D29D620       510     20895       324     26152          7   7

070000007FC94480        35     50729       324     26152          7   7

070000007D12FB00       324     26152       324           

（篇幅原因，有截取结果……）

8 rows selected





注意，在请求了并行度degree=7的情况下，Oracle根据CPU数量分配了7个并行slave进程进行操作。会话层面，七个slave进程分别对应七个会话信息进行并行操作。同时，存在一个额外会话（sid=324），充当全局协调者coordinator的角色。v$px_session中的qcsid字段含义为“Session serial number of the parallel coordinator”，就是并行操作中扮演协调者角色的进程。





如果不使用并行收集，只是简单的串行收集，我们查看一下效率情况。





//指定串行

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,degree => 1);

PL/SQL procedure successfully completed



Executed in46.816seconds





效果清晰可见，从原来的15s多的收集时间，放大为47s左右，几乎是三倍的损耗。





结论：对于统计量收集而言，如果作业时间可以避开业务高峰时间窗口，进行并行操作收集统计量还是一个不错的选择。





3、并行insert操作



下面进行并行insert操作，我们选择使用hint来进行并行控制。



//开启PDML的开关

SQL> alter session enable parallel dml;

Session altered



Executed in 0.016 seconds



使用hint，开启8个并行度进行insert操作。





--并行insert

SQL>insert /*+ parallel(t,8) */ into t select * from t;

10039808 rows inserted



Executed in 76.238 seconds





运行过程中，出现的并行操作过程如下。



//开启8个并行度；

SQL> select * from v$px_session;



SADDR                  SID   SERIAL#     QCSID QCSERIAL#

---------------- ---------- ---------- ---------- ----------

070000007FFF52E0       361      3123       324     26152

070000007FE84950       176     50028       324     26152

070000007FE7EC70       178     35508       324     26152 

070000007FE0AAF0       218      5994       324     26152

070000007D29D620       510     20829       324     26152

070000007D2A0490       509     26391       324     26152

070000007FC94480        35     50615       324     26152 

070000007FFFAFC0       359     32516       324     26152 

070000007D12FB00       324     26152       324           



9 rows selected



SQL> select * from v$px_process;

SERVER_NAME STATUS          PID SPID                   SID SERIAL#

----------- --------- ---------- ------------------------ ---------- ----------

P006       INUSE          100 19005590                        35     50615

P001       INUSE           69 19398710                       176     50028

P002       INUSE           73 9633968                        178     35508

P003       INUSE           85 23068694                       218      5994

P007       INUSE          102 18743298                       359     32516

P000       INUSE           66 14221352                       361      3123

P005       INUSE           99 21233884                       509     26391

P004       INUSE           95 19071188                       510     20829



8 rows selected





此时，我们尝试抽取出执行计划。



//从shared_pool中尝试获取到指定的记录；

SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'insert /*+ parallel(t,8) */%';



SQL_TEXT                       SQL_ID       VERSION_COUNT

-------------------------------------------------- ------------- -------------

insert /*+ parallel(t,8) */ into t select * from t  67wymm0jhw3gv            2



Executed in 0.234 seconds





利用sql_id，尝试抽取出shared_pool中的执行计划。



//抽取出执行计划，篇幅原因，有删节……

SQL> select * from table(dbms_xplan.display_cursor('67wymm0jhw3gv',format => 'advanced',cursor_child_no => 1));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID 67wymm0jhw3gv, child number 1

-------------------------------------

insert /*+ parallel(t,8) */ into t select * from t

Plan hash value: 4064487821



----------------------------------------------------------------------------------------------------

| Id | Operation            | Name    | Rows | Bytes | Cost (%CPU)| Time    |   TQ |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------

|  0 | INSERT STATEMENT     |         |      |      | 2718 (100)|         |       |     |           |

|  1 | PX COORDINATOR      |         |      |      |           |         |       |     |           |

|  2 |  PX SEND QC (RANDOM) | :TQ10000 | 5019K|  469M| 2718  (1)| 00:00:33 | Q1,00 | P->S | Q

|  3 |   LOAD AS SELECT    |         |      |      |           |         | Q1,00 | PCWP |           |

|  4 |    PX BLOCK ITERATOR |         | 5019K|  469M| 2718  (1)| 00:00:33 | Q1,00 | PCWC |           |

|* 5 |     TABLE ACCESS FULL| T       | 5019K|  469M| 2718  (1)| 00:00:33 | Q1,00 | PCWP |           |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  5 - access(:Z>=:Z AND :Z<=:Z)

Note

-----

  -automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

已选择66行。



已用时间: 00: 00: 00.40





如果不使用并行操作，进行如此规模的insert操作，会如何呢？



//使用noparallel的hint进行并行抑制；



SQL>insert /*+ noparallel */ into t select * from t;

10039808 rows inserted



Executed in 87.813 seconds





对应的执行计划如下：





SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'insert /*+ noparallel */%';



SQL_TEXT                               SQL_ID VERSION_COUNT

-------------------------------------------------- ------------- -------------

insert /*+ noparallel */ into t select * from t   9u0xcrr3bcjs1            1



Executed in 0.234 seconds







SQL> select * from table(dbms_xplan.display_cursor('9u0xcrr3bcjs1',format => 'advanced',cursor_child_no => 0));



PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

SQL_ID 9u0xcrr3bcjs1, child number 0

-------------------------------------

insert /*+ noparallel */ into t select * from t



Plan hash value: 2153619298

---------------------------------------------------------------------------------

| Id | Operation               | Name | Rows | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------------

|  0 | INSERT STATEMENT        |       |      |      | 19601 (100)|         |

|  1 | LOAD TABLE CONVENTIONAL |     |      |      |           |     |

|  2 |  TABLE ACCESS FULL     | T   | 5019K|  469M| 19601  (1)| 00:03:56 |

---------------------------------------------------------------------------------







4、结论



本篇对PDML进行了简单的介绍，包括使用方法和并行度设置。由于篇幅原因，只介绍了并行insert和并行统计量的收集。并行update和delete本质相同，就不加以累述了。



最后，并行操作是一种带有特殊性的操作，绝对不要将其轻易作为经常性无监管下的操作。