_optimizer_random_plan parameter

I was trying to find a workaround for a bug in 11.2.0.2

SELECT * FROM
(SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'),
(SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%')
WHERE C = B(+);

B C
---------- ----------
2 3

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 1 | 4 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

As dummy is not Y, B could not be 2.

Ok, I tried :

alter session set "_optimizer_random_plan"=1;

SELECT * FROM
(SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'),
(SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%')
WHERE C = B(+);

B C
---------- ----------
3

Execution Plan
----------------------------------------------------------
Plan hash value: 837538736

-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1146 | 5730 | 27G|
| 1 | MERGE JOIN OUTER | | 603K| 2946K| 27G|
|* 2 | TABLE ACCESS FULL | DUAL | 392K| 767K| 136K|
| 3 | VIEW | | 2 | 6 | 69180 |
|* 4 | FILTER | | | | |
|* 5 | TABLE ACCESS FULL| DUAL | 123K| 240K| 69180 |
-------------------------------------------------------------

Cool, I got correct results! the fact that the cost jumped from 4 to 27 Billions is just a minor annoyance I suppose 😈

I also tried

alter session set "_optimizer_random_plan"=0; -- default

alter session set "_complex_view_merging"=false;

SELECT * FROM
(SELECT 2 B FROM DUAL WHERE DUMMY = 'Y'),
(SELECT 3 C FROM DUAL WHERE DUMMY LIKE '%')
WHERE C = B(+);

B C
---------- ----------
3

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 3 | | |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

The cost is now 5 and instead of 4 and the results are correct

The first thing I did is opening a SR, now I am impatiently waiting for Oracle Support guidance…

8 Comments

Leave a Reply

Your email address will not be published.