Oracle SQL語句優化之UNION

問題描述：印尼客戶發現，Peoplesoft Finance 系統中一個頁面存在效能問題。點選頁面的一個連結，處於卡住狀態。要等十幾分鍾才可進入連結頁面。初步懷疑，SQL語句效能不好導致。

處理步驟：
1）開啟peopelosft PIA SQL trace，鎖定哪條SQL語句。

在登入Peoplesoft Finance時，url新增引數trace=y。



使用FTP到伺服器上，獲取trace檔案。分析消耗時間列，發現一個view存在效能問題。
VIEW：PS_PV_PERF_SR_VW。該view是7層select巢狀。


2）檢視是否在基表存在索引。
    存在。

3）檢視執行計劃，新增新索引後，沒有作用。

4）使用oracle sql tuning advisor (STA)，發現語句使用了union，導致出現效能問題。


PS：當SQL語句需要UNION兩個查詢結果集合時，這兩個結果集合會以Oracle UNION ALL的方式被合併，然後在輸出最終結果前進行排序。假如用 Oracle UNION ALL替代UNION，這樣排序就不是必要了。效率就會因此得到提高。需要注意的是，Oracle UNION ALL將重複輸出兩個結果集合中相同記錄。因此各位還是要從業務需求分析使用Oracle UNION ALL的可行性。 UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊記憶體。對於這塊記憶體的優化也是相當重要的。（摘自51CTO）

