I found this strange behaviour in Oracle 12cR2 ( over Linux Red Hat 7 ) with this particular query. It looks to me like it is a bug, but I post the question just in case someone has found a similar issue.
The query is triggered by a Java Application, but I have been able to reproduce the problem in sqlplus.
Tip: Don't ask me about the excessive number of parenthesis, I did it myself and it is coming from the application itself, so I use the same sql text to be 100% accurate.
Query 1
SQL> SELECT *
2 from (SELECT
3 MiscellaneousInformation.miscInfoNo,
4 null AS thirdPartyNumber,
5 COALESCE(ProposalAgreement.id,
6 Agreement.id,
7 0) AS alfaAgreementIdentifier,
8 COALESCE(ProposalSchedule.id,
9 Schedule.id,
10 0) AS alfaScheduleIdentifier,
11 COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
12 --ProposalAgreement.agreementNumber an1, Agreement.agreementNumber an2,
13 ProposalAgreement.agreementNumber AS AGREEMENTNUMBER0,
14 --CASE WHEN Agreement.agreementNumber IS NOT NULL THEN Agreement.agreementNumber ELSE ProposalAgreement.agreementNumber END AS AGREEMENTNUMBER,
15 COALESCE(Schedule.scheduleNumber,
16 ProposalSchedule.scheduleNumber,
17 0),
18 COALESCE(Schedule.terminationNumber,
19 ProposalSchedule.terminationNumber,
20 0),
21 0 AS lineOfCreditNumber,
22 0 AS securityIdentifier,
23 0 AS caseIdSequenceNumber,
24 MiscellaneousInformation.informationType,
25 MiscellaneousInformation.detail,
26 MiscellaneousInformation.valueAmount,
27 MiscellaneousInformation.dateField,
28 MiscellaneousInformation.valueCurrency,
29 MiscellaneousInfoListValue.description,
30 MiscellaneousInformation.ownerDiscriminator,
31 MiscellaneousInformation.ownerEntityId
32 FROM
33 ALFATS.MiscellaneousInformation
34 LEFT OUTER JOIN
35 ALFATS.MiscellaneousInfoListValue
36 ON (
37 (
38 MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
39 )
40 AND (
41 MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
42 )
43 )
44 LEFT OUTER JOIN
45 ALFATS.ScheduleEntity
46 ON (
47 MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
48 )
49 LEFT OUTER JOIN
50 ALFATS.Schedule
51 ON (
52 (
53 Schedule.id = ScheduleEntity.primaryScheduleId
54 )
55 AND (
56 Schedule.terminationNumber IN (
57 0,
58 1)))
59 LEFT OUTER JOIN
60 ALFATS.ProposalSchedule
61 ON (
62 MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
63 )
64 LEFT OUTER JOIN
65 ALFATS.Agreement
66 ON (
67 (
68 MiscellaneousInformation.ownerEntityId = Agreement.entityId
69 )
70 OR (
71 Schedule.agreementNumber = Agreement.agreementNumber
72 )
73 OR (
74 ProposalSchedule.agreementNumber = Agreement.agreementNumber
75 )
76 )
77 LEFT OUTER JOIN
78 ALFATS.ProposalAgreement
79 ON (
80 (
81 MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
82 )
83 OR (
84 ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
85 )
86 )
87 WHERE
88 (
89 (
90 (
91 (
92 MiscellaneousInformation.ownerDiscriminator = N'AGR'
93 )
94 OR (
95 MiscellaneousInformation.ownerDiscriminator = N'SCH'
96 )
97 )
98 AND (
99 NOT (EXISTS (SELECT
100 1
101 FROM
102 ALFATS.MiscellaneousInformation miscInfoInner
103 WHERE
104 ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
105 AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
106 AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
107 )
108 ) AND (
109 MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
110 )
111 ) )
112 WHERE OWNERDISCRIMINATOR = 'AGR'
113 AND agreementnumber IS not NULL
114 --AND agreementnumber0 IS NOT NULL
115*
no rows selected
Elapsed: 00:03:07.56
Execution Plan
----------------------------------------------------------
Plan hash value: 70829564
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 671 | 370K| 210K (1)| 00:00:09 |
|* 1 | FILTER | | | | | |
| 2 | MERGE JOIN OUTER | | 671 | 370K| 210K (1)| 00:00:09 |
| 3 | MERGE JOIN OUTER | | 336 | 173K| 208K (1)| 00:00:09 |
| 4 | NESTED LOOPS OUTER | | 112 | 55440 | 207K (1)| 00:00:09 |
| 5 | NESTED LOOPS OUTER | | 112 | 51296 | 207K (1)| 00:00:09 |
|* 6 | HASH JOIN OUTER | | 112 | 42448 | 206K (1)| 00:00:09 |
| 7 | NESTED LOOPS OUTER | | 112 | 35280 | 206K (1)| 00:00:09 |
| 8 | NESTED LOOPS ANTI | | 112 | 23072 | 206K (1)| 00:00:09 |
|* 9 | TABLE ACCESS FULL | MISCELLANEOUSINFORMATION | 11184 | 1299K| 173K (1)| 00:00:07 |
|* 10 | INDEX RANGE SCAN | MISCELLANEOUSINFORMATION_L2 | 13M| 1135M| 3 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| PROPOSALSCHEDULE | 1 | 109 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PROPOSALSCHEDULE_N2 | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | MISCELLANEOUSINFOLISTVALUE | 699 | 44736 | 6 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | SCHEDULEENTITY | 1 | 79 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | SCHEDULEENTITY_N2 | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | SCHEDULE | 1 | 37 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | SCHEDULE_PK | 1 | | 0 (0)| 00:00:01 |
| 18 | BUFFER SORT | | 3 | 105 | 208K (1)| 00:00:09 |
| 19 | VIEW | VW_LAT_B4E6951E | 3 | 105 | 9 (0)| 00:00:01 |
| 20 | VIEW | VW_ORE_A774FCAE | 3 | 105 | 9 (0)| 00:00:01 |
| 21 | UNION-ALL | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | AGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID| AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID| AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
| 28 | BUFFER SORT | | 2 | 70 | 210K (1)| 00:00:09 |
| 29 | VIEW | VW_LAT_B4E6951E | 2 | 70 | 6 (0)| 00:00:01 |
| 30 | VIEW | VW_ORE_E1C15686 | 2 | 70 | 6 (0)| 00:00:01 |
| 31 | UNION-ALL | | | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
6 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(
+) AND "MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
9 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
10 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
"MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
"MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
12 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
15 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
16 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
17 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
23 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
24 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
25 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
26 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
27 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
filter(LNNVL("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
33 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
34 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
35 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
1319465 consistent gets
931422 physical reads
0 redo size
1846 bytes sent via SQL*Net to client
597 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
112034 sorts (memory)
0 sorts (disk)
0 rows processed
So the query 1 returns 0 rows. Well, let's just change the select * from for just to get three columns of the table.
Query 2
SQL> 1 SELECT ownerDiscriminator , agreementnumber, agreementnumber0
2 from (SELECT
3 MiscellaneousInformation.miscInfoNo,
4 null AS thirdPartyNumber,
5 COALESCE(ProposalAgreement.id,
6 Agreement.id,
7 0) AS alfaAgreementIdentifier,
8 COALESCE(ProposalSchedule.id,
9 Schedule.id,
10 0) AS alfaScheduleIdentifier,
11 COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
12 --ProposalAgreement.agreementNumber an1, Agreement.agreementNumber an2,
13 ProposalAgreement.agreementNumber AS AGREEMENTNUMBER0,
14 --CASE WHEN Agreement.agreementNumber IS NOT NULL THEN Agreement.agreementNumber ELSE ProposalAgreement.agreementNumber END AS AGREEMENTNUMBER,
15 COALESCE(Schedule.scheduleNumber,
16 ProposalSchedule.scheduleNumber,
17 0),
18 COALESCE(Schedule.terminationNumber,
19 ProposalSchedule.terminationNumber,
20 0),
21 0 AS lineOfCreditNumber,
22 0 AS securityIdentifier,
23 0 AS caseIdSequenceNumber,
24 MiscellaneousInformation.informationType,
25 MiscellaneousInformation.detail,
26 MiscellaneousInformation.valueAmount,
27 MiscellaneousInformation.dateField,
28 MiscellaneousInformation.valueCurrency,
29 MiscellaneousInfoListValue.description,
30 MiscellaneousInformation.ownerDiscriminator,
31 MiscellaneousInformation.ownerEntityId
32 FROM
33 ALFATS.MiscellaneousInformation
34 LEFT OUTER JOIN
35 ALFATS.MiscellaneousInfoListValue
36 ON (
37 (
38 MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
39 )
40 AND (
41 MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
42 )
43 )
44 LEFT OUTER JOIN
45 ALFATS.ScheduleEntity
46 ON (
47 MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
48 )
49 LEFT OUTER JOIN
50 ALFATS.Schedule
51 ON (
52 (
53 Schedule.id = ScheduleEntity.primaryScheduleId
54 )
55 AND (
56 Schedule.terminationNumber IN (
57 0,
58 1)))
59 LEFT OUTER JOIN
60 ALFATS.ProposalSchedule
61 ON (
62 MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
63 )
64 LEFT OUTER JOIN
65 ALFATS.Agreement
66 ON (
67 (
68 MiscellaneousInformation.ownerEntityId = Agreement.entityId
69 )
70 OR (
71 Schedule.agreementNumber = Agreement.agreementNumber
72 )
73 OR (
74 ProposalSchedule.agreementNumber = Agreement.agreementNumber
75 )
76 )
77 LEFT OUTER JOIN
78 ALFATS.ProposalAgreement
79 ON (
80 (
81 MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
82 )
83 OR (
84 ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
85 )
86 )
87 WHERE
88 (
89 (
90 (
91 (
92 MiscellaneousInformation.ownerDiscriminator = N'AGR'
93 )
94 OR (
95 MiscellaneousInformation.ownerDiscriminator = N'SCH'
96 )
97 )
98 AND (
99 NOT (EXISTS (SELECT
100 1
101 FROM
102 ALFATS.MiscellaneousInformation miscInfoInner
103 WHERE
104 ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
105 AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
106 AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
107 )
108 ) AND (
109 MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
110 )
111 ) )
112 WHERE OWNERDISCRIMINATOR = 'AGR'
113 AND agreementnumber IS not NULL
114* --AND agreementnumber0 IS NOT NULL
115 ;
56017 rows selected.
Elapsed: 00:00:25.63
Execution Plan
----------------------------------------------------------
Plan hash value: 2355278442
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 671 | 309K| 210K (1)| 00:00:09 |
|* 1 | FILTER | | | | | |
| 2 | MERGE JOIN OUTER | | 671 | 309K| 210K (1)| 00:00:09 |
| 3 | MERGE JOIN OUTER | | 336 | 147K| 208K (1)| 00:00:09 |
| 4 | NESTED LOOPS OUTER | | 112 | 47936 | 207K (1)| 00:00:09 |
| 5 | NESTED LOOPS OUTER | | 112 | 44128 | 207K (1)| 00:00:09 |
|* 6 | HASH JOIN RIGHT OUTER | | 112 | 35280 | 206K (1)| 00:00:09 |
| 7 | INDEX FAST FULL SCAN | MISCELLANEOUSINFOLISTVALUE_1 | 699 | 13281 | 3 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 112 | 33152 | 206K (1)| 00:00:09 |
| 9 | NESTED LOOPS ANTI | | 112 | 22176 | 206K (1)| 00:00:09 |
|* 10 | TABLE ACCESS FULL | MISCELLANEOUSINFORMATION | 11184 | 1212K| 173K (1)| 00:00:07 |
|* 11 | INDEX RANGE SCAN | MISCELLANEOUSINFORMATION_L2 | 13M| 1135M| 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| PROPOSALSCHEDULE | 1 | 98 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PROPOSALSCHEDULE_N2 | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | SCHEDULEENTITY | 1 | 79 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | SCHEDULEENTITY_N2 | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | SCHEDULE | 1 | 34 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | SCHEDULE_PK | 1 | | 0 (0)| 00:00:01 |
| 18 | BUFFER SORT | | 3 | 66 | 208K (1)| 00:00:09 |
| 19 | VIEW | VW_LAT_B4E6951E | 3 | 66 | 9 (0)| 00:00:01 |
| 20 | VIEW | VW_ORE_A774FCAE | 3 | 66 | 9 (0)| 00:00:01 |
| 21 | UNION-ALL | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| AGREEMENT | 1 | 98 | 3 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | AGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID| AGREEMENT | 1 | 98 | 3 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID| AGREEMENT | 1 | 98 | 3 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
| 28 | BUFFER SORT | | 2 | 44 | 210K (1)| 00:00:09 |
| 29 | VIEW | VW_LAT_B4E6951E | 2 | 44 | 6 (0)| 00:00:01 |
| 30 | VIEW | VW_ORE_E1C15686 | 2 | 44 | 6 (0)| 00:00:01 |
| 31 | UNION-ALL | | | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 98 | 3 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 98 | 3 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
6 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(+
) AND "MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
10 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
11 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
"MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
"MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
13 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
15 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
16 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
17 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
23 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
24 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
25 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
26 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
27 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
filter(LNNVL("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
33 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
34 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
35 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
1437831 consistent gets
637478 physical reads
0 redo size
2796818 bytes sent via SQL*Net to client
41682 bytes received via SQL*Net from client
3736 SQL*Net roundtrips to/from client
112034 sorts (memory)
0 sorts (disk)
56017 rows processed
How is this even possible ? Am I not losing my mind, right ?
As is a testing environment, I did flush buffer cache and shared pool, checked looking for corruption and even bounce the database. So far, nothing.
Short summary, using select * from gives me 0 rows, using select field, field, field from gives me the right number of rows.
Do you think that this is a bug or can be something different which I am not considering ?
P.S. adding an order by also changes the number of rows returned.
UPDATE
Moving the table segments and rebuilding all their indexes did not solve anything either. I tried even using datapump to export the schema, drop the tablespace, rebuild the tablespace in a different ASM disk and importing back. No effect.
So, it is clear to me that this is a BUG in 12.2, but I can't find exactly which one.
I post this answer, although more like an answer I'd define it like a workaround to the issue. But I believe it is worth it just in case someone finds himself in this issue. I could not find the problematic index ( or indexes ), but let me show you how the hints over the optimizer solve the wrong output result.
Original Query
When you use select * the output is 0 rows. If you use some fields only in the outer select, the result is ok.
Workaround 1
Changing optimizer_features_enabled to version 12.1 does the trick.
SQL> alter session set optimizer_features_enable='12.1.0.2';
Session altered.
Elapsed: 00:00:00.01
SQL> SELECT *
2 from (SELECT
3 MiscellaneousInformation.miscInfoNo,
4 null AS thirdPartyNumber,
5 COALESCE(ProposalAgreement.id,
6 Agreement.id,
7 0) AS alfaAgreementIdentifier,
8 COALESCE(ProposalSchedule.id,
9 Schedule.id,
10 0) AS alfaScheduleIdentifier,
11 COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
12 COALESCE(Schedule.scheduleNumber,
13 ProposalSchedule.scheduleNumber,
14 0) as scheduleNumber,
15 COALESCE(Schedule.terminationNumber,
16 ProposalSchedule.terminationNumber,
17 0) as terminationNumber,
18 0 AS lineOfCreditNumber,
19 0 AS securityIdentifier,
20 0 AS caseIdSequenceNumber,
21 MiscellaneousInformation.informationType,
22 MiscellaneousInformation.detail,
23 MiscellaneousInformation.valueAmount,
24 MiscellaneousInformation.dateField,
25 MiscellaneousInformation.valueCurrency,
26 MiscellaneousInfoListValue.description,
27 MiscellaneousInformation.ownerDiscriminator,
28 MiscellaneousInformation.ownerEntityId
29 FROM
30 ALFATS.MiscellaneousInformation
31 LEFT OUTER JOIN
32 ALFATS.MiscellaneousInfoListValue
33 ON (
34 (
35 MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
36 )
37 AND (
38 MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
39 )
40 )
41 LEFT OUTER JOIN
42 ALFATS.ScheduleEntity
43 ON (
44 MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
45 )
46 LEFT OUTER JOIN
47 ALFATS.Schedule
48 ON (
49 (
50 Schedule.id = ScheduleEntity.primaryScheduleId
51 )
52 AND (
53 Schedule.terminationNumber IN (
54 0,
55 1)))
56 LEFT OUTER JOIN
57 ALFATS.ProposalSchedule
58 ON (
59 MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
60 )
61 LEFT OUTER JOIN
62 ALFATS.Agreement
63 ON (
64 (
65 MiscellaneousInformation.ownerEntityId = Agreement.entityId
66 )
67 OR (
68 Schedule.agreementNumber = Agreement.agreementNumber
69 )
70 OR (
71 ProposalSchedule.agreementNumber = Agreement.agreementNumber
72 )
73 )
74 LEFT OUTER JOIN
75 ALFATS.ProposalAgreement
76 ON (
77 (
78 MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
79 )
80 OR (
81 ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
82 )
83 )
84 WHERE
85 (
86 (
87 (
88 (
89 MiscellaneousInformation.ownerDiscriminator = N'AGR'
90 )
91 OR (
92 MiscellaneousInformation.ownerDiscriminator = N'SCH'
93 )
94 )
95 AND (
96 NOT (EXISTS (SELECT
97 1
98 FROM
99 ALFATS.MiscellaneousInformation miscInfoInner
100 WHERE
101 ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
102 AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
103 AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
104 )
105 ) AND (
106 MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
107 )
108 ) )
109 WHERE OWNERDISCRIMINATOR = 'AGR'
110 AND agreementnumber IS not NULL
111 ;
56011 rows selected.
Elapsed: 00:00:23.28
Execution Plan
----------------------------------------------------------
Plan hash value: 1394098322
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 671 | 370K| 213K (1)| 00:00:09 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | 671 | 370K| 213K (1)| 00:00:09 |
| 3 | NESTED LOOPS OUTER | | 335 | 173K| 211K (1)| 00:00:09 |
| 4 | NESTED LOOPS OUTER | | 112 | 55440 | 210K (1)| 00:00:09 |
| 5 | NESTED LOOPS OUTER | | 112 | 51296 | 210K (1)| 00:00:09 |
|* 6 | HASH JOIN OUTER | | 112 | 42448 | 210K (1)| 00:00:09 |
| 7 | NESTED LOOPS OUTER | | 112 | 35280 | 210K (1)| 00:00:09 |
| 8 | NESTED LOOPS ANTI | | 112 | 23072 | 210K (1)| 00:00:09 |
|* 9 | TABLE ACCESS FULL | MISCELLANEOUSINFORMATION | 11178 | 1299K| 176K (1)| 00:00:07 |
|* 10 | INDEX RANGE SCAN | MISCELLANEOUSINFORMATION_L2 | 13M| 1132M| 3 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| PROPOSALSCHEDULE | 1 | 109 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PROPOSALSCHEDULE_N2 | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | MISCELLANEOUSINFOLISTVALUE | 699 | 44736 | 5 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | SCHEDULEENTITY | 1 | 79 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | SCHEDULEENTITY_N2 | 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | SCHEDULE | 1 | 37 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | SCHEDULE_PK | 1 | | 0 (0)| 00:00:01 |
| 18 | VIEW | VW_LAT_B4E6951E | 3 | 105 | 9 (0)| 00:00:01 |
| 19 | CONCATENATION | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | AGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
| 26 | VIEW | VW_LAT_B4E6951E | 2 | 70 | 5 (0)| 00:00:01 |
| 27 | CONCATENATION | | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 104 | 2 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_NK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
6 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(
+) AND "MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
9 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
10 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
"MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
"MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
12 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
15 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
16 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
17 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
21 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
22 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
23 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
24 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
25 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
filter(LNNVL("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
29 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
30 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
31 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")
Statistics
----------------------------------------------------------
385 recursive calls
3 db block gets
1437939 consistent gets
648378 physical reads
132 redo size
10579547 bytes sent via SQL*Net to client
41681 bytes received via SQL*Net from client
3736 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56011 rows processed
Workaround 2
Changing optimizer_index_caching to 100 solves the problem too, which lead me to believe that the bug might be related with some of the nested loop joins performed by the query.
SQL> alter session set OPTIMIZER_INDEX_CACHING=100;
Session altered.
Elapsed: 00:00:00.00
SQL> SELECT *
2 from (SELECT
3 MiscellaneousInformation.miscInfoNo,
4 null AS thirdPartyNumber,
5 COALESCE(ProposalAgreement.id,
6 Agreement.id,
7 0) AS alfaAgreementIdentifier,
8 COALESCE(ProposalSchedule.id,
9 Schedule.id,
10 0) AS alfaScheduleIdentifier,
11 COALESCE(ProposalAgreement.agreementNumber, Agreement.agreementNumber) AS AGREEMENTNUMBER,
12 COALESCE(Schedule.scheduleNumber,
13 ProposalSchedule.scheduleNumber,
14 0) as scheduleNumber,
15 COALESCE(Schedule.terminationNumber,
16 ProposalSchedule.terminationNumber,
17 0) as terminationNumber,
18 0 AS lineOfCreditNumber,
19 0 AS securityIdentifier,
20 0 AS caseIdSequenceNumber,
21 MiscellaneousInformation.informationType,
22 MiscellaneousInformation.detail,
23 MiscellaneousInformation.valueAmount,
24 MiscellaneousInformation.dateField,
25 MiscellaneousInformation.valueCurrency,
26 MiscellaneousInfoListValue.description,
27 MiscellaneousInformation.ownerDiscriminator,
28 MiscellaneousInformation.ownerEntityId
29 FROM
30 ALFATS.MiscellaneousInformation
31 LEFT OUTER JOIN
32 ALFATS.MiscellaneousInfoListValue
33 ON (
34 (
35 MiscellaneousInformation.informationType = MiscellaneousInfoListValue.informationType
36 )
37 AND (
38 MiscellaneousInformation.detail = MiscellaneousInfoListValue.code
39 )
40 )
41 LEFT OUTER JOIN
42 ALFATS.ScheduleEntity
43 ON (
44 MiscellaneousInformation.ownerEntityId = ScheduleEntity.entityId
45 )
46 LEFT OUTER JOIN
47 ALFATS.Schedule
48 ON (
49 (
50 Schedule.id = ScheduleEntity.primaryScheduleId
51 )
52 AND (
53 Schedule.terminationNumber IN (
54 0,
55 1)))
56 LEFT OUTER JOIN
57 ALFATS.ProposalSchedule
58 ON (
59 MiscellaneousInformation.ownerEntityId = ProposalSchedule.entityId
60 )
61 LEFT OUTER JOIN
62 ALFATS.Agreement
63 ON (
64 (
65 MiscellaneousInformation.ownerEntityId = Agreement.entityId
66 )
67 OR (
68 Schedule.agreementNumber = Agreement.agreementNumber
69 )
70 OR (
71 ProposalSchedule.agreementNumber = Agreement.agreementNumber
72 )
73 )
74 LEFT OUTER JOIN
75 ALFATS.ProposalAgreement
76 ON (
77 (
78 MiscellaneousInformation.ownerEntityId = ProposalAgreement.entityId
79 )
80 OR (
81 ProposalSchedule.agreementNumber = ProposalAgreement.agreementNumber
82 )
83 )
84 WHERE
85 (
86 (
87 (
88 (
89 MiscellaneousInformation.ownerDiscriminator = N'AGR'
90 )
91 OR (
92 MiscellaneousInformation.ownerDiscriminator = N'SCH'
93 )
94 )
95 AND (
96 NOT (EXISTS (SELECT
97 1
98 FROM
99 ALFATS.MiscellaneousInformation miscInfoInner
100 WHERE
101 ((miscInfoInner.ownerEntityId = MiscellaneousInformation.ownerEntityId)
102 AND (miscInfoInner.informationType = MiscellaneousInformation.informationType)
103 AND (miscInfoInner.miscInfoNo > MiscellaneousInformation.miscInfoNo))))
104 )
105 ) AND (
106 MOD(MiscellaneousInformation.miscInfoNo, 20) = 13
107 )
108 ) )
109 WHERE OWNERDISCRIMINATOR = 'AGR'
110 AND agreementnumber IS not NULL
111 ;
56011 rows selected.
Elapsed: 00:00:23.06
Execution Plan
----------------------------------------------------------
Plan hash value: 794275964
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 671 | 370K| 179K (1)| 00:00:08 |
|* 1 | FILTER | | | | | |
| 2 | MERGE JOIN OUTER | | 671 | 370K| 179K (1)| 00:00:08 |
| 3 | MERGE JOIN OUTER | | 335 | 173K| 177K (1)| 00:00:07 |
| 4 | NESTED LOOPS OUTER | | 112 | 55440 | 176K (1)| 00:00:07 |
| 5 | NESTED LOOPS OUTER | | 112 | 51296 | 176K (1)| 00:00:07 |
| 6 | NESTED LOOPS OUTER | | 112 | 42448 | 176K (1)| 00:00:07 |
| 7 | NESTED LOOPS OUTER | | 112 | 35280 | 176K (1)| 00:00:07 |
| 8 | NESTED LOOPS ANTI | | 112 | 23072 | 176K (1)| 00:00:07 |
|* 9 | TABLE ACCESS FULL | MISCELLANEOUSINFORMATION | 11178 | 1299K| 176K (1)| 00:00:07 |
|* 10 | INDEX RANGE SCAN | MISCELLANEOUSINFORMATION_L2 | 13M| 1132M| 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | PROPOSALSCHEDULE | 1 | 109 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PROPOSALSCHEDULE_N2 | 1 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| MISCELLANEOUSINFOLISTVALUE | 1 | 64 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | MISCELLANEOUSINFOLISTVALUE_1 | 1 | | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | SCHEDULEENTITY | 1 | 79 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | SCHEDULEENTITY_N2 | 1 | | 0 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | SCHEDULE | 1 | 37 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | SCHEDULE_PK | 1 | | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 3 | 105 | 177K (1)| 00:00:07 |
| 20 | VIEW | VW_LAT_B4E6951E | 3 | 105 | 9 (0)| 00:00:01 |
| 21 | VIEW | VW_ORE_A774FCAE | 3 | 105 | 9 (0)| 00:00:01 |
| 22 | UNION-ALL | | | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | AGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | AGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | AGREEMENT_NK | 1 | | 2 (0)| 00:00:01 |
| 29 | BUFFER SORT | | 2 | 70 | 179K (1)| 00:00:08 |
| 30 | VIEW | VW_LAT_B4E6951E | 2 | 70 | 5 (0)| 00:00:01 |
| 31 | VIEW | VW_ORE_E1C15686 | 2 | 70 | 5 (0)| 00:00:01 |
| 32 | UNION-ALL | | | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 104 | 3 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_N2 | 1 | | 2 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | PROPOSALAGREEMENT | 1 | 104 | 2 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PROPOSALAGREEMENT_NK | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COALESCE("ITEM_2","ITEM_2") IS NOT NULL)
9 - filter("MISCELLANEOUSINFORMATION"."OWNERDISCRIMINATOR"=U'AGR' AND
MOD("MISCELLANEOUSINFORMATION"."MISCINFONO",20)=13)
10 - access("MISCINFOINNER"."OWNERENTITYID"="MISCELLANEOUSINFORMATION"."OWNERENTITYID" AND
"MISCINFOINNER"."INFORMATIONTYPE"="MISCELLANEOUSINFORMATION"."INFORMATIONTYPE" AND
"MISCINFOINNER"."MISCINFONO">"MISCELLANEOUSINFORMATION"."MISCINFONO")
12 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALSCHEDULE"."ENTITYID"(+))
14 - access("MISCELLANEOUSINFORMATION"."INFORMATIONTYPE"="MISCELLANEOUSINFOLISTVALUE"."INFORMATIONTYPE"(+) AND
"MISCELLANEOUSINFORMATION"."DETAIL"="MISCELLANEOUSINFOLISTVALUE"."CODE"(+))
16 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="SCHEDULEENTITY"."ENTITYID"(+))
17 - filter("SCHEDULE"."TERMINATIONNUMBER"(+)=0 OR "SCHEDULE"."TERMINATIONNUMBER"(+)=1)
18 - access("SCHEDULE"."ID"(+)="SCHEDULEENTITY"."PRIMARYSCHEDULEID")
24 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID")
25 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
26 - access("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
27 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="AGREEMENT"."ENTITYID"))
28 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER")
filter(LNNVL("SCHEDULE"."AGREEMENTNUMBER"="AGREEMENT"."AGREEMENTNUMBER"))
34 - access("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID")
35 - filter(LNNVL("MISCELLANEOUSINFORMATION"."OWNERENTITYID"="PROPOSALAGREEMENT"."ENTITYID"))
36 - access("PROPOSALSCHEDULE"."AGREEMENTNUMBER"="PROPOSALAGREEMENT"."AGREEMENTNUMBER")
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
1487478 consistent gets
648378 physical reads
220 redo size
11574128 bytes sent via SQL*Net to client
41682 bytes received via SQL*Net from client
3736 SQL*Net roundtrips to/from client
112022 sorts (memory)
0 sorts (disk)
56011 rows processed
Summary
Although I could not identify the bug which is producing this issue, I was able to fix the problem. As the provider was not able to change the query, I created a baseline for the original query and then manipulate it to use the plan of the query with the alter session.