Is bind peeking disabled on distributed queries?

Question

I'm having trouble optimizing an Oracle query after an upgrade to Oracle 11g and this problem is starting to drive me a little mad.

Note, this question has been now fully edited because I have more information after creating a simple test case. The original question is available here: https://stackoverflow.com/revisions/12304320/1.

This issue is that when joining two tables, one of which has a between condition on a date column, if the query joins to a remote table, bind peeking doesn't happen.

Here is a test case to help reproduce the problem. First set up two source tables. The first is a list of dates, being the first of the month, going back thirty years

create table mike_temp_etl_control
as 
select
  add_months(trunc(sysdate, 'MM'), 1-row_count) as reporting_date
from (
  select level as row_count
  from dual
  connect by level < 360
);

Then some data sourced from dba_objects:

create table mike_temp_dba_objects as
select owner, object_name, subobject_name, object_id, created
from dba_objects
union all
select owner, object_name, subobject_name, object_id, created
from dba_objects;

Then create an empty table to run the data in to:

create table mike_temp_1
as
select 
  a.OWNER,
  a.OBJECT_NAME,
  a.SUBOBJECT_NAME,
  a.OBJECT_ID,
  a.CREATED,
  b.REPORTING_DATE
from 
  mike_temp_dba_objects a
  join mike_temp_etl_control b on (
      b.reporting_date between add_months(a.created, -24) and a.created)
  where 1=2;

Then run the code. You may need to create a larger version mike_temp_dba_objects to slow the query down (or use some other method to get the execution plan). While the query is running, I get an execution plan from the session by running select * from table(dbms_xplan.display_cursor(sql_id => 'xxxxxxxxxxx')) from a different session.

declare
  pv_report_start_date date := date '2002-01-01';
  v_report_end_date date := date '2012-07-01';

begin

  INSERT /*+ APPEND */
  INTO mike_temp_5
  select 
    a.OWNER,
    a.OBJECT_NAME,
    a.SUBOBJECT_NAME,
    a.OBJECT_ID,
    a.CREATED,
    b.REPORTING_DATE
from 
  mike_temp_dba_objects a
  join mike_temp_etl_control b on (
    b.reporting_date between add_months(a.created, -24) and a.created)
  cross join dual@emirrl -- This line causes problems...
where 
  b.reporting_date between add_months(pv_report_start_date, -12) and v_report_end_date;

  rollback;  
end;

By having a remote table in the query, the cardinality estimate for the mike_temp_etl_control table is completely wrong and bind peeking doesn't seem to be happening.

The execution plan for the query above is shown below:

---------------------------------------------------------------------------------------
| Id  | Operation                | Name                  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                       |       |       |   373 (100)|
|   1 |  LOAD AS SELECT          |                       |       |       |            |
|*  2 |   FILTER                 |                       |       |       |            |
|   3 |    MERGE JOIN            |                       |     5 |   655 |   373  (21)|
|   4 |     SORT JOIN            |                       |  1096 |   130K|   370  (20)|
|   5 |      MERGE JOIN CARTESIAN|                       |  1096 |   130K|   369  (20)|
|   6 |       REMOTE             | DUAL                  |     1 |       |     2   (0)|
|   7 |       BUFFER SORT        |                       |  1096 |   130K|   367  (20)|
|*  8 |        TABLE ACCESS FULL | MIKE_TEMP_DBA_OBJECTS |  1096 |   130K|   367  (20)|
|*  9 |     FILTER               |                       |       |       |            |
|* 10 |      SORT JOIN           |                       |     2 |    18 |     3  (34)|
|* 11 |       TABLE ACCESS FULL  | MIKE_TEMP_ETL_CONTROL |     2 |    18 |     2   (0)|
---------------------------------------------------------------------------------------

If I then replace the remote dual with the local version I get the correct cardinality (139 instead of 2):

-------------------------------------------------------------------------------------
| Id  | Operation              | Name                  | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |                       |       |       | 10682 (100)|
|   1 |  LOAD AS SELECT        |                       |       |       |            |
|*  2 |   FILTER               |                       |       |       |            |
|   3 |    MERGE JOIN          |                       |   152K|    19M| 10682   (3)|
|   4 |     SORT JOIN          |                       |   438K|    51M| 10632   (2)|
|   5 |      NESTED LOOPS      |                       |   438K|    51M|   369  (20)|
|   6 |       FAST DUAL        |                       |     1 |       |     2   (0)|
|*  7 |       TABLE ACCESS FULL| MIKE_TEMP_DBA_OBJECTS |   438K|    51M|   367  (20)|
|*  8 |     FILTER             |                       |       |       |            |
|*  9 |      SORT JOIN         |                       |   139 |  1251 |     3  (34)|
|* 10 |       TABLE ACCESS FULL| MIKE_TEMP_ETL_CONTROL |   139 |  1251 |     2   (0)|
-------------------------------------------------------------------------------------

So, I guess the question is how can I get the correct cardinality to be estimated? Is this an Oracle bug or is this the expected behaviour?

Answer 1

I think you should mess about dynamic sampling. It works in 11g differently so may it is the reason of your troubles.