问题描述：印尼客户发现，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'))