Loading ...
Sorry, an error occurred while loading the content.
 

Re: PeopleSoft DBA Forum INSERT taking so long...

Expand Messages
  • Joe-Nino B. Casimiro
    Hi David, Thanks! I tried so many times to speed it up and able to ran it in 7 mins, inserting 560K rows to JRNL_LN table having 200M existing rows and 9
    Message 1 of 5 , Feb 27, 2008
      Hi David,

      Thanks! I tried so many times to speed it up and able to ran it in 7 mins, inserting 560K rows to JRNL_LN table having 200M existing rows and 9 indexes in place. Really appreciate your help.

      Thanks,
      Onin


      ----- Original Message ----
      From: David Kurtz <david.kurtz@...>
      To: psftdba@yahoogroups.com
      Sent: Friday, February 15, 2008 7:50:37 AM
      Subject: RE: PeopleSoft DBA Forum INSERT taking so long...

      If you used level 12 then the waits are in the trace file.  You need to specify waits=yes on the tkprof command line.
       

      regards
      ____________ _________ ____
      David Kurtz
      Go-Faster Consultancy Ltd.
      tel: +44 (0)7771 760660
      fax: +44 (0)7092 348865
      mailto:david. kurtz@go- faster.co. uk
      web: www.go-faster. co.uk
      Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
      DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle: http://blog. go-faster. co.uk
      PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

       


      From: psftdba@yahoogroups .com [mailto:psftdba@ yahoogroups. com] On Behalf Of Joe-Nino B. Casimiro
      Sent: Monday, February 11, 2008 12:31 AM
      To: psftdba@yahoogroups .com
      Subject: Re: PeopleSoft DBA Forum INSERT taking so long...

      Thank you David, your inputs are very helpful. I will try to trace next time it will run again,  i used level 12 trace for this. I will try level 8. JRNL_LN table has more or less 10 indexes. We suspected it already that we haven't perform any re-org(drop indexes, move table & re-create indexes). That after we do this, we might have a performance gain.

      The only thing we're wondering was when we tested it into another environment with rebuilding the WRK_TMP table having a NEXT 64 extents. The insert went quite fast compare to the normal processing. At least we gain 10-15 mins.

      Again, thank you very much.

      Regards,
      Onin


      ----- Original Message ----
      From: David Kurtz <david.kurtz@ go-faster. co.uk>
      To: psftdba@yahoogroups .com
      Sent: Monday, February 11, 2008 3:08:03 AM
      Subject: RE: PeopleSoft DBA Forum INSERT taking so long...

      In this case we can see that the query on the temp table took 9.523859 seconds, and that the whole statement took 2464 seconds.
      So we can infer that the insert into JRNL_LN and index maintenance took 2454 seconds!
       
      There is a big discrepancy between CPU (866s) and Elapsed time (2464s).  I suggest that you collect the trace again but with wait events (event 10046 level 8 - of use dbms_monitor in 10g). 
       
      I would _guess_ that most of this time is index maintenance.  If it is index maintenance you will see lots of db_file_sequential_ read.  If you want to confirm that it really is index read on the JRNL_LN indexes, then check the file and block numbers in the trace file (you'll have to look in the raw trace file - perhaps load it as an external table) against DBA_EXTENTS.
       
      JRNL_LN is one of the main tables in Financials.  It will have several indexes to support queries, including things live nVision drill downs, and there isn't going to be a huge amount you can do about it.
       
      Has this detiorated recently?  Perhaps you have added a level to one or more of the indexes on the table.
       
      I am slightly suprised to see an index range scan on PS_JGEN_WRK_ TMP14.  The temp table should only contain rows for this process instance.  On the one hand its not a big deal because it is still only taking 10 seconds.  However, these data blocks are being loaded into the buffer cache and will push other blocks out, and that might be decreasing its efficiency.  There is quite a lot of physical disk I/O going on and I wondered whether you needed more buffer cache, but if this query was a full scan, the data blocks from the temp table would be put at the cold end of the buffer cache.
       
      Perhaps the min max values on PROCESS_INSTANCE are out of date.  You might need to add a %UpdateStats to whatever AE populates this table.  Or, if this is Oracle 10g, you might delete and lock the statistics and allow Oracle to dynamically sample stats.  I might even consider adding a FULL hint.
       
      Of course my index maintenance guess might be wrong and it might be something else - and that's why it is important to trace with the wait events.

      regards
      ____________ _________ ____
      David Kurtz
      Go-Faster Consultancy Ltd.
      tel: +44 (0)7771 760660
      fax: +44 (0)7092 348865
      mailto:david. kurtz@go- faster.co. uk
      web: www.go-faster. co.uk
      Book: PeopleSoft for the Oracle DBA: http://www.psftdba. com
      DBA Blogs: PeopleSoft: http://blog. psftdba.com, Oracle: http://blog. go-faster. co.uk
      PeopleSoft DBA Forum: http://groups. yahoo.com/ group/psftdba

       


      From: psftdba@yahoogroups .com [mailto:psftdba@ yahoogroups. com] On Behalf Of Joe-Nino B. Casimiro
      Sent: Sunday, February 10, 2008 8:42 AM
      To: psftdba@yahoogroups .com
      Subject: PeopleSoft DBA Forum INSERT taking so long...

      Hi,

      We have this process that taking so long, this is one of the longest DML on that process. Whole process took 2 hours. The PS_JGEN_WRK_ TMP is a DTT in Peoplesoft. I know the DISK Read and CURRENT GETS is quite high on this. However, one thing we noticed that after we reached 220M rows in PS_JRNL_LN the INSERT process was taking a bit longer comare to the normal one. Would there be anything you may recommend? BTW, when we re-create the PS_GJEN_WRK_ TMP with NEXT extents 64 storage parameter. We noticed of performance gain. Any additional idea how?
      Appreciate your help folks.

      ************ ********* ********* ********* ********* ********* ********* ********* *****
       
      INSERT INTO PS_JRNL_LN (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ,
        JOURNAL_LINE, LEDGER, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT,
        FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1,
         AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, BOOK_CODE,
        GL_ADJUST_TYPE, BUDGET_PERIOD, SCENARIO, CURRENCY_CD, BUSINESS_UNIT_ PC,
        PROJECT_ID, ACTIVITY_ID, RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_ CAT,
         ANALYSIS_TYPE, STATISTICS_CODE, MONETARY_AMOUNT, MOVEMENT_FLAG,
        STATISTIC_AMOUNT, JRNL_LN_REF, SUSPENDED_LINE, LINE_DESCR, JRNL_LINE_STATUS,
         JOURNAL_LINE_ DATE, FOREIGN_CURRENCY, RT_TYPE, FOREIGN_AMOUNT, RATE_DIV,
        RATE_MULT, PROCESS_INSTANCE, DOC_TYPE, DOC_SEQ_NBR, DOC_SEQ_DATE,
        DOC_SEQ_STATUS, JRNL_LINE_SOURCE, BUDGET_DT, BUDGET_LINE_ STATUS,
        CLOSING_STATUS, ENTRY_EVENT, EE_PROC_STATUS, JOURNAL_LINE_ GFEE,
        IU_TRAN_GRP_ NBR, IU_ANCHOR_FLG, FB_EMPL_ID, FB_CLIENT_ID, VOUCHER_ID,
        FB_MISC_MEMO, FB_BUS_REF, FB_VALUE_DT, FB_PYMNT_REF, FB_PYMNT_TYPE,
        FB_USER_ID, BUSINESS_UNIT_ AP, FB_DATE_REF, FB_JRNLSQ, FB_EVENT_ID,
        VOUCHER_LINE_ NUM, DISTRIB_LINE_ NUM) SELECT A.BUSINESS_UNIT, '0005388912' ,
        TO_DATE('2008- 02-07','YYYY- MM-DD'), 0, A.JOURNAL_LINE, A.LEDGER, A.ACCOUNT,
        A.ALTACCT, A.DEPTID, A.OPERATING_ UNIT, A.PRODUCT, A.FUND_CODE, A.CLASS_FLD,
        A.PROGRAM_CODE, A.BUDGET_REF, A.AFFILIATE, A.AFFILIATE_ INTRA1,
        A.AFFILIATE_ INTRA2, A.CHARTFIELD1, A.CHARTFIELD2, A.CHARTFIELD3, ' ',
        A.GL_ADJUST_ TYPE, A.BUDGET_PERIOD, A.SCENARIO, A.CURRENCY_CD,
        A.BUSINESS_UNIT_ PC, A.PROJECT_ID, A.ACTIVITY_ID, A.RESOURCE_TYPE,
        A.RESOURCE_CATEGORY , A.RESOURCE_SUB_ CAT, A.ANALYSIS_TYPE, A.STATISTICS_ CODE,
         A.MONETARY_AMOUNT, A.MOVEMENT_FLAG, A.STATISTIC_ AMOUNT, A.JRNL_LN_REF, 0,
        A.LINE_DESCR, '0', TO_DATE('2008- 02-07','YYYY- MM-DD'), A.FOREIGN_CURRENCY,
        A.RT_TYPE, A.FOREIGN_AMOUNT, A.RATE_DIV, A.RATE_MULT, A.PROCESS_INSTANCE,
        A.DOC_TYPE, A.DOC_SEQ_NBR, TO_DATE('2008- 02-08','YYYY- MM-DD'),
        A.DOC_SEQ_STATUS, 'GUS', TO_DATE('2008- 02-07','YYYY- MM-DD'), 'N', ' ',
        A.ENTRY_EVENT, 'Y', A.JOURNAL_LINE_ GFEE, A.IU_TRAN_GRP_ NBR, A.IU_ANCHOR_ FLG,
         A.FB_EMPL_ID, A.FB_CLIENT_ ID, A.VOUCHER_ID, A.FB_MISC_MEMO, A.FB_BUS_REF,
        A.FB_VALUE_DT, A.FB_PYMNT_REF, A.FB_PYMNT_TYPE, A.FB_USER_ID,
        A.BUSINESS_UNIT_ AP, A.FB_DATE_REF, A.FB_JRNLSQ, A.FB_EVENT_ID,
        A.VOUCHER_LINE_ NUM, A.DISTRIB_LINE_ NUM FROM PS_JGEN_WRK_ TMP14 A WHERE
        A.PROCESS_INSTANCE = 6370404
       

      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.05       0.06          0          0          0           0
      Execute      1    866.11    2464.28     834239     532542    6670110      560132
      Fetch        0      0.00       0.00          0          0          0           0
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        2    866.16    2464.34     834239     532542    6670110      560132
       
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 317  (PSOFT)
       
      Rows     Row Source Operation
      -------  ------------ --------- --------- --------- --------- ---
       560132  TABLE ACCESS BY INDEX ROWID PS_JGEN_WRK_ TMP14 (cr=71703 pr=36336 pw=0 time=9523859 us)
       560132   INDEX RANGE SCAN PSAJGEN_WRK_ TMP14 (cr=50229 pr=17149 pw=0 time=1121612 us)(object id 116606446)
       

      Rows     Execution Plan
      -------  ------------ --------- --------- --------- --------- ---
            0  INSERT STATEMENT   MODE: ALL_ROWS
       560132   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                    'PS_JGEN_WRK_ TMP14' (TABLE)
       560132    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'PSAJGEN_WRK_ TMP14'
                     (INDEX)
       
      ************ ********* ********* ********* ********* ********* ********* ********* *****


      Never miss a thing. Make Yahoo your homepage.



      Looking for last minute shopping deals? Find them fast with Yahoo! Search.



      Looking for last minute shopping deals? Find them fast with Yahoo! Search.
    Your message has been successfully submitted and would be delivered to recipients shortly.