Broadcom Customer Care

 View Only
Expand all | Collapse all

PPM Clarity - Page Rendering Slow on New Database Infrastructure - Query itself runs fast

  • 1.  PPM Clarity - Page Rendering Slow on New Database Infrastructure - Query itself runs fast

    Posted May 23, 2017 06:11 PM

    We have moved to a new database infrastructure.  When we run the same query on services transactions, the new server is 5 times as slow.  yet when we look at the logs it shows that the aggregate value for the page is slow.  If we run the same aggregrate query in the database it runs in split second.   (I use the to_date function for the date).  Overall time though is 97 seconds in the trace.  Pretty much the same exact query is run to return the data to the page (as the aggregate below - that query runs in less than a second - according to the trace).   Date format is set the same way in both databases.

     

    <persistence id="revmgr.getInvestmentTransactionSum" elapsed="97,148.000" elapsedSincePriorNode="3.000" elapsedAfterLastNode="0.000" start="9:35:16:291" finish="9:36:53:439" memoryDelta="82771k">
    <statementSet id="revmgr.getInvestmentTransactionSum" location="chargebacks.xml" elapsedSincePriorNode="0.000" start="9:35:16:291"/>
    <statement id="revmgr.getInvestmentTransactionSum" elapsed="97,148.000" elapsedSincePriorNode="1,494,876,916,292.000" elapsedAfterLastNode="1.000" start="9:35:16:291" finish="9:36:53:439" memoryDelta="82771k">
    chargebacks.xml
    <execute id="STMT@f1d98c6" elapsed="97,146.000" elapsedSincePriorNode="1.000" start="9:35:16:292" finish="9:36:53:438" memoryDelta="82771k">
    <![CDATA[SELECT /* stmt_name:revmgr.getInvestmentTransactionSum */
    SUM(WV.AMOUNT) transactionAmountSum
    FROM INV_INVESTMENTS INV, PPA_WIP WIP, PPA_WIP_VALUES WV
    WHERE
    WIP.TRANSNO = WV.TRANSNO
    AND WIP.INVESTMENT_ID = INV.ID
    AND NVL(WIP.STATUS,0) = 0
    AND WV.CURRENCY_TYPE = 'HOME'
    AND ( (? IS NULL) OR
    (WIP.INVESTMENT_ID IN
    (SELECT FLAT.FROM_INV_ID
    FROM INV_INVESTMENT_ALLOCS_FLAT FLAT
    WHERE FLAT.TO_INV_ID = ?
    AND 1=?
    UNION
    SELECT ? FROM DUAL
    )
    )
    )
    AND (1=1 OR WIP.TRANSNO NOT IN (
    SELECT GLLOCK.TRANSACTION_ID
    FROM CBK_GL_TXNS GLLOCK, CBK_INVOICE IVCLOCK
    WHERE GLLOCK.TRANSACTION_ID = WIP.TRANSNO
    AND GLLOCK.INVOICE_ID = IVCLOCK.ID
    AND (IVCLOCK.STATUS_CODE = 4 OR IVCLOCK.IS_LOCKED = 1)
    ) )
    AND WIP.TRANSDATE BETWEEN ? AND ?-1
    AND WIP.TRANSTYPE IN ( 'L','M','Q','X' )



  • 2.  Re: Page Rendering Slow on New Database Infrastructure - Query itself runs fast

    Broadcom Employee
    Posted May 23, 2017 06:15 PM

    Hi Lori,

    Which product are you expierencing this with?

     

    Regards,

    Colleen