Why it is not using PARALLEL hint with only this query?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Why it is not using PARALLEL hint with only this query?

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Unhappy Why it is not using PARALLEL hint with only this query?

    Hi,

    We are using PARALLEL hint very regularly. But, for some reason, it is not using PARALLEL hint only for the following query.

    SELECT /*+ PARALLEL(glcc,6) */
    glcc.segment3 A_ARC,
    glcc.segment1 Fund,
    tl.hierarchy_name Begin_Line,
    SUBSTR(l.attribute9,1,4),
    glcc.segment2 ,
    0 Budget,
    0,
    0,
    0,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'1',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter1,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'2',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter2,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'3',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter3,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'4',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter4,
    0,0,0,0
    FROM gl_je_lines l,
    gl_je_headers h,
    gl_code_combinations glcc,
    gl_periods glp
    ,fnd_flex_value_hierarchies f
    ,fnd_flex_values v
    ,fnd_flex_hierarchies_tl tl
    WHERE
    glcc.code_combination_id = l.code_combination_id
    and glcc.chart_of_accounts_id = :chart_of_Accounts_id
    and glcc.segment10 NOT LIKE 'ZZ%'
    AND glcc.segment2 BETWEEN f.child_flex_value_low AND f.child_flex_value_high
    AND glcc.summary_flag = 'N'
    AND f.flex_value_set_id = :sac_value_Set_id
    AND f.parent_flex_value = v.flex_value
    AND v.flex_value_set_id = :sac_value_set_id
    AND v.summary_flag = 'Y'
    AND v.structured_hierarchy_level = tl.hierarchy_id
    AND tl.hierarchy_name LIKE '%-%'
    AND l.period_name = glp.period_name
    AND (
    (glp.period_year = :fiscal_year AND :fiscal_year = eriod_year AND glp.period_num <= :begin_num)
    OR
    -- newly added for different year logic
    (TRUNC(l.effective_date) BETWEEN '01-JUL-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'YY') AND '01-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'MON-YY'))
    )
    AND SUBSTR(l.attribute9,1,4) = :fiscal_year -- BUDGET YEAR
    AND l.je_header_id = h.je_header_id
    AND h.actual_Flag IN ('A')
    AND h.je_source IN ('1','Manual','Spreadsheet')

    If I simply use PARALLEL hint in say

    select /*+ PARALLEL(glcc,6) */ segment2 from gl_code_combinations glcc

    then, it shows PARALLEL operation in its Explain Plan.

    Pl. advise.

    Thanks,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Try this:
    select /*+ FULL(glcc) PARALLEL(glcc,6) */

    Tamil

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    It is still not using it. Pl. see the EP below.

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Hint=CHOOSE 1 3595
    NESTED LOOPS 1 145 3595
    HASH JOIN 1 134 3593
    NESTED LOOPS 227 25 K 3588
    MERGE JOIN 226 17 K 876
    SORT JOIN 3 144 18
    NESTED LOOPS 3 144 15
    HASH JOIN 1 27 14
    TABLE ACCESS FULL FND_FLEX_HIERARCHIES_TL 34 374 5
    TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES 87 1 K 8
    INDEX RANGE SCAN FND_FLEX_VALUES_N3 87 1
    INDEX RANGE SCAN FND_FLEX_VALUE_HIERARCHIES_N1 335 6 K 1
    FILTER
    SORT JOIN
    TABLE ACCESS FULL GL_CODE_COMBINATIONS 30 K 884 K 681
    TABLE ACCESS BY INDEX ROWID GL_JE_LINES 30 K 1 M 12
    INDEX RANGE SCAN GL_JE_LINES_N1 30 K 2
    TABLE ACCESS FULL GL_PERIODS 260 4 K 4
    TABLE ACCESS BY INDEX ROWID GL_JE_HEADERS 150 K 1 M 2
    INDEX UNIQUE SCAN GL_JE_HEADERS_U1 150 K 1
    But, interestingly after I commented out the line in the SQL statement, which has a BETWEEN clause, it uses PARALLEL only if I use it with FULL hint.

    See the modified qry.

    SELECT /*+ FULL(glcc) PARALLEL(glcc,6) */
    glcc.segment3 A_ARC,
    glcc.segment1 Fund,
    tl.hierarchy_name Begin_Line,
    SUBSTR(l.attribute9,1,4),
    glcc.segment2 ,
    0 Budget,
    0,
    0,
    0,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'1',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter1,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'2',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter2,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'3',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter3,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'4',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter4,
    0,0,0,0
    FROM gl_je_lines l,
    gl_je_headers h,
    gl_code_combinations glcc,
    gl_periods glp
    ,fnd_flex_value_hierarchies f
    ,fnd_flex_values v
    ,fnd_flex_hierarchies_tl tl
    WHERE
    glcc.code_combination_id = l.code_combination_id
    and glcc.chart_of_accounts_id = :chart_of_Accounts_id
    and glcc.segment10 NOT LIKE 'ZZ%'
    -- and glcc.segment2 BETWEEN f.child_flex_value_low AND f.child_flex_value_high
    AND glcc.summary_flag = 'N'
    AND f.flex_value_set_id = :sac_value_Set_id
    AND f.parent_flex_value = v.flex_value
    AND v.flex_value_set_id = :sac_value_set_id
    AND v.summary_flag = 'Y'
    AND v.structured_hierarchy_level = tl.hierarchy_id
    AND tl.hierarchy_name LIKE '%-%'
    AND l.period_name = glp.period_name
    AND (
    (glp.period_year = :fiscal_year AND :fiscal_year = eriod_year AND glp.period_num <= :begin_num)
    OR
    -- newly added for different year logic
    (TRUNC(l.effective_date) BETWEEN '01-JUL-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'YY') AND '01-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'MON-YY'))
    )
    AND SUBSTR(l.attribute9,1,4) = :fiscal_year -- BUDGET YEAR
    AND l.je_header_id = h.je_header_id
    AND h.actual_Flag IN ('A')
    AND h.je_source IN ('1','Manual','Spreadsheet')

    and modified EP.

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Hint=CHOOSE 228 25072
    HASH JOIN 228 30 K 25072 :Q1705858007 P->S QC (RANDOM)
    INDEX RANGE SCAN FND_FLEX_VALUE_HIERARCHIES_N1 335 3 K 4 :Q1705858003 S->P HASH
    HASH JOIN 87 10 K 25067 :Q1705858006 P->P HASH
    TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES 87 1 K 8 :Q1705858002 S->P HASH
    INDEX RANGE SCAN FND_FLEX_VALUES_N3 87 1
    MERGE JOIN CARTESIAN 2 K 276 K 25058 :Q1705858005 P->P HASH
    NESTED LOOPS 77 7 K 24976 :Q1705858005 PCWP
    HASH JOIN 77 6 K 24822 :Q1705858005 PCWP
    HASH JOIN 77 4 K 24140 :Q1705858000 S->P HASH
    TABLE ACCESS FULL GL_PERIODS 260 4 K 4
    TABLE ACCESS FULL GL_JE_LINES 30 K 1 M 24135
    TABLE ACCESS FULL GL_CODE_COMBINATIONS 30 K 884 K 681 :Q1705858004 P->P HASH
    TABLE ACCESS BY INDEX ROWID GL_JE_HEADERS 150 K 1 M 2 :Q1705858005 PCWP
    INDEX UNIQUE SCAN GL_JE_HEADERS_U1 150 K 1 :Q1705858005 PCWP
    SORT JOIN 34 374 25056 :Q1705858005 PCWP
    TABLE ACCESS FULL FND_FLEX_HIERARCHIES_TL 34 374 5 :Q1705858001 S->P BROADCAST


    Thx.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    How big is your glcc table?

    tamil

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Let me point out that this piece of SQL ...
    Code:
    (TRUNC(l.effective_date) BETWEEN '01-JUL-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'YY') AND '01-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'MON-YY'))
           )
    ... is one of the worst pieces of crap that I've ever seen, and won't do what you think it will.

    You need ...
    Code:
    l.effective_date between to_date('0107'||:P_EXPENDITURE_DATE,'DDMMRR') and Trunc(:P_EXPENDITURE_DATE,'MM')
    ... but i expect that there is a logical problem in the code that means that even this is not what you intend.

    Maybe Oracle is not using parallelelism because it is offended by the horribleness of that statement.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Tamilsevan,

    It has only 182858 rows.

    Hi Slimdave,

    I commented out that whole block of AND & OR condition but it still does not use PARALLEL. Only if I comment out
    'and glcc.segment2 BETWEEN f.child_flex_value_low AND f.child_flex_value_high' line and use FULL and PARALLEL both, it shows PARALLEL usage in the explain plan.

    Also, is there any alternative way of BETWEEN in above statement? Because, if I remove BETWEEN and put EQUAL sign than it uses index but in the query, it needs BETWEEN to compare segment2 with low and high range of child_flex_value.

    Pl. advise.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    For 182K rows, I will not think of parallelism.

    Tamil

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi tamilselvan,

    There is a FTS on glcc table and index is not getting used because of the BETWEEN clause in the query. pl. see the original query and explain plan below.

    SELECT
    glcc.segment3 A_ARC,
    glcc.segment1 Fund,
    tl.hierarchy_name Begin_Line,
    SUBSTR(l.attribute9,1,4),
    glcc.segment2 ,
    0 Budget,
    0,
    0,
    0,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'1',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter1,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'2',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter2,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'3',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter3,
    (DECODE(NVL(SUBSTR(l.attribute9,5,1),glp.quarter_num),'4',NVL(l.accounted_dr,0) - NVL(l.accounted_cr,0))) Exp_Quarter4,
    0,0,0,0
    FROM gl_je_lines l,
    gl_je_headers h,
    gl_code_combinations glcc,
    gl_periods glp
    ,fnd_flex_value_hierarchies f
    ,fnd_flex_values v
    ,fnd_flex_hierarchies_tl tl
    WHERE
    glcc.chart_of_accounts_id = :chart_of_Accounts_id
    AND glcc.summary_flag = 'N'
    AND glcc.segment10 NOT LIKE 'ZZ%'
    AND glcc.segment2 BETWEEN f.child_flex_value_low AND f.child_flex_value_high
    AND f.flex_value_set_id = :sac_value_Set_id
    AND f.parent_flex_value = v.flex_value
    AND v.flex_value_set_id = :sac_value_set_id
    AND v.summary_flag = 'Y'
    AND v.structured_hierarchy_level = tl.hierarchy_id
    AND tl.hierarchy_name LIKE '%-%'
    AND glcc.code_combination_id = l.code_combination_id
    AND l.period_name = glp.period_name
    AND (
    (glp.period_year = :fiscal_year AND :fiscal_year = eriod_year AND glp.period_num <= :begin_num)
    OR
    -- newly added for different year logic
    (TRUNC(l.effective_date) BETWEEN '01-JUL-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'YY') AND '01-'||TO_CHAR(TO_DATE(:P_EXPENDITURE_DATE),'MON-YY'))
    )
    AND SUBSTR(l.attribute9,1,4) = :fiscal_year -- BUDGET YEAR
    AND l.je_header_id = h.je_header_id
    AND h.actual_Flag IN ('A')
    AND h.je_source IN ('1','Manual','Spreadsheet')
    Explain Plan

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    SELECT STATEMENT Hint=CHOOSE 1 3595
    NESTED LOOPS 1 145 3595
    HASH JOIN 1 134 3593
    TABLE ACCESS FULL GL_PERIODS 260 4 K 4
    NESTED LOOPS 227 25 K 3588
    MERGE JOIN 226 17 K 876
    SORT JOIN 3 144 18
    NESTED LOOPS 3 144 15
    HASH JOIN 1 27 14
    TABLE ACCESS FULL FND_FLEX_HIERARCHIES_TL 34 374 5
    TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES 87 1 K 8
    INDEX RANGE SCAN FND_FLEX_VALUES_N3 87 1
    INDEX RANGE SCAN FND_FLEX_VALUE_HIERARCHIES_N1 335 6 K 1
    FILTER
    SORT JOIN
    TABLE ACCESS FULL GL_CODE_COMBINATIONS 30 K 884 K 681
    TABLE ACCESS BY INDEX ROWID GL_JE_LINES 30 K 1 M 12
    INDEX RANGE SCAN GL_JE_LINES_N1 30 K 2
    TABLE ACCESS BY INDEX ROWID GL_JE_HEADERS 150 K 1 M 2
    INDEX UNIQUE SCAN GL_JE_HEADERS_U1 150 K 1
    Pl. advise.

    Thanks
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    I see there is a join missing in the where clause.

    There are 2 independent joins.
    The first one is:
    Table f -->v ---> tl
    f -->glcc

    2nd join is:
    Table l -->glp
    l -->h

    You need to first correct "WHERE" clause.

    Tamil

  10. #10
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi tamilselvan,

    I am sorry for the delay in reply. I was on a vacation. I am not clear how there are two indendent joins. As I can see, all the tables are joined with each other properly.

    I reorganized it a bit as below.

    FROM
    gl_je_lines l,
    gl_je_headers h,
    gl_code_combinations glcc,
    gl_periods glp,
    fnd_flex_value_hierarchies f,
    fnd_flex_values v,
    fnd_flex_hierarchies_tl tl
    WHERE
    h.je_header_id = l.je_header_id
    AND l.code_combination_id = glcc.code_combination_id
    AND l.period_name = glp.period_name
    AND glcc.segment2 BETWEEN f.child_flex_value_low AND f.child_flex_value_high
    AND f.parent_flex_value = v.flex_value
    AND v.structured_hierarchy_level = tl.hierarchy_id

    Could you pl. make it more clear?

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width