5 Replies Latest reply: Mar 20, 2013 11:47 PM by mmaller0319 RSS

    8.9 to 9.1 FP2 FSCM.Data Conversion Step

    mmaller0319
      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
          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
            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
              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
                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
                  I shall try n let u know .