Hidden and Undocumented "Cardinality Feedback"

I know this blog is supposed to be about Oracle Streams and AQ only... I could not resist to share one of my recent finding about some undocumented and automatic plan re-evaluation behavior I've faced with my 11.2.0.1 on Linux 32bits. Hopefully you'll forgive me this post...

If you've read the Oracle Database 11.1 documentation, you've found a feature so called "Intelligent or Adaptive Cursor Sharing". From the documentation the feature seems related to bind peeking and you'll find an excellent article about it on the Optimizer Magic's "Update on Adaptive Cursor Sharing" blog. This story seems both very close and very different.

However, like often with Oracle, that's more subtle than what it appears; I've found a case, you should be able to reproduce, where the query is reparsed, though there is not any bind variable.

My demo schema

The behavior my be related to some of my laptop characteristics. My very simple database is running 11.2.0.1 on Linux x86 32 bits with the following parameters:

sga_target=260M
pga_aggregate_target=180M
filesystemio_options=setall

I create a DEMO user and a set of tables, data and statistics. You can download the script from my website.

The query

Execute the query below; the first time, you should figure out that it gets a first plan and its hash value is 1851413986:

-- To change the behavior, set this parameter to none:
-- alter session set "_optimizer_extended_cursor_sharing_rel"=none;
-- Other than that, leave it to simple that is the default value for 11.2.0.1:
-- alter session set "_optimizer_extended_cursor_sharing_rel"=simple;

set timing on
select /* GG */ t.year_id, sum(f.metric1)
from fact f, time t, dim2 d2, dim3 d3, dim4 d4
where f.time_id=t.time_id
and f.dim2_id=d2.dim2_id
and f.dim3_id1=d3.dim3_id1
and f.dim3_id2=d3.dim3_id2
and f.dim4_id=d4.dim4_id
  and d2.dim2_lib='Value 5'
  and d3.dim3_lib='Value (2,2)'
  and d4.dim4_l2='L2.1'
  and attr2='ZZ4'
  and t.time_id=trunc(t.time_id,'W')
group by t.year_id
order by t.year_id;

YEAR_ID   SUM(F.METRIC1)
--------- --------------
01-JAN-09       38490

Elapsed: 00:00:06.10

select *
  from table(dbms_xplan.display_cursor(format=>'basic note'));

PLAN_TABLE_OUTPUT
-----------------
EXPLAINED SQL STATEMENT:
------------------------
select /* GG_2 */ t.year_id, sum(f.metric1)   from fact f, time t, dim2
d2, dim3 d3, dim4 d4  where f.time_id=t.time_id    and
f.dim2_id=d2.dim2_id and f.dim3_id1=d3.dim3_id1    and
f.dim3_id2=d3.dim3_id2   and f.dim4_id=d4.dim4_id    and
d2.dim2_lib='Value 5'  and d3.dim3_lib='Value (2,2)'   and
d4.dim4_l2='L2.1'    and attr2='ZZ4' and
t.time_id=trunc(t.time_id,'W') group by t.year_id  order by t.year_id

Plan hash value: 1851413986

-----------------------------------------------------------------
| Id  | Operation                               | Name          |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                        |               |
|   1 |  SORT GROUP BY                          |               |
|   2 |   NESTED LOOPS                          |               |
|   3 |    NESTED LOOPS                         |               |
|   4 |     HASH JOIN                           |               |
|   5 |      PART JOIN FILTER CREATE            | :BF0000       |
|   6 |       NESTED LOOPS                      |               |
|   7 |        NESTED LOOPS                     |               |
|   8 |         MERGE JOIN CARTESIAN            |               |
|   9 |          PARTITION RANGE ALL            |               |
|  10 |           TABLE ACCESS FULL             | TIME          |
|  11 |          BUFFER SORT                    |               |
|  12 |           TABLE ACCESS FULL             | DIM3          |
|  13 |         PARTITION RANGE ITERATOR        |               |
|  14 |          PARTITION HASH ALL             |               |
|  15 |           BITMAP CONVERSION TO ROWIDS   |               |
|  16 |            BITMAP AND                   |               |
|  17 |             BITMAP INDEX SINGLE VALUE   | FACT_TIME_IDX |
|  18 |             BITMAP INDEX SINGLE VALUE   | FACT_DIM3_IDX |
|  19 |        TABLE ACCESS BY LOCAL INDEX ROWID| FACT          |
|  20 |      PARTITION HASH JOIN-FILTER         |               |
|  21 |       TABLE ACCESS FULL                 | DIM2          |
|  22 |     INDEX UNIQUE SCAN                   | DIM4_PK       |
|  23 |    TABLE ACCESS BY INDEX ROWID          | DIM4          |
-----------------------------------------------------------------

