并行操作为什么无法执行

在一次系统割接的时候，我们碰到一个十分奇怪的现象。由于进行系统迁移，因此很多大表在数据导入时没有创建索引，导入结束后需要重建索引。为了加快索引的创建速度，我们需要并行建索引。虽然在创建索引的脚本中加入了PARALLEL40，但是实际上，创建索引的操作还是串行的。

这时一套拥有64个核的系统，并行创建索引可以成倍的提高速度。而无法是用并行会严重影响割接前的准备工作。因此这个原因需要尽快查清。碰到这样的问题，首先我们要做的事情就是先检查一下并行的相关参数设置：

SQL>showparameterparallel

NAMETYPEVALUE

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

fast_start_parallel_rollbackstringLOW

parallel_adaptive_multi_userbooleanTRUE

parallel_automatic_tuningbooleanFALSE

parallel_execution_message_sizeinteger2152

parallel_instance_groupstringXXXX31

parallel_max_serversinteger1000

parallel_min_percentinteger0

parallel_min_serversinteger10

parallel_serverbooleanTRUE

parallel_server_instancesinteger2

parallel_threads_per_cpuinteger2

recovery_parallelisminteger0

可以看到，PARALLEL相关的参数设置并无问题，PARALLEL_MAX_SERVERS是1000,PARALLEL_MIN_SERVERS是10。通过ps命令：

oracle@test31:/oracle$ps-ef|grepp0

oracle1304410Oct20?0:04ora_p008_test31

oracle1303810Oct20?0:04ora_p005_test31

oracle1302910Oct20?0:04ora_p003_test31

oracle1302710Oct20?0:04ora_p002_test31

oracle642510Oct18?0:08ora_psp0_test31

oracle1303110Oct20?0:04ora_p004_test31

oracle1302510Oct20?0:04ora_p001_test31

oracle1304010Oct20?0:04ora_p006_test31

oracle1302310Oct20?0:04ora_p000_test31

oracle1304610Oct20?0:04ora_p009_test31

oracle1304210Oct20?0:04ora_p007_test31

可以看出目前只启动了10个并行进程，也就是PARALLEL_MIN_SERVERS指定的数量。从ps的结果可以看出并行进程的启动是正常的。在ALERTLOG中也没有看到相关的错误活着警告信息。看样子这个问题有点奇怪了，为了尽快定位问题，我们首先要创建一个测试环境：

createtablexuji_testtablespacesysauxasselect*fromdba_objects;

altertablexuji_testparallel20;

selectcount(*)fromxuji_test;

我们首先通过DBA_OBJECTS创建了一张有6万多条记录的表xuji_test，然后将这张表的并行度设置为20，然后做一个COUNT(*)操作，SQL执行后，从V$SQLAREA找到这条SQL的SQL_ID，然后查看执行计划：

SQL>select*fromtable(dbms_xplan.display_cursor('8sj2h9nsq7s4h',null,'ADVANCED'));

selectcount(*)fromxuji_test

Planhashvalue:3609358487

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

|Id|Operation|Name|Rows|TQ|IN-OUT|PQDistrib|

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

|0|SELECTSTATEMENT||||||

|1|SORTAGGREGATE||1||||

|2|PXCOORDINATOR||||||

|3|PXSENDQC(RANDOM)|:TQ10000|1|Q1,00|P->S|QC(RAND)|

|4|SORTAGGREGATE||1|Q1,00|PCWP||

|5|PXBLOCKITERATOR||61059|Q1,00|PCWC||

|*6|TABLEACCESSFULL|XUJI_TEST|61059|Q1,00|PCWP||

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

QueryBlockName/ObjectAlias(identifiedbyoperationid):

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

1-SEL$1

PLAN_TABLE_OUTPUT

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

6-SEL$1/XUJI_TEST@SEL$1

OutlineData

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

PLAN_TABLE_OUTPUT

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

FULL(@"SEL$1""XUJI_TEST"@"SEL$1")

END_OUTLINE_DATA

*/

PredicateInformation(identifiedbyoperationid):

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

6-access(:Z>=:ZAND:Z<=:Z)

ColumnProjectionInformation(identifiedbyoperationid):

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

PLAN_TABLE_OUTPUT

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

