_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 thoughts on “_optimizer_random_plan parameter”

  1. Dominic Brooks

    By default, the original query generates an interesting predicate in the execution plan:
    3 – filter((CASE WHEN ROWID IS NOT NULL THEN 2 ELSE NULL END =3 AND “DUMMY”=’Y’))

    You can always use the ultimate cop out – /*+ no_query_transformation */

  2. Laurent Schneider

    Nice, thank you!

    Actually I am looking for a system wide solution as I am in the process of migrating a db with 3000 views to 11g, maybe I will wait for 11.2.0.3 which is announced for October for AIX

  3. Pingback: 11.2.0.2 Bug – Outer Joins and Literals « Random Neurons

  4. Nicolas Gasparotto

    I was going to tell you that the Oracle support advice will be an upgrade to 11.2.0.3 where that bug seems to be solved (with the same plan as your very first one). A bit of patience, we’re in October already.

    Nicolas.

  5. Laurent Schneider Post author

    Indeed Nicolas :
    Metalink: Solution offered Apply patch 11.2.0.3 when it is released on your platform

    Me: I cannot base my business planning on a patchset which will be released in future

    I do not trust Oracle Support on their schedule, time will say 🙂

  7. Pingback: State of Data #72 « Dr Data's Blog

Comments are closed.