让语句横着走————对海量数据更新的并行优化

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-05-07 14:57:45

分享到 

环境： OS：linux CPU：8个 Oracle Database：10.2.0.3.0       接到开发组的一个调优请求，任务是对一张海量表CS2_CT_MVMT（近2亿多记录，表大小48G）进行数据update，而更新数据来自于另外一张海量的日志表CS2_TXN_LOG（同样近2亿，表大小42G），数据处理的语句如下：   UPDATE CS2_CT_MVMT CT          SET CT.LAST_MOD_DT = (SELECT TL.MSG_GMT_DT                                    FROM CS2_TXN_LOG TL                                  WHERE TL.MSG_ID > 9000000000000000000                                    AND CT.MSG_ID = TL.MSG_ID                                    AND TL.MSG_GMT_DT IS NOT NULL) WHERE EXISTS (SELECT 1                 FROM CS2_TXN_LOG TL                WHERE TL.MSG_ID > 9000000000000000000                  AND CT.MSG_ID = TL.MSG_ID                  AND TL.MSG_GMT_DT IS NOT NULL);   根据开发人员对数据的估算，在此逻辑下，CS2_CT_MVMT将会有近一半数据（近1亿条）要被更新，相对应的，数据来自于CS2_TXN_LOG中的近1一亿条数据。开发人员曾经尝试过运行语句，但是3天都没执行完。   第一步，对语句进行调优 首先，先看下语句的查询计划： SQL> set line 300 SQL> set pages 50 SQL> explain plan for   2  UPDATE CS2_CT_MVMT CT   3     SET CT.LAST_MOD_DT = (SELECT TL.MSG_GMT_DT   4                             FROM CS2_TXN_LOG TL   5                            WHERE TL.MSG_ID > 9000000000000000000   6                              AND CT.MSG_ID = TL.MSG_ID   7                              AND TL.MSG_GMT_DT IS NOT NULL)   8   WHERE EXISTS (SELECT 1   9            FROM CS2_TXN_LOG TL 10           WHERE TL.MSG_ID > 9000000000000000000 11             AND CT.MSG_ID = TL.MSG_ID 12             AND TL.MSG_GMT_DT IS NOT NULL);   Explained.   SQL> select * from table(dbms_xplan.display());   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- Plan hash value: 3604468536   --------------------------------------------------------------------------- | Id  | Operation                     | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | UPDATE STATEMENT              |                |    11M|   432M|       |  3580K  (1)| 11:56:06 | |   1 |  UPDATE                       | CS2_CT_MVMT    |       |       |       |            |          | |*  2 |   HASH JOIN SEMI              |                |    11M|   432M|   352M|  3580K  (1)| 11:56:06 | |*  3 |    TABLE ACCESS FULL          | CS2_CT_MVMT    |    11M|   216M|       |  1690K  (1)| 05:38:12 | |*  4 |    TABLE ACCESS FULL          | CS2_TXN_LOG    |   214M|  3888M|       |  1552K  (1)| 05:10:25 | |*  5 |   FILTER                      |                |       |       |       |            |          | |*  6 |    TABLE ACCESS BY INDEX ROWID| CS2_TXN_LOG    |     1 |    19 |       |     4   (0)| 00:00:01 | |*  7 |     INDEX UNIQUE SCAN         | CS2_TXN_LOG_PK |     1 |       |       |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("CT"."MSG_ID"="TL"."MSG_ID")    3 - filter("CT"."MSG_ID">9000000000000000000)    4 - filter("TL"."MSG_ID">9000000000000000000 AND "TL"."MSG_GMT_DT" IS NOT NULL)    5 - filter(9000000000000000000<:B1)   PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------    6 - filter("TL"."MSG_GMT_DT" IS NOT NULL)    7 - access("TL"."MSG_ID"=:B1)        filter("TL"."MSG_ID">9000000000000000000)   25 rows selected.   从查询计划看，两表之间存在一个hash join。再看看两表之间的关系：   SQL> select c.table_name as child_table, c.constraint_name as FK, p.table_name as parent_table, p.constraint_name as PK   2  from all_constraints p, all_constraints c   3  where c.table_name in ('CS2_CT_MVMT','CS2_TXN_LOG')   4  and c.r_constraint_name = p.constraint_name   5  and c.constraint_type = 'R'   6  and p.constraint_type='P';   CHILD_TABLE                    FK                             PARENT_TABLE                   PK ------------------------------ ------------------------------ ------------- CS2_CT_MVMT                    CS2_CT_MVMT_FK1                CS2_TXN_LOG                    CS2_TXN_LOG_PK   SQL> select table_name, column_name from dba_ind_columns   2  where index_name = 'CS2_TXN_LOG_PK';   TABLE_NAME                     COLUMN_NAME ------------------------------ ------------------------------ CS2_TXN_LOG                    MSG_ID   可见，CS2_CT_MVMT是依赖于CS2_TXN_LOG的子表，其依赖字段MSG_ID正是CS2_TXN_LOG的主键唯一字段。我们可以对这条语句的逻辑描述如下： CS2_CT_MVMT依据主外键关系从父表CS2_TXN_LOG中更新相应数据，且只从CS2_TXN_LOG获取满足(MSG_ID > 9000000000000000000 AND MSG_GMT_DT IS NOT NULL)。我们可以这个逻辑关系对语句进行调整以减少查询计划中的访问路径： 根据业务分析，CS2_CT_MVMT中将近一半的数据满足更新条件，也就是由hash join只能过滤一半的数据，而在SET子句中的子查询已经保证了两表数据的完整性关系已经对父表的数据过滤条件。这样的话，这个join的代价就太大了，它应该从语句中拿掉： UPDATE CS2_CT_MVMT CT          SET CT.LAST_MOD_DT = (SELECT TL.MSG_GMT_DT                                    FROM CS2_TXN_LOG TL                                  WHERE TL.MSG_ID > 9000000000000000000                                    AND CT.MSG_ID = TL.MSG_ID                                    AND TL.MSG_GMT_DT IS NOT NULL);   但这样会产生一个问题：子查询的记录数可能为1或0（1:1），也就是将近1半为NULL（即对应的CS2_CT_MVMT记录无需更新）。很简单，我们用NVL函数来处理： UPDATE CS2_CT_MVMT CT          SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                                      FROM CS2_TXN_LOG TL                                    WHERE TL.MSG_ID > 9000000000000000000                                       AND CT.MSG_ID = TL.MSG_ID                                       AND TL.MSG_GMT_DT IS NOT NULL), CT.LAST_MOD_DT); 从查询计划中看到，COST将近一半：   --------------------------------------------------------------------------- | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | UPDATE STATEMENT              |                |   119M|  2160M|  1699K  (2)| 05:39:57 | |   1 |  UPDATE                       | CS2_CT_MVMT    |       |       |            |          | |   2 |   TABLE ACCESS FULL           | CS2_CT_MVMT    |   119M|  2160M|  1699K  (2)| 05:39:57 | |*  3 |   FILTER                      |                |       |       |            |          | |*  4 |    TABLE ACCESS BY INDEX ROWID| CS2_TXN_LOG    |     1 |    19 |     4   (0)| 00:00:01 | |*  5 |     INDEX UNIQUE SCAN         | CS2_TXN_LOG_PK |     1 |       |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - filter(9000000000000000000<:B1)    4 - filter("TL"."MSG_GMT_DT" IS NOT NULL)    5 - access("TL"."MSG_ID"=:B1)        filter("TL"."MSG_ID">9000000000000000000)   再进一步，由于存在等价关系CT.MSG_ID = TL.MSG_ID，我们可以将TL.MSG_ID > 9000000000000000000转换为CT.MSG_ID > 9000000000000000000，作为对CS2_CT_MVMT扫描后的filter，从而大大减少UPDATE操作，也大大减少子查询的运行次数。   UPDATE CS2_CT_MVMT CT          SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                                      FROM CS2_TXN_LOG TL                                    WHERE CT.MSG_ID = TL.MSG_ID                                       AND TL.MSG_GMT_DT IS NOT NULL), CT.LAST_MOD_DT) WHERE CT.MSG_ID > 9000000000000000000;   --------------------------------------------------------------------------- | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | UPDATE STATEMENT             |                |    11M|   216M|  1690K  (1)| 05:38:12 | |   1 |  UPDATE                      | CS2_CT_MVMT    |       |       |            |          | |*  2 |   TABLE ACCESS FULL          | CS2_CT_MVMT    |    11M|   216M|  1690K  (1)| 05:38:12 | |*  3 |   TABLE ACCESS BY INDEX ROWID| CS2_TXN_LOG    |     1 |    19 |     4   (0)| 00:00:01 | |*  4 |    INDEX UNIQUE SCAN         | CS2_TXN_LOG_PK |     1 |       |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("CT"."MSG_ID">9000000000000000000)    3 - filter("TL"."MSG_GMT_DT" IS NOT NULL)    4 - access("TL"."MSG_ID"=:B1)   最后再看子查询部分：对于每条满足条件的CS2_CT_MVMT中的记录，都将执行一次子查询，由CS2_TXN_LOG的主键获取其表数据块中的MSG_GMT_DT数据。子查询包含了3步操作： 1. 对主键的INDEX UNIQUE SCAN，获取表记录的ROWID； 2. 由ROWID读取表的数据块； 3. 根据条件MSG_GMT_DT IS NOT NULL进行过滤   可以预计，子查询的执行次数也是巨大的，我们可以考虑通过建立复合字段索引来消除第二步操作。而通过与开发人员确认，实际上CS2_TXN_LOG中MSG_GMT_DT为NULL的数据极少，也就是MSG_GMT_DT IS NOT NULL过滤的数据极少，NVL函数已经可以处理这些数据，我们就可以将这个条件拿掉。   SQL> CREATE UNIQUE INDEX CS2_PARTY_OWNER.CS2_TXN_LOG_TEST ON CS2_TXN_LOG (MSG_ID, MSG_GMT_DT);   Index created.   语句及查询计划如下：   UPDATE CS2_CT_MVMT CT          SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                                        FROM CS2_TXN_LOG TL                                       WHERE CT.MSG_ID = TL.MSG_ID), CT.LAST_MOD_DT) WHERE CT.MSG_ID > 9000000000000000000;   --------------------------------------------------------------------------- | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | UPDATE STATEMENT   |                  |    11M|   216M|  1690K  (1)| 05:38:12 | |   1 |  UPDATE            | CS2_CT_MVMT      |       |       |            |          | |*  2 |   TABLE ACCESS FULL| CS2_CT_MVMT      |    11M|   216M|  1690K  (1)| 05:38:12 | |*  3 |   INDEX RANGE SCAN | CS2_TXN_LOG_TEST |     1 |    19 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("CT"."MSG_ID">9000000000000000000)    3 - access("TL"."MSG_ID"=:B1)   通过逻辑分析和查询计划分析对语句的查询部分优化基本上完成。但是这个结果还远不能满足1小时的系统off line时间完成数据更新的目标。   由于这是一次表更新操作，因此我们就还需要考虑一些依赖对象对更新操作的影响。   第二步，消除依赖对象的性能影响 我们看下被更新表上有一些什么依赖对象会影响到更新性能。   SQL> SELECT trigger_name FROM DBA_TRIGGERS WHERE table_name='CS2_CT_MVMT' AND triggering_event like '%UPDATE%';   no rows selected   SQL> SELECT s.constraint_name, search_condition   2  FROM DBA_CONSTRAINTS S, DBA_CONS_COLUMNS C   3  WHERE s.table_name='CS2_CT_MVMT'   4  AND s.constraint_name=c.constraint_name   5  and s.table_name=c.table_name   6  and c.column_name='LAST_MOD_DT';   CONSTRAINT_NAME                SEARCH_CONDITION ------------------------------ -------------------------------------------- SYS_C0013690                   "LAST_MOD_DT" IS NOT NULL   SQL> SELECT index_name FROM DBA_IND_COLUMNS   2  WHERE TABLE_NAME='CS2_CT_MVMT'   3  AND column_name='LAST_MOD_DT';   INDEX_NAME ------------------------------ CS2_CT_MVMT_IDX7   在批量更新之前，将这些依赖对象和约束先禁用将有助于提高更新性能。   SQL> ALTER TABLE CS2_CT_MVMT MODIFY LAST_MOD_DT NULL;   Table altered.   提示：我们已经通过逻辑保证了LAST_MOD_DT不被更新为NULL，这个约束对整体性能的影响很小，可以不用关闭。   SQL> drop index CS2_CT_MVMT_IDX7;   Index dropped.   Tips：有人可能会想到将表修改为NOLOGGING，以禁止写REDO LOG。但事实上，NOLOGGING只对Direct Write的INSERT起作用，对UPDATE，总是会写REDO LOG。有兴趣的朋友可以做个简单实验去验证一下。   第三步，将语句并行化 这一步起的作用并不是提高整体性能，而是使过程在更短的时间内占用更大的负载来完成任务。 给语句加上并行化提示   UPDATE /*+parallel(CT 8)*/ CS2_CT_MVMT CT          SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                                        FROM CS2_TXN_LOG TL                                       WHERE CT.MSG_ID = TL.MSG_ID), CT.LAST_MOD_DT) WHERE CT.MSG_ID > 9000000000000000000;   看查询计划：   --------------------------------------------------------------------------- | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | UPDATE STATEMENT   |                  |    11M|   216M|  1690K  (1)| 05:38:12 | |   1 |  UPDATE            | CS2_CT_MVMT      |       |       |            |          | |*  2 |   TABLE ACCESS FULL| CS2_CT_MVMT      |    11M|   216M|  1690K  (1)| 05:38:12 | |*  3 |   INDEX RANGE SCAN | CS2_TXN_LOG_TEST |     1 |    19 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------   嗯，没其作用。看看系统的parallel设置：   SQL> show parameter parallel   NAME                                 TYPE        VALUE ------------------------------------ ----------- -------------------------- fast_start_parallel_rollback         string      LOW parallel_adaptive_multi_user         boolean     TRUE parallel_automatic_tuning            boolean     FALSE parallel_execution_message_size      integer     2152 parallel_instance_group              string parallel_max_servers                 integer     0 parallel_min_percent                 integer     0 parallel_min_servers                 integer     0 parallel_server                      boolean     TRUE parallel_server_instances            integer     3 parallel_threads_per_cpu             integer     2 recovery_parallelism                 integer     0   注意到，parallel_max_servers被设置为0了，因此不会起并行进程。修改设置，   SQL> alter system set parallel_max_servers=160 scope=memory;   System altered.   再次获取查询计划   --------------------------------------------------------------------------- | Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib | --------------------------------------------------------------------------- |   0 | UPDATE STATEMENT      |                  |    11M|   216M|   234K  (1)| 00:46:50 |        |      |            | |   1 |  UPDATE               | CS2_CT_MVMT      |       |       |            |          |        |      |            | |   2 |   PX COORDINATOR      |                  |       |       |            |          |        |      |            | |   3 |    PX SEND QC (RANDOM)| :TQ10000         |    11M|   216M|   234K  (1)| 00:46:50 |  Q1,00 | P->S | QC (RAND)  | |   4 |     PX BLOCK ITERATOR |                  |    11M|   216M|   234K  (1)| 00:46:50 |  Q1,00 | PCWC |            | |*  5 |      TABLE ACCESS FULL| CS2_CT_MVMT      |    11M|   216M|   234K  (1)| 00:46:50 |  Q1,00 | PCWP |            | |*  6 |   INDEX RANGE SCAN    | CS2_TXN_LOG_TEST |     1 |    19 |     4   (0)| 00:00:01 |        |      |            | ---------------------------------------------------------------------------   语句已经并行化了。   尝试运行语句，看看效果。     SQL> select s.sid, s.SERIAL#, s.PROGRAM, s.MODULE, sq.SQL_ID, sq.sql_text   2  from v$session s, v$sqlarea sq   3  where s.sql_address = sq.address   4  order by sql_id, program;          SID    SERIAL# PROGRAM                                          MODULE                                           SQL_ID ---------- ---------- ------------------------------------------------ ---- SQL_TEXT ---------------------------------------------------------------------------       1892       6069 oracle@pmrac01 (P000)                            SQL*Plus                                         51b0kx3g5gbfx UPDATE /*+ PARALLEL(CT,8) */ CS2_CT_MVMT CT    SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                            FROM CS2_TXN_LOG TL                        WHERE CT.MSG_ID = TL.MSG_ID),CT.LAST_MOD_DT) WHERE CT.MSG_ID > 9000000000000000000       1961      23363 oracle@pmrac01 (P001)                            SQL*Plus                                         51b0kx3g5gbfx UPDATE /*+ PARALLEL(CT,8) */ CS2_CT_MVMT CT    SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                            FROM CS2_TXN_LOG TL                        WHERE CT.MSG_ID = TL.MSG_ID),CT.LAST_MOD_DT) WHERE CT.MSG_ID > 9000000000000000000 ... ...       1898       7240 oracle@pmrac01 (P007)                            SQL*Plus                                         51b0kx3g5gbfx UPDATE /*+ PARALLEL(CT,8) */ CS2_CT_MVMT CT    SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                            FROM CS2_TXN_LOG TL                        WHERE CT.MSG_ID = TL.MSG_ID),CT.LAST_MOD_DT) WHERE CT.MSG_ID > 9000000000000000000       1889       6026 sqlplus@pmrac01 (TNS V1-V3)                      SQL*Plus                                         51b0kx3g5gbfx UPDATE /*+ PARALLEL(CT,8) */ CS2_CT_MVMT CT    SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT                            FROM CS2_TXN_LOG TL                        WHERE CT.MSG_ID = TL.MSG_ID),CT.LAST_MOD_DT) WHERE CT.MSG_ID > 9000000000000000000   可以注意到，已经有9个会话在执行该语句（1个协调进程(coordinator)，8个并行进程(slave)）。sql_id是51b0kx3g5gbfx。再观察这些会话产生的统计数据：   SQL> select s.sid, s.SERIAL#, n.NAME, ss.value, s.USERNAME,s.LOGON_TIME,s.MODULE   2  from v$sesstat ss, v$statname n, v$session s, v$px_session px   3  where n.name in ('physical write bytes', 'physical read bytes', 'undo change vector size','redo size')   4  and ss.statistic#=n.STATISTIC#   5  and ss.SID = s.SID   6  and ss.SID = px.SID   7  and not exists (select 1 from v$mystat m where s.sid=m.sid) order by n.name desc,ss.value desc;          SID    SERIAL# NAME                                         VALUE ---------- ---------- -------------------------------------- -----------       1889       6026 undo change vector size                  374128088       1892       6069 undo change vector size                          0       1893      10815 undo change vector size                          0       1954       2616 undo change vector size                          0       1935      11165 undo change vector size                          0       1945       7426 undo change vector size                          0       1961      23363 undo change vector size                          0       1832       6515 undo change vector size                          0       1898       7240 undo change vector size                          0       1889       6026 redo size                               1140869748       1961      23363 redo size                                        0       1832       6515 redo size                                        0       1892       6069 redo size                                        0       1893      10815 redo size                                        0       1898       7240 redo size                                        0       1935      11165 redo size                                        0       1954       2616 redo size                                        0       1945       7426 redo size                                        0       1954       2616 physical write bytes                             0       1945       7426 physical write bytes                             0       1935      11165 physical write bytes                             0       1898       7240 physical write bytes                             0       1893      10815 physical write bytes                             0       1892       6069 physical write bytes                             0       1889       6026 physical write bytes                             0       1832       6515 physical write bytes                             0       1961      23363 physical write bytes                             0       1889       6026 physical read bytes                     3674587136       1832       6515 physical read bytes                     2610708480       1935      11165 physical read bytes                     2233417728       1945       7426 physical read bytes                     1317404672       1892       6069 physical read bytes                     1291378688       1893      10815 physical read bytes                     1283899392       1954       2616 physical read bytes                     1242357760       1898       7240 physical read bytes                     1230888960       1961      23363 physical read bytes                     1193918464   36 rows selected.   发现问题了：只有一个进程（即主进程）产生了Redo和Undo log，所有并行进程都没有redo和undo产生，也就是只有一个进程在进行写操作，尽管读已经并行化了。 再看会话的并行属性：   SQL> select s.sid, s.SERIAL#, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.PROGRAM   2  from v$session s, v$px_session px   3  where s.sid = px.sid   4  and not exists (select 1 from v$mystat m where s.sid=m.sid)；          SID    SERIAL# PDML_STA PDDL_STA PQ_STATU ---------- ---------- -------- -------- --------       1792       3473 DISABLED ENABLED  ENABLED       1827      12996 DISABLED ENABLED  ENABLED       1872       4173 DISABLED ENABLED  ENABLED       1889       6998 DISABLED ENABLED  ENABLED       1910      16279 DISABLED ENABLED  ENABLED       1916       3073 DISABLED ENABLED  ENABLED       1935      12587 DISABLED ENABLED  ENABLED       1953       3125 DISABLED ENABLED  ENABLED       1958      19949 DISABLED ENABLED  ENABLED   9 rows selected.   看到PDML是diabled。说明语句只进行了parallel query，而没有达到parallel DML（PDML）的目的。为了实现PDML，还需要在会话中打开parallel DML的开关   SQL> ALTER SESSION FORCE PARALLEL DML;   Session altered.   再次运行语句，观察统计情况：   SQL> select s.sid, s.SERIAL#, n.NAME, ss.value   2  from v$sesstat ss, v$statname n, v$session s, v$sqlarea sq   3  where n.name in ('physical write bytes', 'physical read bytes', 'undo change vector size','redo size')   4  and ss.statistic#=n.STATISTIC#   5  and s.sql_address = sq.address   6  and ss.SID = s.SID   7  and sq.sql_id='51b0kx3g5gbfx'   8  order by n.name desc,ss.value desc;          SID    SERIAL# NAME                                           VALUE ---------- ---------- -------------------------------------- --------------       1779        524 undo change vector size                      10659808       1806       8301 undo change vector size                      10592844       1935      11332 undo change vector size                      9842040 ... ...       1838       8657 redo size                                    22625612       1806       8301 redo size                                    22488968       1935      11332 redo size                                    20878408       1892       6131 redo size                                    15077588 ... ...       1935      11332 physical write bytes                                0       1838       8657 physical write bytes                                0       1901      28061 physical write bytes                                0 ... ...       1838       8657 physical read bytes                         631848960       1935      11332 physical read bytes                         625991680       1872       3007 physical read bytes                         559521792 ... ...   68 rows selected.   会话信息中，PDML已经强制使用   SQL> select s.sid, s.SERIAL#, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.PROGRAM   2  from v$session s, v$px_session px   3  where s.sid = px.sid   4  and not exists (select 1 from v$mystat m where s.sid=m.sid)；          SID    SERIAL# PDML_STA PDDL_STA PQ_STATU ---------- ---------- -------- -------- --------       1779        524 FORCED   ENABLED  ENABLED       1783      13993 FORCED   ENABLED  ENABLED       1792       3477 FORCED   ENABLED  ENABLED       1806       8481 FORCED   ENABLED  ENABLED       1812       2874 FORCED   ENABLED  ENABLED       1819      24796 FORCED   ENABLED  ENABLED       1838       8756 FORCED   ENABLED  ENABLED       1842       5929 FORCED   ENABLED  ENABLED       1872       4177 FORCED   ENABLED  ENABLED       1889       7002 FORCED   ENABLED  ENABLED       1894      13805 FORCED   ENABLED  ENABLED       1910      16366 FORCED   ENABLED  ENABLED       1916       3077 FORCED   ENABLED  ENABLED       1927       6182 FORCED   ENABLED  ENABLED       1935      12591 FORCED   ENABLED  ENABLED       1958      19955 FORCED   ENABLED  ENABLED       1959       9259 FORCED   ENABLED  ENABLED   17 rows selected.   细心的朋友可能会留意到在我的HINT中指定的Parallel Degree是8，而在这却出现了16个slave进程。我们看看在设置PDML语句的查询计划变化就知道原因了：   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------- Plan hash value: 4032879153   -------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------- |   0 | UPDATE STATEMENT         |                  |    11M|   216M|   124K  (1)| 00:24:53 |        |      |            | |   1 |  PX COORDINATOR          |                  |       |       |            |          |        |      |            | |   2 |   PX SEND QC (RANDOM)    | :TQ10001         |    11M|   216M|   124K  (1)| 00:24:53 |  Q1,01 | P->S | QC (RAND)  | |   3 |    INDEX MAINTENANCE     | CS2_CT_MVMT      |       |       |            |          |  Q1,01 | PCWP |            | |   4 |     PX RECEIVE           |                  |    11M|   216M|   124K  (1)| 00:24:53 |  Q1,01 | PCWP |            | |   5 |      PX SEND RANGE       | :TQ10000         |    11M|   216M|   124K  (1)| 00:24:53 |  Q1,00 | P->P | RANGE      | |   6 |       UPDATE             | CS2_CT_MVMT      |       |       |            |          |  Q1,00 | PCWP |            | |   7 |        PX BLOCK ITERATOR |                  |    11M|   216M|   124K  (1)| 00:24:53 |  Q1,00 | PCWC |            | |*  8 |         TABLE ACCESS FULL| CS2_CT_MVMT      |    11M|   216M|   124K  (1)| 00:24:53 |  Q1,00 | PCWP |            | |*  9 |        INDEX RANGE SCAN  | CS2_TXN_LOG_TEST |     1 |    19 |     4   (0)| 00:00:01 |        |      |            | --------------------------------------------------------------------------------------------------------------------------   在查询计划中，slave进程集的数量是2个（TQ字段，Q1,00和Q1,01），因此，进程总量就变为2*8=16了。 这样，这条语句就真正实现了PDML。最终，我们设置该语句的并行度为32，在生产环境上的执行时间是25分钟，加上索引重建的时间，总共时间不到1小时，实现了优化目的。   Bug：说实话，Oracle的PDML还不是十分成熟，在我们的测试调优过程中，遇到了多个Bug（参见Metalink上Bug 4896424、Bug 5914711）。其中还有一个Oracle还在定位中（当Parallel Degree大于32时随机出现）。这些bug在10g中都没有补丁，虽然它们都有规避办法，但是如果直接在online时使用PDML总还是让人不放心，好彩我们的程序只是一个运行一次的数据补丁，并不会直接影响生产系统。   补充1：parallel DML的效果在很大程度上还取决于磁盘IO的并行程度。有人可能会有一个误解，认为parallel DML只能在分区表上起作用。但事实上，从9iR2开始，oracle的intra-partition技术支持在单分区的parallel DML（update,merge.在9iR1开始支持单分区的parallel Insert）。   补充2：在分分区表或者单个分区上并行进程数量受到Min Transaction Freelists的限制。在MSSM的段管理方式中，可以参考下表可（并非一定等于该数值，因为不同的数据块结构会有不同ITL数或其他信息，这些不同都会影响到Min Transaction Freelists）。在ASSM中，Oracle通过bitmap管理free block，这一限制最大可以达到65535。 Block Size Min Transaction Freelists 2k         25 4k         50 8k         101 16k        204 32k        409  33：在我的调试过程中，曾经发生一件令人费解的事——有时从会话中找不到并行会话！最终，通过的方法找到了原因，具体可参见这片文章：《通过Parallel Trace分析并行过程》。