Hi There,
We have recently upgraded to Enterprise Edition and Enabled Parallelism for better performance. Strangely Parallelism giving no results for some queries, which gave correct results when ran serially.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
FYI : We have parallel_degree_policy set to AUTO and parallelism is limited to 2 using Resource Manager.
The Explanation is as below
CASE 1 : Query ran serially
----------------------------------------
- alter session disable parallel query;
- Ran the below SQL query
WITH
PRENTRY_VIEW AS
( SELECT A."CNY#",A."RECORD#",A."LOCATION#",A."DEPT#",A."RECORDTYPE",A."RECORDKEY",A."LINEITEM",A."TRX_AMOUNT",a."AMOUNT",A."EXCHANGE_RATE"
FROM PRENTRY A
WHERE ( (a.location# is null and exists (select 1 from sess_loc_below l where l.cny# = a.cny# and l.lockey = 0) )
or a.location# in (select l.lockey from sess_loc_below l where l.cny# = a.cny# ) )
and ( (a.dept# is null and exists (select d.deptkey from sess_dept_below d where d.cny# = a.cny# and d.deptkey = 0) )
or a.dept# in (select d.deptkey from sess_dept_below d where d.cny# = a.cny# ) )
),
PRENTRYSUM_VIEW AS
(SELECT B.CNY#, B.RECORDKEY, NVL(SUM(B.AMOUNT),0) TOTALENTERED, SUM(DECODE(B.EXCHANGE_RATE, NULL, B.AMOUNT, B.TRX_AMOUNT)) TRX_TOTALENTERED
FROM PRENTRY_VIEW B
WHERE B.LINEITEM <> 'F'
GROUP BY B.CNY#, B.RECORDKEY
),
PRRECORD_VIEW AS
(SELECT A.CNY#, A.RECORD#, A.RECORDTYPE, PRENTRY_VIEW.TOTALENTERED, PRENTRY_VIEW.TRX_TOTALENTERED
FROM PRRECORD A,
PRENTRYSUM_VIEW PRENTRY_VIEW
where ( (a.locationkey is null and exists (select 1 from sessionlocabove l where l.cny# = a.cny# and l.locationkey = 0 and sessionkey = '<SESSION KEY>') )
or a.locationkey in (select l.locationkey from sessionlocabove l where l.cny# = a.cny# and sessionkey = '<SESSION KEY>') )
and ( (a.deptkey is null and exists (select d.deptkey from sessiondeptabove d where d.cny# = a.cny# and d.deptkey = 0 and sessionkey = '<SESSION KEY>') )
or a.deptkey in (select d.deptkey from sessiondeptabove d where d.cny# = a.cny# and sessionkey = '<SESSION KEY>') )
AND A.CNY# = PRENTRY_VIEW.CNY#
AND A.RECORD# = PRENTRY_VIEW.RECORDKEY
)
select p.record#, case
when exists (SELECT COUNT(1)
FROM prentrypymtrecs py
WHERE py.cny# = p.cny#
AND ( py.recordkey = p.record#
OR py.paymentkey = p.record#))
then 1
else 0
end
from prrecord_view p where p.recordtype = 'ri' and p.cny# = <COMPANY ID>;
823639 rows returned , which is the correct result.
Execution plan is as below:
---------------------------------------
Plan hash value: 1097984534
-------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | CONCATENATION | | |
| 3 | TABLE ACCESS BY INDEX ROWID | PRENTRYPYMTRECS | 6 |
|* 4 | INDEX RANGE SCAN | IX_PRENTRYPYMTRECS_PAYMENTKEY | 17 |
|* 5 | TABLE ACCESS BY INDEX ROWID | PRENTRYPYMTRECS | 6 |
|* 6 | INDEX RANGE SCAN | IX_PRENTRYPYMTRECS_RECORDKEY | 17 |
|* 7 | FILTER | | |
| 8 | NESTED LOOPS | | 3 |
| 9 | TABLE ACCESS BY INDEX ROWID | PRRECORD | 24532 |
|* 10 | INDEX RANGE SCAN | IX_PRRECORD_CLRDATE | 24532 |
| 11 | VIEW PUSHED PREDICATE | | 1 |
|* 12 | FILTER | | |
| 13 | SORT AGGREGATE | | 1 |
|* 14 | FILTER | | |
|* 15 | FILTER | | |
| 16 | TABLE ACCESS BY INDEX ROWID| PRENTRY | 5 |
|* 17 | INDEX RANGE SCAN | UK_PRENTRY_COVER | 14 |
|* 18 | INDEX UNIQUE SCAN | PK_SESS_LOC_BELOW | 1 |
|* 19 | INDEX UNIQUE SCAN | PK_SESS_LOC_BELOW | 1 |
|* 20 | INDEX UNIQUE SCAN | PK_SESS_DEPT_BELOW | 1 |
|* 21 | INDEX UNIQUE SCAN | PK_SESS_DEPT_BELOW | 1 |
|* 22 | INDEX UNIQUE SCAN | PK_SESSIONLOCABOVE | 1 |
|* 23 | INDEX UNIQUE SCAN | PK_SESSIONLOCABOVE | 1 |
|* 24 | INDEX UNIQUE SCAN | PK_SESSIONDEPTABOVE | 1 |
|* 25 | INDEX UNIQUE SCAN | PK_SESSIONDEPTABOVE | 1 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PY"."CNY#"=:B1 AND "PY"."PAYMENTKEY"=:B2)
5 - filter(LNNVL("PY"."PAYMENTKEY"=:B1))
6 - access("PY"."CNY#"=:B1 AND "PY"."RECORDKEY"=:B2)
7 - filter((( IS NOT NULL OR ("A"."LOCATIONKEY" IS NULL AND IS NOT
NULL)) AND ( IS NOT NULL OR ("A"."DEPTKEY" IS NULL AND IS NOT NULL))))
10 - access("A"."CNY#"=113243 AND "A"."RECORDTYPE"='ri')
12 - filter(COUNT(*)>0)
14 - filter((( IS NOT NULL OR ("A"."LOCATION#" IS NULL AND IS NOT NULL))
AND ( IS NOT NULL OR ("A"."DEPT#" IS NULL AND IS NOT NULL))))
15 - filter("A"."CNY#"=113243)
17 - access("A"."CNY#"=113243 AND "A"."RECORDKEY"="A"."RECORD#")
filter("A"."LINEITEM"<>'F')
18 - access("L"."CNY#"=:B1 AND "L"."LOCKEY"=:B2)
19 - access("L"."CNY#"=:B1 AND "L"."LOCKEY"=0)
20 - access("D"."CNY#"=:B1 AND "D"."DEPTKEY"=:B2)
21 - access("D"."CNY#"=:B1 AND "D"."DEPTKEY"=0)
22 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"L"."CNY#"=:B1 AND "L"."LOCATIONKEY"=:B2)
23 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"L"."CNY#"=:B1 AND "L"."LOCATIONKEY"=0)
24 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"D"."CNY#"=:B1 AND "D"."DEPTKEY"=:B2)
25 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"D"."CNY#"=:B1 AND "D"."DEPTKEY"=0)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
CASE 2 : Query ran in parallel [ default ]
------------------------------------------------------------
- Same query as above and " NO ROWS" returned.
- Execution Plan is as below
Plan hash value: 2253432787
---------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | CONCATENATION | | |
| 3 | TABLE ACCESS BY INDEX ROWID | PRENTRYPYMTRECS | 6 |
|* 4 | INDEX RANGE SCAN | IX_PRENTRYPYMTRECS_PAYMENTKEY | 17 |
|* 5 | TABLE ACCESS BY INDEX ROWID | PRENTRYPYMTRECS | 6 |
|* 6 | INDEX RANGE SCAN | IX_PRENTRYPYMTRECS_RECORDKEY | 17 |
|* 7 | FILTER | | |
| 8 | PX COORDINATOR | | |
| 9 | PX SEND QC (RANDOM) | :TQ10000 | 3 |
| 10 | NESTED LOOPS | | 3 |
| 11 | PX BLOCK ITERATOR | | |
|* 12 | TABLE ACCESS FULL | PRRECORD | 24532 |
| 13 | VIEW PUSHED PREDICATE | | 1 |
|* 14 | FILTER | | |
| 15 | SORT AGGREGATE | | 1 |
|* 16 | FILTER | | |
|* 17 | FILTER | | |
| 18 | TABLE ACCESS BY INDEX ROWID| PRENTRY | 5 |
|* 19 | INDEX RANGE SCAN | UK_PRENTRY_COVER | 14 |
|* 20 | INDEX UNIQUE SCAN | PK_SESS_LOC_BELOW | 1 |
|* 21 | INDEX UNIQUE SCAN | PK_SESS_LOC_BELOW | 1 |
|* 22 | INDEX UNIQUE SCAN | PK_SESS_DEPT_BELOW | 1 |
|* 23 | INDEX UNIQUE SCAN | PK_SESS_DEPT_BELOW | 1 |
|* 24 | INDEX UNIQUE SCAN | PK_SESSIONLOCABOVE | 1 |
|* 25 | INDEX UNIQUE SCAN | PK_SESSIONLOCABOVE | 1 |
|* 26 | INDEX UNIQUE SCAN | PK_SESSIONDEPTABOVE | 1 |
|* 27 | INDEX UNIQUE SCAN | PK_SESSIONDEPTABOVE | 1 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PY"."CNY#"=:B1 AND "PY"."PAYMENTKEY"=:B2)
5 - filter(LNNVL("PY"."PAYMENTKEY"=:B1))
6 - access("PY"."CNY#"=:B1 AND "PY"."RECORDKEY"=:B2)
7 - filter((( IS NOT NULL OR ("A"."LOCATIONKEY" IS NULL AND IS NOT NULL))
AND ( IS NOT NULL OR ("A"."DEPTKEY" IS NULL AND IS NOT NULL))))
12 - access(:Z>=:Z AND :Z<=:Z)
filter(("A"."CNY#"=113243 AND "A"."RECORDTYPE"='ri'))
14 - filter(COUNT(*)>0)
16 - filter((( IS NOT NULL OR ("A"."LOCATION#" IS NULL AND IS NOT NULL))
AND ( IS NOT NULL OR ("A"."DEPT#" IS NULL AND IS NOT NULL))))
17 - filter("A"."CNY#"=113243)
19 - access("A"."CNY#"=113243 AND "A"."RECORDKEY"="A"."RECORD#")
filter("A"."LINEITEM"<>'F')
20 - access("L"."CNY#"=:B1 AND "L"."LOCKEY"=:B2)
21 - access("L"."CNY#"=:B1 AND "L"."LOCKEY"=0)
22 - access("D"."CNY#"=:B1 AND "D"."DEPTKEY"=:B2)
23 - access("D"."CNY#"=:B1 AND "D"."DEPTKEY"=0)
24 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"L"."CNY#"=:B1 AND "L"."LOCATIONKEY"=:B2)
25 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"L"."CNY#"=:B1 AND "L"."LOCATIONKEY"=0)
26 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"D"."CNY#"=:B1 AND "D"."DEPTKEY"=:B2)
27 - access("SESSIONKEY"='DdI4Vynr02ymDo1UGgAhyuvYbKcOjQ..' AND
"D"."CNY#"=:B1 AND "D"."DEPTKEY"=0)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2 because of degree limit
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Have followed the doc " How to Narrow Down Wrong Results Issues from Parallel Execution (Doc ID 1340246.1)" , but could not help much. Your help is greatly appreciated.
Thanks,
Ram