12.2 Returns Wrong Query with Bloom Filtering Plan
(Doc ID 2508340.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Symptoms
- Wrong results after upgrade from 11204 to 12.2 with _optimizer_group_by_placement
- Bad plan has bloom filtering:
----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 88 (100)| |
| 1 | HASH GROUP BY | | 221 | 10387 | 88 (10)| 00:00:01 |
|* 2 | HASH JOIN | | 221 | 10387 | 87 (9)| 00:00:01 |
| 3 | VIEW | | 201 | 4020 | 5 (20)| 00:00:01 |
| 4 | HASH GROUP BY | | 201 | 7035 | 5 (20)| 00:00:01 |
|* 5 | HASH JOIN | | 1681 | 58835 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL | EVENT | 186 | 3348 | 2 (0)| 00:00:01 |
|* 7 | INDEX STORAGE FAST FULL SCAN| WAVE | 2115 | 35955 | 2 (0)| 00:00:01 |
| 8 | VIEW | VW_ABC | 279 | 7533 | 82 (8)| 00:00:01 |=================================View VW_ABC
| 9 | HASH GROUP BY | | 279 | 41292 | 82 (8)| 00:00:01 |
|* 10 | HASH JOIN | | 28591 | 4132K| 81 (7)| 00:00:01 |
| 11 | JOIN FILTER CREATE | :CF0000 | 28591 | 4132K| 81 (7)| 00:00:01 | ======================Bloom filtering
|* 12 | HASH JOIN | | 5968 | 308K| 22 (5)| 00:00:01 |
| 13 | TABLE ACCESS STORAGE FULL | PAY | 2947 | 38311 | 3 (0)| 00:00:01 |
|* 14 | TABLE ACCESS STORAGE FULL | TAX | 5959 | 232K| 18 (0)| 00:00:01 |
| 15 | JOIN FILTER USE | :CF0000 | 29286 | 2716K| 59 (7)| 00:00:01 | ======================Bloom filtering
|* 16 | TABLE ACCESS STORAGE FULL | AUD | 29286 | 2716K| 59 (7)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Changes
View VW_ABC in the wrong results execution plan.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |