3 Replies Latest reply on Feb 12, 2014 1:54 PM by David Berger

    Custom Report required Tuning

    user4358330

      Hi Team,

       

      am new to performance tuning

      Any one is there

      how can we proceed to increase performance of sql's running while pl/sql Report in oracle apps.

       

      Regards

      Sampath

        • 1. Re: Custom Report required Tuning
          David Berger

          Hello user43...

           

          There are many possibilities but I think you know it.

           

          First of all you can read about performance tuning here: Contents

           

          If I should give a tip without knowing anything about your requirements/environment and so on, I would say: Parallelize your sql queries... You CAN (it is not sure) make your query faster without knowing what you should tune on it.

           

          I hope it helps.

          • 2. Re: Custom Report required Tuning
            user4358330

            INSERT INTO T123 SELECT GLCC.SEGMENT2 BRANCH, GLCC.SEGMENT5 ACCOUNT,

              SUM(GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR) + SUM(GB.PERIOD_NET_DR -

              GB.PERIOD_NET_CR) AMOUNT FROM GL_BALANCES GB , GL_CODE_COMBINATIONS GLCC ,

              (SELECT FLEX_VALUE BRANCH_VALUE FROM FND_FLEX_VALUE_CHILDREN_V WHERE

              FLEX_VALUE_SET_ID = :B3 START WITH PARENT_FLEX_VALUE BETWEEN :B2 AND :B1

              CONNECT BY PRIOR FLEX_VALUE = PARENT_FLEX_VALUE UNION SELECT A.FLEX_VALUE

              FROM FND_FLEX_VALUES_VL A WHERE A.FLEX_VALUE BETWEEN :B2 AND :B1 AND

              A.ENABLED_FLAG = 'Y' AND A.FLEX_VALUE_SET_ID = :B3 ) BRANCH WHERE 1=1 AND

              GB.LEDGER_ID = :B8 AND GB.PERIOD_NAME = :B7 AND GB.CURRENCY_CODE = :B6 AND

              GB.ACTUAL_FLAG = 'A' AND GB.TEMPLATE_ID IS NULL AND GB.CODE_COMBINATION_ID =

               GLCC.CODE_COMBINATION_ID AND GLCC.SEGMENT2 = BRANCH.BRANCH_VALUE AND

              GLCC.SEGMENT5 BETWEEN NVL(:B5 , GLCC.SEGMENT5) AND NVL(:B4 , GLCC.SEGMENT5)

              GROUP BY GLCC.SEGMENT2, GLCC.SEGMENT5 HAVING SUM(GB.BEGIN_BALANCE_DR -

              GB.BEGIN_BALANCE_CR) + SUM(GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) <> 0

             

            Is taking 20000 Cost how can i Reduce

            and the same query is executing 30+ times in the report

             

            Please guide how to fix.

            • 3. Re: Custom Report required Tuning
              David Berger

              Could you please update your last post so that you choose the "extended editor" mode in your post "right corner on the top of the post-window".

              After that please mark your text and choose a font type: Courier New.

               

              Thanks!