Oracle:并行操作為什麼無法執行(老白)

在一次系統割接的時候，我們碰到一個十分奇怪的現象。由于進行系統遷移，是以很多大表在資料導入時沒有建立索引，導入結束後需要重建索引。為了加快索引的建立速度，我們需要并行建索引。雖然在建立索引的腳本中加入了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
kxfpgsggetting 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是最佳的分析方法，排除法是最不靠譜的方法。

Oracle:并行操作為什麼無法執行(老白)

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List