在一次系统割接的时候，我们碰到一个十分奇怪的现象。由于进行系统迁移，因此很多大表在数据导入时没有创建索引，导入结束后需要重建索引。为了加快索引的创建速度，我们需要并行建索引。虽然在创建索引的脚本中加入了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
如有错误，欢迎指正
邮箱：czmcj@163.com
相关推荐
2.将ojdbc6.jar包添加到maven，也就是运行下面的语句，注意：不是在C盘下运行，是在该目录下执行下面的语句，如果你不知道你的版本号，可以执行select * from v$version;进行查看 mvn install:install-file -...
Oracle并行执行
深入浅出Oracle： DBA入门、进阶与诊断案例.pdf 深入浅出Oracle： DBA入门、进阶与诊断案例.pdf
《涂抹Oracle：三思笔记之一步一步学Oracle》语言生动幽默，案例丰富，实践性强，在章节编排和内容组织方面，主要面向初次接触Oracle数据库的技术人员，以及对数据库操作感兴趣的开发人员，对于具有一定技术实力的...
并行处理就是利用多个CPU和I/O资源来执行单个数据库操作。尽管现在每个主要的数据库供应商都声称可以提供并行处理能力，但每个供应商所提供的体系结构其实存在关键的差异。本文讨论Oracle9i并行处理的体系结构，并...
Oracle的并行查询是使用多个操作系统级别的Server Process来同时完成一个SQL查询，本文讲解Oracle数据库并行查询出错的解决方法如下： 1、错误描述 ORA-12801: 并行查询服务器P007中发出错误信号 ORA-01722:无效...
深入浅出Oracle：DBA入门、进阶与诊断案例深入浅出Oracle：DBA入门、进阶与诊断案例深入浅出Oracle：DBA入门、进阶与诊断案例深入浅出Oracle：DBA入门、进阶与诊断案例深入浅出Oracle：DBA入门、进阶与诊断案例
ORACLE并行SQL指南,讲述了oracle中通过编写并行sql来提高数据库的执行效率和应用的响应速度
白皮书：Oracle：企业大数据解决方案(PDF)
该资源为oracle的数据库驱动包，版本：ojdbc14-10.2.0.3.0.jar，有需要可执行下载~
oracle中的并行处理，还是值得一看的！
在本文中，在一个简单的星型模型上，我会使用大量例子和sqlmonitor报告，力求以最直观简单的方式，向读者阐述并行执行的核心内容：Oracle版本为12.1.0.2.2，两个节点的RAC，硬件为ExadataX3-‐8。这是一个典型的...
Oracle批处理：使用C# 自带Oracle驱动一次执行多条Sql语句
现实世界中，由于使用不当，并行操作无法并行，或者并行执行计划效率低下，没有获得期望的性能提升。本节举几个典型例子。 在’数据倾斜对不同分发方式的影响’小节中，我们新建一个表lineorder_skew把lineorder的...
oracle10g 并行查询的优化讲解，讲解的比较透彻全面
Oracle数据库的并行操作特性，其本质上就是强行榨取除数据库服务器空闲资源（主要是CPU资源），对一些高负荷大数据量数据进行分治处理。并行操作是一种非确定性的优化策略，在选择的时候需要小心对待。目前，使用...
1 Oracle:索引 5分 2 Oracle:plsql，游标 6分 3 Oracle:sql命令 8分 4 Oracle:数据库创建，授权 6分 5 Oracle:plsql块 5分 6 Oracle:过程与函数 6分 7 Oracle:plsql块 6分 8 Oracle:plsql，过程与函数 8分 9 Oracle:...
Oracle DBA认证方面的资料 oracle DBA 1z0-001 oracle7.3的4本： M03989 Instroduction to Oracle: SQL and PL/SQL Participant Guide Vol. 1 M03990 Instroduction to Oracle: SQL and PL/SQL Participant ...
Missing artifact com.oracle:ojdbc14:jar:10.2.0.3.0-附件资源