Oracle:并行操作为什么无法执行(老白)

转载：http://czmmiao.iteye.com/blog/1892331

在一次系统割接的时候，我们碰到一个十分奇怪的现象。由于进行系统迁移，因此很多大表在数据导入时没有创建索引，导入结束后需要重建索引。为了加快索引的创建速度，我们需要并行建索引。虽然在创建索引的脚本中加入了PARALLEL 40，但是实际上，创建索引的操作还是串行的。
这时一套拥有64个核的系统，并行创建索引可以成倍的提高速度。而无法是用并行会严重影响割接前的准备工作。因此这个原因需要尽快查清。碰到这样的问题，首先我们要做的事情就是先检查一下并行的相关参数设置：
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      XXXX31
parallel_max_servers                 integer     1000
parallel_min_percent                 integer     0
parallel_min_servers                 integer     10
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
可以看到，PARALLEL相关的参数设置并无问题，PARALLEL_MAX_SERVERS是1000,PARALLEL_MIN_SERVERS是10。通过ps命令：
oracle@test31:/oracle$ ps -ef|grep p0
  oracle 13044     1  0  Oct 20  ?         0:04 ora_p008_test31
  oracle 13038     1  0  Oct 20  ?         0:04 ora_p005_test31
  oracle 13029     1  0  Oct 20  ?         0:04 ora_p003_test31
  oracle 13027     1  0  Oct 20  ?         0:04 ora_p002_test31
  oracle  6425     1  0  Oct 18  ?         0:08 ora_psp0_test31
  oracle 13031     1  0  Oct 20  ?         0:04 ora_p004_test31
  oracle 13025     1  0  Oct 20  ?         0:04 ora_p001_test31
  oracle 13040     1  0  Oct 20  ?         0:04 ora_p006_test31
  oracle 13023     1  0  Oct 20  ?         0:04 ora_p000_test31
  oracle 13046     1  0  Oct 20  ?         0:04 ora_p009_test31
  oracle 13042     1  0  Oct 20  ?         0:04 ora_p007_test31
可以看出目前只启动了10个并行进程，也就是PARALLEL_MIN_SERVERS指定的数量。从ps的结果可以看出并行进程的启动是正常的。在 ALERT LOG中也没有看到相关的错误活着警告信息。看样子这个问题有点奇怪了，为了尽快定位问题，我们首先要创建一个测试环境：
create table xuji_test tablespace sysaux as select * from dba_objects ;
alter table xuji_test parallel 20;
select count(*) from xuji_test;
我们首先通过DBA_OBJECTS创建了一张有6万多条记录的表xuji_test，然后将这张表的并行度设置为20，然后做一个COUNT(*)操作，SQL执行后，从V$SQLAREA找到这条SQL的SQL_ID，然后查看执行计划：
SQL> select * from table(dbms_xplan.display_cursor('8sj2h9nsq7s4h',null,'ADVANCED'));
select count(*) from xuji_test
Plan hash value: 3609358487
-------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  |  TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |       |      |      |            |
|   1 |  SORT AGGREGATE        |           |     1 |      |      |            |
|   2 |   PX COORDINATOR       |           |       |      |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |     1 |Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |           |     1 |Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |           | 61059 |Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| XUJI_TEST | 61059 |Q1,00 | PCWP |            |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
   6 - SEL$1 / XUJI_TEST@SEL$1
Outline Data
-------------
  /*+
      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
  */
Predicate Information (identified by operation id):
--------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
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|grep p0
  oracle 13044     1  0  Oct 20  ?         0:04 ora_p008_test31
  oracle 13038     1  0  Oct 20  ?         0:04 ora_p005_test31
  oracle 13029     1  0  Oct 20  ?         0:04 ora_p003_test31
  oracle 13027     1  0  Oct 20  ?         0:04 ora_p002_test31
  oracle  6425     1  0  Oct 18  ?         0:08 ora_psp0_test31
  oracle 13031     1  0  Oct 20  ?         0:04 ora_p004_test31
  oracle 13025     1  0  Oct 20  ?         0:04 ora_p001_test31
  oracle 13040     1  0  Oct 20  ?         0:04 ora_p006_test31
  oracle 13023     1  0  Oct 20  ?         0:04 ora_p000_test31
  oracle 13046     1  0  Oct 20  ?         0:04 ora_p009_test31
  oracle 13042     1  0  Oct 20  ?         0:04 ora_p007_test31
