作者： fuyuncat
来源： www.HelloDBA.com
| 环境： 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分析并行过程 》。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
