This discussion is archived
5 Replies Latest reply: Mar 20, 2013 9:47 PM by mmaller0319 RSS

8.9 to 9.1 FP2 FSCM.Data Conversion Step

mmaller0319 Newbie
Currently Being Moderated
One of my Data Conversion App Engines seem to take a lot of time on the query

UPDATE PS_EOAW_WL
SET EOAWDEFN_ID =
(
SELECT A.EOAWDEFN_ID
FROM PS_EX_SHEET_AW A
WHERE A.EOAWPRCS_ID = 'ERApproval'
AND PS_EOAW_WL.EOAWTHREAD_ID = A.EOAWTHREAD_ID
)
WHERE PS_EOAW_WL.EOAWPRCS_ID = 'ERApproval'
AND EXISTS
(
SELECT 'X'
FROM PS_EX_SHEET_AW A
WHERE A.EOAWPRCS_ID = 'ERApproval'
AND PS_EOAW_WL.EOAWTHREAD_ID = A.EOAWTHREAD_ID
)


This is a part of UPG_SAC.SACS001.Step12.


It was going via the Execution Plan

UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=3453)
UPDATE OF PS_EOAW_WL
HASH JOIN (SEMI) (Cost=465 Cardinality=1 Bytes=141)
INDEX (RANGE SCAN) OF PSCEOAW_WL (INDEX) (Cost=0 Cardinality=1 Bytes=137)
INDEX (FAST FULL SCAN) OF PSCEX_SHEET_AW (INDEX) (Cost=465 Cardinality=1 Bytes=4)
INDEX (FULL SCAN) OF PSCEX_SHEET_AW (INDEX) (Cost=2987 Cardinality=1 Bytes=6)

I thought PSCEX_SHEET_AW is the culprit and i dropped the index and reran the appengine and now its taking this execution plan

UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=349454101)
UPDATE OF PS_EOAW_WL
HASH JOIN (SEMI) (Cost=3954 Cardinality=134249 Bytes=5101462)
TABLE ACCESS (FULL) OF PS_EOAW_WL (TABLE) (Cost=486 Cardinality=134250 Bytes=2685000)
TABLE ACCESS (FULL) OF PS_EX_SHEET_AW (TABLE) (Cost=2602 Cardinality=1179379 Bytes=21228822)
TABLE ACCESS (FULL) OF PS_EX_SHEET_AW (TABLE) (Cost=2602 Cardinality=1 Bytes=24)

Its pretty clear dropping the index is not a good idea But i am allowing it to run this time as its already 5 hrs into the query and it should be done in next 3 hrs.

Kindly suggest some ideas on the same as this would be crucial in my next moves.

Edited by: mmaller0319 on Jan 29, 2013 8:03 AM
  • 1. Re: 8.9 to 9.1 FP2 FSCM.Data Conversion Step
    HakanBiroglu Oracle ACE
    Currently Being Moderated
    Your issue is addressed in the following doc
    EEX9.1:Performance Issue Upgrade from 8.9 to 9.1 - When Performing Data Conversion UPG_SAC.SACS000 Appengine Runs For 16 Hours. [ID 1485582.1]     

    Wasn't this patch incliuded in the Required for Upgrade patches, just wondering?

    Answering my own question: yes it is.
    When you search for patches on MOS using the following search criteria:
    Product: PeopleSoft FIN Install
    Release: PeopleSoft 9.1
    and Install/Upgrade (PeopleSoft) is Required at Upgrade you will find the patch mentioned in MOS doc 1485582.1.

    Hope it helps.

    Hakan

    Edited by: Hakan Biroglu on Jan 29, 2013 7:52 PM
  • 2. Re: 8.9 to 9.1 FP2 FSCM.Data Conversion Step
    mmaller0319 Newbie
    Currently Being Moderated
    This Update is included in required for Upgrade Patches. and i have applied it.

    I did a compare with my DEMO and found it to be same same as well.

    One of my senior DBAs suggested an index like below


    CREATE INDEX SYSADM.PSEEX_SHEET_AW ON SYSADM.PS_EX_SHEET_AW(EOAWTHREAD_ID) tablespace PSINDEX;


    We are now testing.
  • 3. Re: 8.9 to 9.1 FP2 FSCM.Data Conversion Step
    mmaller0319 Newbie
    Currently Being Moderated
    The index addition did not help.The UPG_SAC.SACS001.Step12 is that AppEngine Step which is taking hours to execute the below query

    UPDATE PS_EOAW_WL
    SET EOAWDEFN_ID =
    (
    SELECT A.EOAWDEFN_ID
    FROM PS_EX_SHEET_AW A
    WHERE A.EOAWPRCS_ID = 'ERApproval'
    AND PS_EOAW_WL.EOAWTHREAD_ID = A.EOAWTHREAD_ID
    )
    WHERE PS_EOAW_WL.EOAWPRCS_ID = 'ERApproval'
    AND EXISTS
    (
    SELECT 'X'
    FROM PS_EX_SHEET_AW A
    WHERE A.EOAWPRCS_ID = 'ERApproval'
    AND PS_EOAW_WL.EOAWTHREAD_ID = A.EOAWTHREAD_ID
    )

    Still working/researching on the same

    Edited by: mmaller0319 on Jan 29, 2013 2:10 PM
  • 4. Re: 8.9 to 9.1 FP2 FSCM.Data Conversion Step
    678241 Explorer
    Currently Being Moderated
    Did you try to use "/*+ APPEND PARALLEL" hint on the update statement for your performance issue.

    You can also try parallel level turned on at the application table level for the update to complete and alter it back to the default "1"

    Just curious... Thanks
  • 5. Re: 8.9 to 9.1 FP2 FSCM.Data Conversion Step
    mmaller0319 Newbie
    Currently Being Moderated
    I shall try n let u know .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points