为了进一步确认并行查询是否发生，找到一张表的记录数为3亿的大表进行了查询，
select /*+ full(a) parallel( a 50) */ from xxxx a; 这个查询执行了10分钟，在SQL执行期间查询视图V$PX_SESSION：
SQL> select * from v$px_session;

未选定行
从查询结果来看发现无并行进程，从而可以确认并行查询并未发生。这种情况下，下一步该怎么办呢？只好通过_px_trace参数来进行跟踪了。跟踪并行查询可以使用隐含参数_px_trace。关于如何使用_px_trace来分析并行查询可以参考metalink的文档How to Use _PX_TRACE to Check Whether Parallelism is Used [ID 400886.1]。
首先在会话中设置_px_trace参数：
SQL> alter session set "_px_trace"="compilation","execution","messaging"
2 /
会话已更改。
这个参数的设置含义是在SQL编译、执行、并行执行消息传递活动发生时进行跟踪。设置好参数后，执行查询操作：
SQL> select count(*) from xuji_test;
COUNT(*)
61059
SQL执行结束后，在udump下找到这个TRACE文件，其内容如下：
*** ACTION NAME:() 2011-10-20 20:43:29.336
*** MODULE NAME:(sqlplus@test31 (TNS V1-V3)) 2011-10-20 20:43:29.336
*** SERVICE NAME:(SYS$USERS) 2011-10-20 20:43:29.336
*** SESSION ID:(2720.703) 2011-10-20 20:43:29.336
kkfdapdml
pgadep:0 pdml mode:0 PQ allowed DML allowed not autonomous => not allowed
kxfplist
Getting instance info for open group
kxfralo
serial - instance group has no open members
~
~
上面这段TRACE中的第一句pgadep:0是每个TRACE都会有的，不需要注意。下面的kxfplist和kxfralo这两句十分重要。意思是查找 实例的parallel group,判断本会话是否属于开放的parallel group，如果会话的 parallel_instance_group设置的PARALLEL GROUP在某个实例上没有设置，那么就不能使用并行查询。从kxfralo的结果上我们看到了一个问题，最终选择的执行方式是serial，而选择serial的原因是instance group has no open members，也就是说我们的parallel_instance_group参数不属于instance_groups中指定的GROUP。难道parallel_instance_group参数设置的有问题吗？这个时候我们再来看看这两个参数：
SQL> show parameter instance_group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string test3,test31
parallel_instance_group string test31
从参数上看好像也没什么问题，parallel_instance_group 设置为test31,而实例的组设置为test3和test31两个。从TRACE的结果可以定位并行查询无法执行的原因肯定是 parallel_instance_group参数设置的问题，而从参数上看，有没有任何问题，难道碰到了灵异事件吗？多年的工作经验使我坚信 Oracle 不可能存在灵异事件，可能是我忽略了什么。于是我采取了一个十分有效也十分简单的方法来验证参数设置是否存在问题。就是找一个没有问题的数 据库，查看一下参数设置：
SQL> show parameter instance_group
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_groups string test1, test11
parallel_instance_group string test11
从表面上看，好像也没有什么不同。不过如果细心一点，还是能够发现问题的。经过5分钟的反复比对，我终于发现了，正确的那个instance_groups的两个组之间又一个空格，这可能就是问题所在。下一步我们可以验证一下这个空格是否和参数设置的不同有关。在这两个系统上，我们分别生成一个pfile，来查看参数：
create pfile='/tmp/init.ora' from spfile
有问题的系统的参数设置为：
instance_groups='test3,test31'
正确的系统的参数设置为：
instance_groups='test1','test11'
在参数文件中，我们看到的结果更清晰一些了。一个是用单引号引起了两个组，一个是每个组用单引号引起来，用逗号分割。从上面的结果我们可以猜测，第一个配置的错误之处在于实际上这种设置方法把INSTANCE_GROUPS设置为一个叫做test3,test31的组，因为逗号是组成组名的合法字符。我们可以通过下面的例子来验证这个猜测：
SQL> alter session set "_px_trace"="compilation","execution","messaging";
会话已更改。
SQL> alter session set parallel_instance_group='test3,test31';
会话已更改。
SQL> select count(*) from xuji_test;
COUNT(*)
----------
61059
我们通过将会话的parallel_instance_group设置为test3,test31,使之符合并行查询的条件。令人兴奋的是，我们发现TRACE文件发生了改变：
*** ACTION NAME:() 2011-10-20 20:53:48.616
*** MODULE NAME:(sqlplus@test31 (TNS V1-V3)) 2011-10-20 20:53:48.616
*** SERVICE NAME:(SYS$USERS) 2011-10-20 20:53:48.616
*** SESSION ID:(4121.314) 2011-10-20 20:53:48.616
kkfdapdml
pgadep:0 pdml mode:0 PQ allowed DML allowed not autonomous => not allowe
d
kxfplist
Getting instance info for open group
kxfrSysInfo
DOP trace -- compute default DOP from system info
# instance alive = 1 (kxfrsnins)
kxfrDefaultDOP
DOP Trace -- compute default DOP
# CPU = 64
Threads/CPU = 2 ("parallel_threads_per_cpu")
default DOP = 128 (# CPU * Threads/CPU)
default DOP = 128 (DOP * # instance)
kxfrSysInfo
system default DOP = 128 (from kxfrDefaultDOP())
kxfralo
DOP trace -- requested thread from best ref obj = 20 (from kxfrIsBestRef
())
kxfralo
threads requested = 20 (from kxfrComputeThread())
kxfralo
adjusted no. threads = 20 (from kxfrAdjustDOP())
kxfralo
about to allocate 20 slaves
kxfrAllocSlaves
DOP trace -- call kxfpgsg to get 20 slaves
kxfpgsg
num server requested = 20
kxfplist
Getting instance info for open group
kxfpiinfo
inst[cpus:mxslv]
1[64:1000]
kxfpclinfo
inst(load:user:pct:fact)aff
1(3:0:100:2133)
kxfpAdaptDOP
Requested=20 Granted=20 Target=512 Load=3 Default=128 users=0 sets=1
kxfpgsg
getting 1 sets of 20 threads, client parallel query execution flg=0x30
Height=20, Affinity List Size=0, inst_total=1, coord=1
Insts 1
Threads 20
kxfpg1srv
trying to get slave P000 on instance 1
kxfpg1sg
Got It. 1 so far.
kxfpg1srv
trying to get slave P001 on instance 1
kxfpg1sg
Got It. 2 so far.
kxfpg1srv
trying to get slave P002 on instance 1
kxfpg1sg
Got It. 3 so far.
kxfpg1srv
trying to get slave P003 on instance 1
kxfpg1sg
Got It. 4 so far.
kxfpg1srv
trying to get slave P004 on instance 1
kxfpg1sg
Got It. 5 so far.
kxfpg1srv
trying to get slave P005 on instance 1
kxfpg1sg
Got It. 6 so far.
kxfpg1srv
trying to get slave P006 on instance 1
kxfpg1sg
Got It. 7 so far.
kxfpg1srv
trying to get slave P007 on instance 1
kxfpg1sg
Got It. 8 so far.
kxfpg1srv
trying to get slave P008 on instance 1
kxfpg1sg
Got It. 9 so far.
kxfpg1srv
trying to get slave P009 on instance 1
...
...
...
看样子我们的猜测是正确的，问题得到了解决。由于修改INSTANCE_GROUPS参数要重启实例，我们可以通过会话级修改parallel_instance_group来规避这个问题。等到可以重启实例时彻底解决这个问题。
后来我在METALINK上找到了一个相关的文档，正好是讲述这个问题的。这个文档是 After changing the init parameter INSTANCE_GROUPS, queries are no longer being executed in parallel. [ID 750645.1]。 这篇文档中正好指出了INSTANCE_GROUPS设置错误将会导致并行执行无法正常工作。
Changed the initialization parameter settings for the parameters INSTANCE_GROUPS and
PARALLEL_INSTANCE_GROUP.  Now the parameters are as follows:

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

After restarting the instances, parallel execution is disabled on all instances.  Parallel
query processes do not get spawned even when the execution plan shows parallel.
并指出要解决这个问题，INSTANCE_GROUPS参数需要进行下面的调整：
1. change the value of instance_groups in the pfile or spfile
eg for spfile:
alter system set instance_groups='MYRAC','MYRAC1','MYRAC2','MYRAC3' SCOPE=SPFILE SID='*' ;

2. restart each instance one at a time (to avoid downtime)

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

参考至：http://www.oraclefans.cn/forum/showtopic.jsp?rootid=38027&CPages=1