When I've executed the exact same query again, I've found a second plan with a hash value of 1094455219. Though the plan is better than the 1st one in my case, I would have expected the cursor to be reused:

set timing on
select /* GG */ t.year_id, sum(f.metric1)
from fact f, time t, dim2 d2, dim3 d3, dim4 d4
where f.time_id=t.time_id
and f.dim2_id=d2.dim2_id
and f.dim3_id1=d3.dim3_id1
and f.dim3_id2=d3.dim3_id2
and f.dim4_id=d4.dim4_id
  and d2.dim2_lib='Value 5'
  and d3.dim3_lib='Value (2,2)'
  and d4.dim4_l2='L2.1'
  and attr2='ZZ4'
  and t.time_id=trunc(t.time_id,'W')
group by t.year_id
order by t.year_id;

YEAR_ID   SUM(F.METRIC1)
--------- --------------
01-JAN-09       38490

Elapsed: 00:00:00.18

select *
  from table(dbms_xplan.display_cursor(format=>'basic note'));

PLAN_TABLE_OUTPUT
-----------------
EXPLAINED SQL STATEMENT:
------------------------
select /* GG_2 */ t.year_id, sum(f.metric1)   from fact f, time t, dim2
d2, dim3 d3, dim4 d4  where f.time_id=t.time_id    and
f.dim2_id=d2.dim2_id and f.dim3_id1=d3.dim3_id1    and
f.dim3_id2=d3.dim3_id2   and f.dim4_id=d4.dim4_id    and
d2.dim2_lib='Value 5'  and d3.dim3_lib='Value (2,2)'   and
d4.dim4_l2='L2.1'    and attr2='ZZ4' and
t.time_id=trunc(t.time_id,'W') group by t.year_id  order by t.year_id

Plan hash value: 1094455219

--------------------------------------------------------------
| Id  | Operation                            | Name          |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |
|   1 |  SORT GROUP BY                       |               |
|   2 |   HASH JOIN                          |               |
|   3 |    NESTED LOOPS                      |               |
|   4 |     NESTED LOOPS                     |               |
|   5 |      MERGE JOIN CARTESIAN            |               |
|   6 |       MERGE JOIN CARTESIAN           |               |
|   7 |        PARTITION HASH ALL            |               |
|   8 |         TABLE ACCESS FULL            | DIM2          |
|   9 |        BUFFER SORT                   |               |
|  10 |         TABLE ACCESS FULL            | DIM3          |
|  11 |       BUFFER SORT                    |               |
|  12 |        PARTITION RANGE ALL           |               |
|  13 |         TABLE ACCESS FULL            | TIME          |
|  14 |      PARTITION RANGE ITERATOR        |               |
|  15 |       PARTITION HASH ITERATOR        |               |
|  16 |        BITMAP CONVERSION TO ROWIDS   |               |
|  17 |         BITMAP AND                   |               |
|  18 |          BITMAP INDEX SINGLE VALUE   | FACT_TIME_IDX |
|  19 |          BITMAP INDEX SINGLE VALUE   | FACT_DIM3_IDX |
|  20 |          BITMAP INDEX SINGLE VALUE   | FACT_DIM2_IDX |
|  21 |     TABLE ACCESS BY LOCAL INDEX ROWID| FACT          |
|  22 |    TABLE ACCESS FULL                 | DIM4          |
--------------------------------------------------------------

Note
-----
 - cardinality feedback used for this statement

As you can see from the plan note something has changed in the calculation; if you collect the 10053 trace, you'll see, not only the cursor is re-parsed at the 2nd execution time but also some opt_estimate hints are used to correct the cardinality estimate. Interesting?

Conclusion

From my tests, the value of _optimizer_extended_cursor_sharing_rel impacts that behavior. However, the data the CBO relies on to decide it's more clever to perform a plan change doesn't look obvious to me. In addition, the 1st time the query is parsed, the IS_SHAREABLE column of the query is set to 'Y' and if you query V$SQL_SHARED_CURSOR when the 2 cursors are in the shared pool, all you'll find no property to explain the plan change. I'm very curious about the how and the why... Any Idea?