This discussion is archived
6 Replies Latest reply: Dec 11, 2012 11:10 PM by Nikolay Savvinov RSS

Query running for hours

884519 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    Whats the DB and SO version?

    Please put here the plan of your query
  • 2. Re: Query running for hours
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Your answer suggests that you didn't read {message:id=1812597} or {thread:id=863295}.
  • 6. Re: Query running for hours
    Nikolay Savvinov Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points