Friday, May 11, 2012

Optimizer Funkiness...

I have two simple tables that I want to join in an outer join:

SQL> @bug32052.sql
SQL> set feedback on
SQL> 
SQL> drop table jh1;


Table dropped.


SQL> create table jh1 (x number(10));


Table created.


SQL> 
SQL> drop table jh2;


Table dropped.


SQL> create table jh2 (x number(10));


Table created.


SQL> 
SQL> insert into jh1 values (1);


1 row created.


SQL> commit;


Commit complete.


SQL> 
SQL> select count(*) from jh1;


  COUNT(*)                                                                      
----------                                                                      
         1                                                                      


1 row selected.


SQL> 
SQL> select count(*) from jh2;


  COUNT(*)                                                                      
----------                                                                      
         0                                                                      


1 row selected.


SQL> 

I write a simple OUTER JOIN query...

SQL> select jh1.x, jh2.x from jh1
  2  full outer join jh2 on jh2.x = jh1.x;
...and I expect to get a "1" from jh1 and a null value from jh2:

         X          X                                                           
---------- ----------                                                           
         1                                                                      

1 row selected.

SQL> 

Perfect, that's what I am expecting.  Except in one of my dbs, I get this:

SQL> select jh1.x, jh2.x from jh1
  2  full outer join jh2 on jh2.x = jh1.x;

no rows selected

SQL> 

Hmm, interesting.  After a little detective work, I find on one database my execution plan is:

SQL> select jh1.x, jh2.x from jh1
  2  full outer join jh2 on jh2.x = jh1.x;

         X          X
---------- ----------
         1

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1813927128

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    26 |     5  (20)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     1 |    26 |     5  (20)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     1 |    26 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | JH2      |       |       |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | JH1      |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("JH2"."X"="JH1"."X")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        391  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

and on the db where the query comes back with no rows, it's:

SQL> select jh1.x, jh2.x from jh1
  2  full outer join jh2 on jh2.x = jh1.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2793306587

-----------------------------------------
| Id  | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  VIEW                | VW_FOJ_0 |
|   2 |   MERGE JOIN         |          |
|   3 |    SORT JOIN         |          |
|   4 |     TABLE ACCESS FULL| JH2      |
|*  5 |    SORT JOIN         |          |
|   6 |     TABLE ACCESS FULL| JH1      |
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("JH2"."X"="JH1"."X")
       filter("JH2"."X"="JH1"."X")

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        298  bytes sent via SQL*Net to client
        353  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

So it looks like we have an issue with the Rule Based Optimizer.  I confirm the issue by passing the /*+ rule */ hint on both databases and get the wrong results.  I then use the /*+ dynamic_sampling=7 */ hint on both and get the correct results.

While I know I can get around the issue by setting optimizer_dynamic_sampling=7 in my init.ora file, I am reluctant because I don't know what other queries it will mess up.  I have an SR with Oracle to try and explain why it works one way on one db and one way on the other.

Environment:
  • 11.2.0.2 with the same patches applied on both OHs.
  • Linux x86_64
  • Basically same parameters on both dbs (which is probably where I am going wrong).