近日將一個10.2.0.4 (LInux X86-64)的庫升級到11.2.0.3 (Linux X86-64)後發生一點小問題

前期請開發測試11G測試庫都很正常，決定正式升級

昨天早上開發打電話來，User反映導PPID很慢，程式會僵死，登上GridControl一看，發現有鎖表現象。

程式首先執行Update，後執行Insert，Insert被Update Block了

/* Formatted on 2011/12/20 上午 10:57:09 (QP5 v5.163.1008.3004) */
UPDATE pm_vendorshippingdetail1
SET receivingno = '2RN-1C0002',
   receivingdate = SYSDATE,
   receivingflag = 'Y'
WHERE serialno IN
      ( SELECT serialno
               FROM pm_vendorshippingdetail1
         START WITH parentserialno IN (SELECT serialno
                                       FROM im_movelist
                                       WHERE docno = '2RN-1C0002')
         CONNECT BY PRIOR serialno = parentserialno
         UNION
            SELECT serialno
               FROM pm_vendorshippingdetail1
         START WITH serialno IN (SELECT serialno
                                 FROM im_movelist
                                 WHERE docno = '2RN-1C0002')
         CONNECT BY PRIOR parentserialno = serialno)
   AND receivingflag = 'N';

/* Formatted on 2011/12/21 上午 09:43:12 (QP5 v5.163.1008.3004) */
INSERT INTO im_inventorydetail (serialno,
                              pn,
                              revision,
                              plantno,
                              whseno,
                              locationno,
                              lotno,
                              binno,
                              packingtype,
                              unitcode,
                              qty,
                              invunitcode,
                              receivingno,
                              receivingitemno,
                              vendorcode,
                              scanindate,
                              statuscode)
SELECT serialno,
      pn,
      revision,
      plantno,
      whseno,
      locationno,
      lotno,
      binno,
      packingtype,
      unitcode,
      qty,
      invunitcode,
      receivingno,
      receivingitemno,
      vendorcode,
      scanindate,
      statuscode
   FROM im_movelist
WHERE docno = '2RN-1C0002' AND docitemno = '1.00';

GridControl中看Update執行時間超過10分鐘仍然沒完，把Update SQL拷貝到Toad中繼續研究

Update的pm_vendorshippingdetail1表上serialno有索引，單純Update速度應該很快，外層的問

題可以排除掉。再看裡面的嵌套SQL，這個嵌套SQL有一個UNION連接前後兩段

SELECT serialno
               FROM pm_vendorshippingdetail1
         START WITH parentserialno IN (SELECT serialno
                                       FROM im_movelist
                                       WHERE docno = '2RN-1C0002')
         CONNECT BY PRIOR serialno = parentserialno
         UNION
            SELECT serialno
               FROM pm_vendorshippingdetail1
         START WITH serialno IN (SELECT serialno
                                 FROM im_movelist
                                 WHERE docno = '2RN-1C0002')
         CONNECT BY PRIOR parentserialno = serialno

單獨執行UNION前段速度很快，單獨執行UNION後段速度無法忍受，看來問題就在UNION後段

/* Formatted on 2011/12/21 上午 09:52:05 (QP5 v5.163.1008.3004) */
SELECT serialno
   FROM pm_vendorshippingdetail1
START WITH serialno IN (SELECT serialno
                        FROM im_movelist
                     WHERE docno = '2RN-1C0002')
CONNECT BY PRIOR parentserialno = serialno

這段的執行計劃竟然有個INDEX  FULL SCAN。。。所有的Loading應該都在這裡。

嘗試加上and Rownum<2試試看。這次INDEX FULL SCAN沒有了。。。，速度很快

這次把Rownum <2改成Rownum <3看看，INDEX FULL SCAN又來了，速度難以接受

看來就是因為這個地方的SQL執行計劃在升級到11.2.0.3後發生突變，導致Update速度慢，進而產生鎖表

把CBO特性降低到10.2.0.4再試試看

alter system set optimizer_features_enable='10.2.0.4';

這次無論怎麼調，那個INDEX FULL SCAN再沒出現了。再讓開發找User導數據，這次一切正常，問題徹底解決

又嘗試對pm_vendorshippingdetail1表的serialno與parentserialno兩個字段加上組合索引，並在Session級別將

CBO特性恢復到11.2.0.3。這次必須要加上Hint，那個問題SQL才能使用新組合索引。

開發不太想改程式，暫時放棄這個備選方案，這個問題被Close掉。

10.2.0.4升級11.2.0.3問題處理