1-(#keys=0)COUNT()[22]

2-SYS_OP_MSR()[10]

3-(#keys=0)SYS_OP_MSR()[10]

4-(#keys=0)SYS_OP_MSR()[10]

从执行计划上看，好像这个SQL是走了并行查询。不过从ps的结果来看，并行进程并无改变：

oracle@test31:/oracle$ps-ef|grepp0

oracle1304410Oct20?0:04ora_p008_test31

oracle1303810Oct20?0:04ora_p005_test31

oracle1302910Oct20?0:04ora_p003_test31

oracle1302710Oct20?0:04ora_p002_test31

oracle642510Oct18?0:08ora_psp0_test31

oracle1303110Oct20?0:04ora_p004_test31

oracle1302510Oct20?0:04ora_p001_test31

oracle1304010Oct20?0:04ora_p006_test31

oracle1302310Oct20?0:04ora_p000_test31

oracle1304610Oct20?0:04ora_p009_test31

oracle1304210Oct20?0:04ora_p007_test31

为了进一步确认并行查询是否发生，找到一张表的记录数为3亿的大表进行了查询，

select/*+full(a)parallel(a50)*/fromxxxxa;这个查询执行了10分钟，在SQL执行期间查询视图V$PX_SESSION：

SQL>select*fromv$px_session;

未选定行

从查询结果来看发现无并行进程，从而可以确认并行查询并未发生。这种情况下，下一步该怎么办呢？只好通过_px_trace参数来进行跟踪了。跟踪并行查询可以使用隐含参数_px_trace。关于如何使用_px_trace来分析并行查询可以参考metalink的文档HowtoUse_PX_TRACEtoCheckWhetherParallelismisUsed[ID400886.1]。

首先在会话中设置_px_trace参数：

SQL>altersessionset"_px_trace"="compilation","execution","messaging"
2/

会话已更改。

这个参数的设置含义是在SQL编译、执行、并行执行消息传递活动发生时进行跟踪。设置好参数后，执行查询操作：

SQL>selectcount(*)fromxuji_test;

COUNT(*)
61059

SQL执行结束后，在udump下找到这个TRACE文件，其内容如下：

***ACTIONNAME:()2011-10-2020:43:29.336
***MODULENAME:(sqlplus@test31(TNSV1-V3))2011-10-2020:43:29.336
***SERVICENAME:(SYS$USERS)2011-10-2020:43:29.336
***SESSIONID:(2720.703)2011-10-2020:43:29.336
kkfdapdml
pgadep:0pdmlmode:0PQallowedDMLallowednotautonomous=>notallowed
kxfplist
Gettinginstanceinfoforopengroup
kxfralo
serial-instancegrouphasnoopenmembers
~
~

上面这段TRACE中的第一句pgadep:0是每个TRACE都会有的，不需要注意。下面的kxfplist和kxfralo这两句十分重要。意思是查找实例的parallelgroup,判断本会话是否属于开放的parallelgroup，如果会话的parallel_instance_group设置的PARALLELGROUP在某个实例上没有设置，那么就不能使用并行查询。从kxfralo的结果上我们看到了一个问题，最终选择的执行方式是serial，而选择serial的原因是instancegrouphasnoopenmembers，也就是说我们的parallel_instance_group参数不属于instance_groups中指定的GROUP。难道parallel_instance_group参数设置的有问题吗？这个时候我们再来看看这两个参数：

SQL>showparameterinstance_group

NAMETYPEVALUE
-----------------------------------------------------------------------------
instance_groupsstringtest3,test31
parallel_instance_groupstringtest31

从参数上看好像也没什么问题，parallel_instance_group设置为test31,而实例的组设置为test3和test31两个。从TRACE的结果可以定位并行查询无法执行的原因肯定是parallel_instance_group参数设置的问题，而从参数上看，有没有任何问题，难道碰到了灵异事件吗？多年的工作经验使我坚信Oracle不可能存在灵异事件，可能是我忽略了什么。于是我采取了一个十分有效也十分简单的方法来验证参数设置是否存在问题。就是找一个没有问题的数据库，查看一下参数设置：

SQL>showparameterinstance_group

NAMETYPEVALUE
-----------------------------------------------------------------------------
instance_groupsstringtest1,test11
parallel_instance_groupstringtest11

从表面上看，好像也没有什么不同。不过如果细心一点，还是能够发现问题的。经过5分钟的反复比对，我终于发现了，正确的那个instance_groups的两个组之间又一个空格，这可能就是问题所在。下一步我们可以验证一下这个空格是否和参数设置的不同有关。在这两个系统上，我们分别生成一个pfile，来查看参数：

createpfile='/tmp/init.ora'fromspfile

有问题的系统的参数设置为：

instance_groups='test3,test31'

正确的系统的参数设置为：

instance_groups='test1','test11'

在参数文件中，我们看到的结果更清晰一些了。一个是用单引号引起了两个组，一个是每个组用单引号引起来，用逗号分割。从上面的结果我们可以猜测，第一个配置的错误之处在于实际上这种设置方法把INSTANCE_GROUPS设置为一个叫做test3,test31的组，因为逗号是组成组名的合法字符。我们可以通过下面的例子来验证这个猜测：

SQL>altersessionset"_px_trace"="compilation","execution","messaging";

会话已更改。

SQL>altersessionsetparallel_instance_group='test3,test31';

会话已更改。

SQL>selectcount(*)fromxuji_test;

COUNT(*)
----------
61059

我们通过将会话的parallel_instance_group设置为test3,test31,使之符合并行查询的条件。令人兴奋的是，我们发现TRACE文件发生了改变：

***ACTIONNAME:()2011-10-2020:53:48.616
***MODULENAME:(sqlplus@test31(TNSV1-V3))2011-10-2020:53:48.616
***SERVICENAME:(SYS$USERS)2011-10-2020:53:48.616
***SESSIONID:(4121.314)2011-10-2020:53:48.616
kkfdapdml
pgadep:0pdmlmode:0PQallowedDMLallowednotautonomous=>notallowe
d
kxfplist
Gettinginstanceinfoforopengroup
kxfrSysInfo
DOPtrace--computedefaultDOPfromsysteminfo
#instancealive=1(kxfrsnins)
kxfrDefaultDOP
DOPTrace--computedefaultDOP
#CPU=64
Threads/CPU=2("parallel_threads_per_cpu")
defaultDOP=128(#CPU*Threads/CPU)
defaultDOP=128(DOP*#instance)
kxfrSysInfo
systemdefaultDOP=128(fromkxfrDefaultDOP())
kxfralo
DOPtrace--requestedthreadfrombestrefobj=20(fromkxfrIsBestRef
())
kxfralo
threadsrequested=20(fromkxfrComputeThread())
kxfralo
adjustedno.threads=20(fromkxfrAdjustDOP())
kxfralo
abouttoallocate20slaves
kxfrAllocSlaves
DOPtrace--callkxfpgsgtoget20slaves
kxfpgsg
numserverrequested=20
kxfplist
Gettinginstanceinfoforopengroup
kxfpiinfo
inst[cpus:mxslv]
1[64:1000]
kxfpclinfo
inst(load:user:pct:fact)aff
1(3:0:100:2133)
kxfpAdaptDOP
Requested=20Granted=20Target=512Load=3Default=128users=0sets=1
kxfpgsg

getting1setsof20threads,clientparallelqueryexecutionflg=0x30
Height=20,AffinityListSize=0,inst_total=1,coord=1
Insts1
Threads20
kxfpg1srv
tryingtogetslaveP000oninstance1
kxfpg1sg
GotIt.1sofar.
kxfpg1srv
tryingtogetslaveP001oninstance1
kxfpg1sg
GotIt.2sofar.
kxfpg1srv
tryingtogetslaveP002oninstance1
kxfpg1sg
GotIt.3sofar.
kxfpg1srv
tryingtogetslaveP003oninstance1
kxfpg1sg
GotIt.4sofar.
kxfpg1srv
tryingtogetslaveP004oninstance1
kxfpg1sg
GotIt.5sofar.
kxfpg1srv
tryingtogetslaveP005oninstance1
kxfpg1sg
GotIt.6sofar.
kxfpg1srv
tryingtogetslaveP006oninstance1
kxfpg1sg
GotIt.7sofar.
kxfpg1srv
tryingtogetslaveP007oninstance1
kxfpg1sg
GotIt.8sofar.
kxfpg1srv
tryingtogetslaveP008oninstance1
kxfpg1sg
GotIt.9sofar.
kxfpg1srv
tryingtogetslaveP009oninstance1

...

...

...

看样子我们的猜测是正确的，问题得到了解决。由于修改INSTANCE_GROUPS参数要重启实例，我们可以通过会话级修改parallel_instance_group来规避这个问题。等到可以重启实例时彻底解决这个问题。

后来我在METALINK上找到了一个相关的文档，正好是讲述这个问题的。这个文档是AfterchangingtheinitparameterINSTANCE_GROUPS,queriesarenolongerbeingexecutedinparallel.[ID750645.1]。这篇文档中正好指出了INSTANCE_GROUPS设置错误将会导致并行执行无法正常工作。

ChangedtheinitializationparametersettingsfortheparametersINSTANCE_GROUPSandPARALLEL_INSTANCE_GROUP.Nowtheparametersareasfollows:

*.instance_groups='MYRAC,MYRAC1,MYRAC2,MYRAC3'

MYRAC1.parallel_instance_group='MYRAC1'

MYRAC2.parallel_instance_group='MYRAC2'

MYRAC3.parallel_instance_group='MYRAC3'

Afterrestartingtheinstances,parallelexecutionisdisabledonallinstances.Parallelqueryprocessesdonotgetspawnedevenwhentheexecutionplanshowsparallel.

并指出要解决这个问题，INSTANCE_GROUPS参数需要进行下面的调整：

1.changethevalueofinstance_groupsinthepfileorspfile

egforspfile:

altersystemsetinstance_groups='MYRAC','MYRAC1','MYRAC2','MYRAC3'SCOPE=SPFILESID='*';

2.restarteachinstanceoneatatime(toavoiddowntime)

Youshouldnowbeabletoexecutequeriesinparallelagain.

这个案例，大家可能看起来觉得十分简单，没有什么技术含量。不过如果你碰到这样的案例，可能你就会觉得这是一个灵异事件。碰到这样的问题，首先我们要明确，任何不正常的事件肯定存在其错误的地方，只是有些错误十分隐秘，我们不太容易察觉而已。碰到这样的问题，不要轻易相信遇到了鬼，而是要采取主动的手段去进一步分析。做TRACE是最佳的分析方法，排除法是最不靠谱的方法。