-
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
-
Try this:
select /*+ FULL(glcc) PARALLEL(glcc,6) */
Tamil
-
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
-
How big is your glcc table?
tamil
-
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.
-
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
-
For 182K rows, I will not think of parallelism.
Tamil
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|