6 Replies Latest reply: Dec 12, 2012 1:10 AM by Nikolay Savvinov RSS

    Query running for hours

    884519
      Please help me tune the below query.



      SELECT DISTINCT geog1.level1 geog,
      gcc1.segment3 acct,
      ---- Return 0 if natural account exist in value set else return Segment4
      NVL((SELECT gcc.segment4
      FROM gl_code_combinations gcc
      WHERE gcc.rowid = gcc1.rowid
      AND EXISTS
      (SELECT *
      FROM fnd_flex_values
      WHERE flex_value_set_id = 123123
      AND enabled_flag = 'Y'
      AND end_date_active IS NULL
      AND flex_value = b1.set_of_books_id ||
      gcc1.segment3)),
      0) subacct,
      -- '0' subacct,
      -- YTD as of previous year --
      (SELECT SUM(DECODE(gcc.account_type,
      'A',
      NVL(b.begin_balance_dr, 0) -
      NVL(b.begin_balance_cr, 0),
      'E',
      NVL(b.begin_balance_dr, 0) -
      NVL(b.begin_balance_cr, 0),
      'L',
      NVL(b.begin_balance_cr, 0) -
      NVL(b.begin_balance_dr, 0),
      'O',
      NVL(b.begin_balance_cr, 0) -
      NVL(b.begin_balance_dr, 0),
      'R',
      NVL(b.begin_balance_cr, 0) -
      NVL(b.begin_balance_dr, 0)) +
      DECODE(gcc.account_type,
      'A',
      NVL(b.period_net_dr, 0) -
      NVL(b.period_net_cr, 0),
      'E',
      NVL(b.period_net_dr, 0) -
      NVL(b.period_net_cr, 0),
      'L',
      NVL(b.period_net_cr, 0) -
      NVL(b.period_net_dr, 0),
      'O',
      NVL(b.period_net_cr, 0) -
      NVL(b.period_net_dr, 0),
      'R',
      NVL(b.period_net_cr, 0) -
      NVL(b.period_net_dr, 0))) ytd_amt
      FROM gl_balances b,
      gl_code_combinations gcc,
      (SELECT distinct segment_code level0,
      parent_segment_code level1
      FROM dhx_seg_hierarchy
      WHERE segment_type = 'GEOENTITY'
      AND bottom_level_flag = 'Y') geog
      WHERE b.code_combination_id =
      gcc.code_combination_id
      AND b.actual_flag = 'A'
      AND b.translated_flag is NULL
      AND gcc.enabled_flag = 'Y'
      AND gcc.end_date_active is NULL
      AND geog.level0 = gcc.segment1
      AND geog1.level1 = geog.level1
      AND gcc1.segment3 = gcc.segment3
      AND gcc1.segment4 = gcc.segment4
      AND b1.set_of_books_id = b.set_of_books_id
      AND b1.currency_code = b.currency_code
      AND b.period_name = &l_prev_period) begin_balance,
      -- YTD as of current year --
      (SELECT SUM(DECODE(gcc.account_type,
      'A',
      NVL(b.begin_balance_dr, 0) -
      NVL(b.begin_balance_cr, 0),
      'E',
      NVL(b.begin_balance_dr, 0) -
      NVL(b.begin_balance_cr, 0),
      'L',
      NVL(b.begin_balance_cr, 0) -
      NVL(b.begin_balance_dr, 0),
      'O',
      NVL(b.begin_balance_cr, 0) -
      NVL(b.begin_balance_dr, 0),
      'R',
      NVL(b.begin_balance_cr, 0) -
      NVL(b.begin_balance_dr, 0)) +
      DECODE(gcc.account_type,
      'A',
      NVL(b.period_net_dr, 0) -
      NVL(b.period_net_cr, 0),
      'E',
      NVL(b.period_net_dr, 0) -
      NVL(b.period_net_cr, 0),
      'L',
      NVL(b.period_net_cr, 0) -
      NVL(b.period_net_dr, 0),
      'O',
      NVL(b.period_net_cr, 0) -
      NVL(b.period_net_dr, 0),
      'R',
      NVL(b.period_net_cr, 0) -
      NVL(b.period_net_dr, 0))) ytd_amt
      FROM gl_balances b,
      gl_code_combinations gcc,
      (SELECT distinct segment_code level0,
      parent_segment_code level1
      FROM dhx_seg_hierarchy
      WHERE segment_type = 'GEOENTITY'
      AND bottom_level_flag = 'Y') geog
      WHERE b.code_combination_id =
      gcc.code_combination_id
      AND b.actual_flag = 'A'
      AND b.translated_flag is NULL
      AND gcc.enabled_flag = 'Y'
      AND gcc.end_date_active is NULL
      AND geog.level0 = gcc.segment1
      AND geog1.level1 = geog.level1
      AND gcc1.segment3 = gcc.segment3
      AND gcc1.segment4 = gcc.segment4
      AND b1.set_of_books_id = b.set_of_books_id
      AND b1.currency_code = b.currency_code
      AND b.period_name = &p_period) Ending_balance
      FROM gl_balances b1,
      gl_code_combinations gcc1,
      (SELECT distinct segment_code level0,
      parent_segment_code level1
      FROM dhx_seg_hierarchy
      WHERE segment_type = 'GEOENTITY'
      AND bottom_level_flag = 'Y'
      AND parent_segment_code LIKE 'C%') geog1
      WHERE b1.code_combination_id = gcc1.code_combination_id
      AND b1.actual_flag = 'A'
      AND b1.translated_flag is NULL
      /* AND b1.set_of_books_id =
      fnd_profile.value('GL_SET_OF_BKS_ID') */
      AND gcc1.enabled_flag = 'Y'
      AND gcc1.end_date_active is NULL
      AND geog1.level0 = gcc1.segment1
      AND b1.period_name = &p_period order by 1;





      Structure for custom table 'dhx_seg_hierarchy' below:

      EXP_BATCH_ID     NUMBER
      SEGMENT_TYPE     VARCHAR2(20)
      SEGMENT_CODE     VARCHAR2(10)
      SEGMENT_DESC     VARCHAR2(240)
      PARENT_SEGMENT_CODE     VARCHAR2(10)
      PARENT_SEGMENT_DESC     VARCHAR2(240)
      ATTRIBUTE1     VARCHAR2(240)
      ATTRIBUTE2     VARCHAR2(240)
      SIGN     VARCHAR2(1)
      BOTTOM_LEVEL_FLAG      VARCHAR2(1)
      CREATION_DATE     DATE
      CREATED_BY     NUMBER
      TRANSLATION_METHOD     VARCHAR2(30)
      NAT_SIGNAGE     VARCHAR2(10)
      FX_METHOD     VARCHAR2(30)
      TYPE     VARCHAR2(1)
      TARGET_FLAG     VARCHAR2(1)


      Thanks,
      gvk.
        • 1. Re: Query running for hours
          861120
          Whats the DB and SO version?

          Please put here the plan of your query
          • 2. Re: Query running for hours
            BluShadow
            First read this:

            {message:id=9360002}

            and specifically read the two threads linked to by this FAQ: {message:id=9360003}

            then post the appropriate details so that people can help.
            • 3. Re: Query running for hours
              884519
              No sure what you mean but here is all I know:

              Daabase: Oracle 11g.
              Application: Oracle Applications 11.5.10.2 (11i)


              Thanks,
              gvk.
              • 4. Re: Query running for hours
                884519
                Explain Plan is below:

                1     Plan hash value: 2881729617
                2     
                3     --------------------------------------------------------------------------------------------------------------
                4     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                5     --------------------------------------------------------------------------------------------------------------
                6     | 0 | SELECT STATEMENT | | 284 | 27548 | 19644 (1)| 00:03:56 |
                7     |* 1 | FILTER | | | | | |
                8     | 2 | TABLE ACCESS BY USER ROWID | GL_CODE_COMBINATIONS | 1 | 18 | 1 (0)| 00:00:01 |
                9     |* 3 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUES | 1 | 17 | 2 (0)| 00:00:01 |
                10     |* 4 | INDEX RANGE SCAN | FND_FLEX_VALUES_N1 | 1 | | 1 (0)| 00:00:01 |
                11     | 5 | TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUE_SETS | 1 | 28 | 2 (0)| 00:00:01 |
                12     |* 6 | INDEX UNIQUE SCAN | FND_FLEX_VALUE_SETS_U2 | 1 | | 1 (0)| 00:00:01 |
                13     | 7 | SORT AGGREGATE | | 1 | 87 | | |
                14     |* 8 | TABLE ACCESS BY INDEX ROWID | GL_BALANCES | 1 | 40 | 7 (0)| 00:00:01 |
                15     | 9 | NESTED LOOPS | | 1 | 87 | 33 (4)| 00:00:01 |
                16     | 10 | NESTED LOOPS | | 1 | 47 | 26 (4)| 00:00:01 |
                17     | 11 | VIEW | | 1 | 14 | 6 (17)| 00:00:01 |
                18     | 12 | SORT UNIQUE | | 1 | 27 | 6 (17)| 00:00:01 |
                19     |* 13 | TABLE ACCESS BY INDEX ROWID| DHX_SEG_HIERARCHY | 1 | 27 | 5 (0)| 00:00:01 |



                Thanks,
                gvk.
                • 5. Re: Query running for hours
                  Marwim
                  Your answer suggests that you didn't read {message:id=1812597} or {thread:id=863295}.
                  • 6. Re: Query running for hours
                    Nikolay Savvinov
                    Hi,

                    according to the plan, the query should be running for a couple of minutes at most. This means that we cannot trust the optimizer, and you haven't posted anything else -- like actual elapsed time, I/O incurred, number of rows returned etc.
                    BTW if the query is taking too long to complete to provide dbms_xplan output, you can use SQL real-time monitor instead (provided you have the Diagnostic Pack License).

                    Best regards,
                    Nikolay