5）從業務角度考慮，不能儲存重複資料，所以不能使用UNION ALL 代替 UNION。
仔細研究SQL語句。最終重寫。使用左連線代替UNION。之後，執行時間大幅下降。

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ A.SETID

 ,A.INV_ITEM_ID

 ,A.VENDOR_SETID

 ,A.VENDOR_ID

 ,A.CATEGORY_ID

 ,A.UNIT_MEASURE_STD

 ,G.UNIT_OF_MEASURE

 ,A.DESCR

 ,A.DESCR254_MIXED

 ,A.ITM_ID_VNDR

 ,A.TREE_NAME

 ,A.VNDR_CATALOG_ID

 ,A.PRICE_LIST

 ,A.ITM_VNDR_PRIORITY

 ,A.CURRENCY_CD_BASE

 ,A.VNDR_NAME1

 ,G.EFFDT

 ,G.VNDR_LOC

 ,G.PRICE_VNDR

 ,G.CURRENCY_CD

 ,A.PV_MIN_PRIORITY

 ,A.INVENTORY_ITEM

  FROM PS_PV_PERF_V1_VW A

  , PS_ITM_VNDR_UOM_PR G

 WHERE A.INV_ITEM_ID= G.INV_ITEM_ID

   AND A.SETID = G.SETID

   AND A.VENDOR_SETID = G.VENDOR_SETID

   AND A.VENDOR_ID = G.VENDOR_ID

   AND A.VNDR_LOC = G.VNDR_LOC

   AND G.EFFDT = (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ MAX(EFFDT)

  FROM PS_ITM_VNDR_UOM_PR

 WHERE INV_ITEM_ID = G.INV_ITEM_ID

   AND SETID = G.SETID

   AND VENDOR_ID = G.VENDOR_ID

   AND VENDOR_SETID = G.VENDOR_SETID

   AND VNDR_LOC = G.VNDR_LOC

   AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE

   AND CURRENCY_CD = G.CURRENCY_CD

   AND QTY_MIN = G.QTY_MIN

   AND EFF_STATUS = 'A'

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )

   AND G.UNIT_OF_MEASURE = (

 SELECT /* INDEX PS_ITM_VNDR_UOM (PSAITM_VNDR_UOM), ALL_ROWS */ U.UNIT_OF_MEASURE

  FROM PS_ITM_VNDR_UOM U

 WHERE G.INV_ITEM_ID = U.INV_ITEM_ID

   AND G.VENDOR_ID = U.VENDOR_ID

   AND G.VNDR_LOC = U.VNDR_LOC

   AND U.DFLT_UOM = 'Y'

   AND G.VENDOR_SETID = U.VENDOR_SETID

   AND G.SETID = U.SETID )

   AND G.CURRENCY_CD = (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ MAX(W.CURRENCY_CD)

  FROM PS_ITM_VNDR_UOM_PR W

 WHERE G.VENDOR_SETID = W.VENDOR_SETID

   AND G.INV_ITEM_ID = W.INV_ITEM_ID

   AND G.SETID = W.SETID

   AND G.VENDOR_ID = W.VENDOR_ID

   AND G.VNDR_LOC = W.VNDR_LOC

   AND G.UNIT_OF_MEASURE = W.UNIT_OF_MEASURE

   AND G.QTY_MIN = W.QTY_MIN)

   AND G.VNDR_LOC = (

 SELECT VNDR_LOC

  FROM PS_VENDOR_LOC L

 WHERE G.VENDOR_SETID = L.SETID

   AND G.VENDOR_ID = L.VENDOR_ID

   AND G.VNDR_LOC = L.VNDR_LOC

   AND EFFDT =(

 SELECT MAX(EFFDT)

  FROM PS_VENDOR_LOC

 WHERE SETID = L.SETID

   AND VENDOR_ID = L.VENDOR_ID

   AND VNDR_LOC = L.VNDR_LOC

   AND EFF_STATUS = 'A'

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') ))

   AND G.QTY_MIN = (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ MIN(QTY_MIN)

  FROM PS_ITM_VNDR_UOM_PR

 WHERE INV_ITEM_ID = G.INV_ITEM_ID

   AND SETID = G.SETID

   AND VENDOR_ID = G.VENDOR_ID

   AND VENDOR_SETID = G.VENDOR_SETID

   AND VNDR_LOC = G.VNDR_LOC

   AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

  UNION

 SELECT A.SETID

 ,A.INV_ITEM_ID

 ,A.VENDOR_SETID

 ,A.VENDOR_ID

 ,A.CATEGORY_ID

 ,A.UNIT_MEASURE_STD

 ,CAST(NULL AS CHAR(3))

 ,A.DESCR

 ,A.DESCR254_MIXED

 ,A.ITM_ID_VNDR

 ,A.TREE_NAME

 ,A.VNDR_CATALOG_ID

 ,A.PRICE_LIST

 ,A.ITM_VNDR_PRIORITY

 ,A.CURRENCY_CD_BASE

 ,A.VNDR_NAME1

 ,CAST(NULL AS DATE)

 ,CAST(NULL AS CHAR(10))

 ,0

 ,CAST(NULL AS CHAR(3))

 ,A.PV_MIN_PRIORITY

 ,A.INVENTORY_ITEM

  FROM PS_PV_PERF_V1_VW A

 WHERE NOT EXISTS (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ 'X'

  FROM PS_ITM_VNDR_UOM_PR G

 WHERE A.INV_ITEM_ID= G.INV_ITEM_ID

   AND A.SETID = G.SETID

   AND A.VENDOR_SETID = G.VENDOR_SETID

   AND A.VENDOR_ID = G.VENDOR_ID

   AND A.VNDR_LOC = G.VNDR_LOC

   AND G.EFFDT = (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ MAX(EFFDT)

  FROM PS_ITM_VNDR_UOM_PR

 WHERE INV_ITEM_ID = G.INV_ITEM_ID

   AND SETID = G.SETID

   AND VENDOR_ID = G.VENDOR_ID

   AND VENDOR_SETID = G.VENDOR_SETID

   AND VNDR_LOC = G.VNDR_LOC

   AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE

   AND CURRENCY_CD = G.CURRENCY_CD

   AND QTY_MIN = G.QTY_MIN

   AND EFF_STATUS = 'A'

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )

   AND G.UNIT_OF_MEASURE = (

 SELECT /* INDEX PS_ITM_VNDR_UOM (PSAITM_VNDR_UOM), ALL_ROWS */ U.UNIT_OF_MEASURE

  FROM PS_ITM_VNDR_UOM U

 WHERE G.INV_ITEM_ID = U.INV_ITEM_ID

   AND G.VENDOR_ID = U.VENDOR_ID

   AND U.DFLT_UOM = 'Y'

   AND G.VENDOR_SETID = U.VENDOR_SETID

   AND G.SETID = U.SETID)

   AND G.CURRENCY_CD = (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ MAX(W.CURRENCY_CD)

  FROM PS_ITM_VNDR_UOM_PR W

 WHERE G.INV_ITEM_ID = W.INV_ITEM_ID

   AND G.SETID = W.SETID

   AND G.VENDOR_SETID = W.VENDOR_SETID

   AND G.VENDOR_ID = W.VENDOR_ID

   AND G.VNDR_LOC = W.VNDR_LOC

   AND G.UNIT_OF_MEASURE = W.UNIT_OF_MEASURE

   AND G.QTY_MIN = W.QTY_MIN)

   AND G.VNDR_LOC = (

 SELECT VNDR_LOC

  FROM PS_VENDOR_LOC L

 WHERE G.VENDOR_SETID = L.SETID

   AND G.VENDOR_ID = L.VENDOR_ID

   AND G.VNDR_LOC = L.VNDR_LOC

   AND EFFDT =(

 SELECT MAX(EFFDT)

  FROM PS_VENDOR_LOC

 WHERE SETID = L.SETID

   AND VENDOR_ID = L.VENDOR_ID

   AND VNDR_LOC = L.VNDR_LOC

   AND EFF_STATUS = 'A'

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') ))

   AND G.QTY_MIN = (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ MIN(QTY_MIN)

  FROM PS_ITM_VNDR_UOM_PR

 WHERE INV_ITEM_ID = G.INV_ITEM_ID

   AND SETID = G.SETID

   AND VENDOR_ID = G.VENDOR_ID

   AND VENDOR_SETID = G.VENDOR_SETID

   AND VNDR_LOC = G.VNDR_LOC

   AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE

   AND CURRENCY_CD = G.CURRENCY_CD

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')))

 

 

1.       The SQL of PS_PV_PERF_V2_VW view was replaced by a new SQL statement based on the creation of a new view

 

 

SELECT A.SETID

 ,A.INV_ITEM_ID

 ,A.VENDOR_SETID

 ,A.VENDOR_ID

 ,A.CATEGORY_ID

 ,A.UNIT_MEASURE_STD

 ,G.UNIT_OF_MEASURE

 ,A.DESCR

 ,A.DESCR254_MIXED

 ,A.ITM_ID_VNDR

 ,A.TREE_NAME

 ,A.VNDR_CATALOG_ID

 ,A.PRICE_LIST

 ,A.ITM_VNDR_PRIORITY

 ,A.CURRENCY_CD_BASE

 ,A.VNDR_NAME1

 ,G.EFFDT

 ,G.VNDR_LOC

 ,G.PRICE_VNDR

 ,G.CURRENCY_CD

 ,A.PV_MIN_PRIORITY

 ,A.INVENTORY_ITEM

  FROM PS_PV_PERF_V1_VW A LEFT OUTER JOIN PS_C4_ITM_VNDR_UOM G ON (A.SETID = G.SETID

   AND A.INV_ITEM_ID= G.INV_ITEM_ID

   AND A.VENDOR_SETID = G.VENDOR_SETID

   AND A.VENDOR_ID = G.VENDOR_ID

   AND A.VNDR_LOC = G.VNDR_LOC )

 

 

1.       The record PS_C4_ITM_VNDR_UOM  was created as

SELECT G.SETID

 , G.INV_ITEM_ID

 , G.VENDOR_SETID

 , G.VENDOR_ID

 , G.VNDR_LOC

 , G.UNIT_OF_MEASURE

 , G.CURRENCY_CD

 , G.QTY_MIN

 , G.EFFDT

 , G.EFF_STATUS

 , G.PRICE_VNDR

 , G.UNIT_PRC_TOL

 , G.EXT_PRC_TOL

 , G.USE_STD_TOLERANCES

 , G.PCT_UNIT_PRC_TOL

 , G.PCT_EXT_PRC_TOL

 , G.QTY_RECV_TOL_PCT

 , G.UNIT_PRC_TOL_L

 , G.PCT_UNIT_PRC_TOL_L

 , G.EXT_PRC_TOL_L

 , G.PCT_EXT_PRC_TOL_L

 , G.BU_PRICE_STATUS

 , G.STD_PRICE_STATUS

 , G.LEAD_TIME

 , G.OPRID_MODIFIED_BY

 , G.LAST_DTTM_UPDATE

 , G.PRICE_CHANGE

  FROM PS_ITM_VNDR_UOM_PR G

 WHERE G.EFFDT = (

 SELECT MAX(EFFDT)

  FROM PS_ITM_VNDR_UOM_PR

 WHERE SETID = G.SETID

   AND INV_ITEM_ID = G.INV_ITEM_ID

   AND VENDOR_ID = G.VENDOR_ID

   AND VENDOR_SETID = G.VENDOR_SETID

   AND VNDR_LOC = G.VNDR_LOC

   AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE

   AND CURRENCY_CD = G.CURRENCY_CD

   AND QTY_MIN = G.QTY_MIN

   AND EFF_STATUS = 'A'

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') )

   AND G.UNIT_OF_MEASURE = (

 SELECT U.UNIT_OF_MEASURE

  FROM PS_ITM_VNDR_UOM U

 WHERE G.VENDOR_SETID = U.VENDOR_SETID

   AND G.VENDOR_ID = U.VENDOR_ID

   AND G.SETID = U.SETID

   AND G.INV_ITEM_ID = U.INV_ITEM_ID

   AND G.VNDR_LOC = U.VNDR_LOC

   AND U.DFLT_UOM = 'Y')

   AND G.CURRENCY_CD = (

 SELECT MAX(W.CURRENCY_CD)

  FROM PS_ITM_VNDR_UOM_PR W

 WHERE G.VENDOR_SETID = W.VENDOR_SETID

   AND G.VENDOR_ID = W.VENDOR_ID

   AND G.SETID = W.SETID

   AND G.INV_ITEM_ID = W.INV_ITEM_ID

   AND G.VNDR_LOC = W.VNDR_LOC

   AND G.UNIT_OF_MEASURE = W.UNIT_OF_MEASURE

   AND G.QTY_MIN = W.QTY_MIN)

   AND G.VNDR_LOC = (

 SELECT VNDR_LOC

  FROM PS_VENDOR_LOC L

 WHERE G.VENDOR_SETID = L.SETID

   AND G.VENDOR_ID = L.VENDOR_ID

   AND G.VNDR_LOC = L.VNDR_LOC

   AND EFFDT =(

 SELECT MAX(EFFDT)

  FROM PS_VENDOR_LOC

 WHERE SETID = L.SETID

   AND VENDOR_ID = L.VENDOR_ID

   AND VNDR_LOC = L.VNDR_LOC

   AND EFF_STATUS = 'A'

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') ))

   AND G.QTY_MIN = (

 SELECT MIN(QTY_MIN)

  FROM PS_ITM_VNDR_UOM_PR

 WHERE SETID = G.SETID

   AND INV_ITEM_ID = G.INV_ITEM_ID

   AND VENDOR_ID = G.VENDOR_ID

   AND VENDOR_SETID = G.VENDOR_SETID

   AND VNDR_LOC = G.VNDR_LOC

   AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE

   AND CURRENCY_CD = G.CURRENCY_CD

   AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

 

    

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27009266/viewspace-1077762/，如需轉載，請註明出處，否則將追究法律責任。