让语句横着走————对海量数据更新的并行优化
[English]
作者：
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 CTSET CT.LAST_MOD_DT = (SELECT TL.MSG_GMT_DTFROM CS2_TXN_LOG TLWHERE TL.MSG_ID > 9000000000000000000AND CT.MSG_ID = TL.MSG_IDAND TL.MSG_GMT_DT IS NOT NULL)WHERE EXISTS (SELECT 1FROM CS2_TXN_LOG TLWHERE TL.MSG_ID > 9000000000000000000AND CT.MSG_ID = TL.MSG_IDAND TL.MSG_GMT_DT IS NOT NULL);
根据开发人员对数据的估算，在此逻辑下，CS2_CT_MVMT将会有近一半数据（近1亿条）要被更新，相对应的，数据来自于CS2_TXN_LOG中的近1一亿条数据。开发人员曾经尝试过运行语句，但是3天都没执行完。
第一步，对语句进行调优
首先，先看下语句的查询计划：
SQL> set line 300SQL> set pages 50SQL> explain plan for2 UPDATE CS2_CT_MVMT CT3 SET CT.LAST_MOD_DT = (SELECT TL.MSG_GMT_DT4 FROM CS2_TXN_LOG TL5 WHERE TL.MSG_ID > 90000000000000000006 AND CT.MSG_ID = TL.MSG_ID7 AND TL.MSG_GMT_DT IS NOT NULL)8 WHERE EXISTS (SELECT 19 FROM CS2_TXN_LOG TL10 WHERE TL.MSG_ID > 900000000000000000011 AND CT.MSG_ID = TL.MSG_ID12 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 PK2 from all_constraints p, all_constraints c3 where c.table_name in ('CS2_CT_MVMT','CS2_TXN_LOG')4 and c.r_constraint_name = p.constraint_name5 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_PKSQL> select table_name, column_name from dba_ind_columns2 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 CTSET CT.LAST_MOD_DT = (SELECT TL.MSG_GMT_DTFROM CS2_TXN_LOG TLWHERE TL.MSG_ID > 9000000000000000000AND CT.MSG_ID = TL.MSG_IDAND TL.MSG_GMT_DT IS NOT NULL);
但这样会产生一个问题：子查询的记录数可能为1或0（1:1），也就是将近1半为NULL（即对应的CS2_CT_MVMT记录无需更新）。很简单，我们用NVL函数来处理：
UPDATE CS2_CT_MVMT CTSET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DTFROM CS2_TXN_LOG TLWHERE TL.MSG_ID > 9000000000000000000AND CT.MSG_ID = TL.MSG_IDAND 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 CTSET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DTFROM CS2_TXN_LOG TLWHERE CT.MSG_ID = TL.MSG_IDAND 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 CTSET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DTFROM CS2_TXN_LOG TLWHERE 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 selectedSQL> SELECT s.constraint_name, search_condition2 FROM DBA_CONSTRAINTS S, DBA_CONS_COLUMNS C3 WHERE s.table_name='CS2_CT_MVMT'4 AND s.constraint_name=c.constraint_name5 and s.table_name=c.table_name6 and c.column_name='LAST_MOD_DT';CONSTRAINT_NAME SEARCH_CONDITION------------------------------ --------------------------------------------SYS_C0013690 "LAST_MOD_DT" IS NOT NULLSQL> SELECT index_name FROM DBA_IND_COLUMNS2 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 CTSET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DTFROM CS2_TXN_LOG TLWHERE 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 parallelNAME TYPE VALUE------------------------------------ ----------- --------------------------fast_start_parallel_rollback string LOWparallel_adaptive_multi_user boolean TRUEparallel_automatic_tuning boolean FALSEparallel_execution_message_size integer 2152parallel_instance_group stringparallel_max_servers integer 0parallel_min_percent integer 0parallel_min_servers integer 0parallel_server boolean TRUEparallel_server_instances integer 3parallel_threads_per_cpu integer 2recovery_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_text2 from v$session s, v$sqlarea sq3 where s.sql_address = sq.address4 order by sql_id, program;SID SERIAL# PROGRAM MODULE SQL_ID---------- ---------- ------------------------------------------------ ----SQL_TEXT---------------------------------------------------------------------------1892 6069 oracle@pmrac01 (P000) SQL*Plus 51b0kx3g5gbfxUPDATE /*+ PARALLEL(CT,8) */ CS2_CT_MVMT CT SET CT.LAST_MOD_DT = NVL((SELECT TL.MSG_GMT_DT