Cardinality feedback changed in 11.2.0.2

Recently noticed a change to Cardinality feedback with 11gR2 (Patch set 11.2.0.2).

In 11.2.0.1 Cardinality feedback kicks in immediately when there is major deviation in cardinality after first execution, but with 11.2.0.2 we need to tweak that with /*+ materialize */ hints.

rajesh@ORA11G>select * from v$version;

BANNER

-----------------------------------------------------------------------------

Personal Oracle Database 11g Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

rajesh@ORA11G>select * from table( parse_list(:x) );

COLUMN_VALUE

--------------------

a

b

c

d

rajesh@ORA11G>select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  cd280w5k60sbz, child number 0

-------------------------------------

select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------

| Id  | Operation                         | Name       | Rows  | Bytes |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |            |       |       |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |

------------------------------------------------------------------------

13 rows selected.

rajesh@ORA11G>select * from table( parse_list(:x) );

COLUMN_VALUE

--------------------

a

b

c

d

rajesh@ORA11G>select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  cd280w5k60sbz, child number 1

-------------------------------------

select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------

| Id  | Operation                         | Name       | Rows  | Bytes |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |            |       |       |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |     8 |    16 |

------------------------------------------------------------------------

Note

-----

   - cardinality feedback used for this statement

But starting with 11.2.0.2 this behaves differently, you need to force /*+ materialize */ to have Cardinality feedback kicks in.

rajesh@ORA11G2.2> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

rajesh@ORA11G2.2>

rajesh@ORA11G2.2> select * from table( parse_list(:x) );

COLUMN_VALUE

--------------------

a

b

c

d

4 rows selected.

rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  cd280w5k60sbz, child number 0

-------------------------------------

select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------

| Id  | Operation                         | Name       | Rows  | Bytes |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |            |       |       |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |

------------------------------------------------------------------------

rajesh@ORA11G2.2> select * from table( parse_list(:x) );

COLUMN_VALUE

--------------------

a

b

c

d

4 rows selected.

rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  cd280w5k60sbz, child number 0

-------------------------------------

select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------

| Id  | Operation                         | Name       | Rows  | Bytes |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |            |       |       |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |

------------------------------------------------------------------------

13 rows selected.

rajesh@ORA11G2.2>

But when added /*+ materialize */ hint, execution changes like this.

rajesh@ORA11G2.2> with t as

  2  ( select /*+ materialize */ * from table( parse_list(:x) ) )

  3  select * from t

  4  /

COLUMN_VALUE

--------------------

a

b

c

d

4 rows selected.

rajesh@ORA11G2.2>

rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  949hj409nczd2, child number 0

-------------------------------------

with t as ( select /*+ materialize */ * from table( parse_list(:x) ) )

select * from t

Plan hash value: 3775689547

-------------------------------------------------------------------------------------------

| Id  | Operation                           | Name                        | Rows  | Bytes |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                             |       |       |

|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |

|   2 |   LOAD AS SELECT                    |                             |       |       |

|   3 |    COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST                  |  8168 | 16336 |

|   4 |   VIEW                              |                             |  8168 | 98016 |

|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D664B_4C1BF2F9 |  8168 | 16336 |

-------------------------------------------------------------------------------------------

18 rows selected.

rajesh@ORA11G2.2> with t as

  2  ( select /*+ materialize */ * from table( parse_list(:x) ) )

  3  select * from t

  4  /

COLUMN_VALUE

--------------------

a

b

c

d

4 rows selected.

rajesh@ORA11G2.2>

rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  949hj409nczd2, child number 1

-------------------------------------

with t as ( select /*+ materialize */ * from table( parse_list(:x) ) )

select * from t

Plan hash value: 556187288

-------------------------------------------------------------------------------------------

| Id  | Operation                           | Name                        | Rows  | Bytes |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                             |       |       |

|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |

|   2 |   LOAD AS SELECT                    |                             |       |       |

|   3 |    COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST                  |  8168 | 16336 |

|   4 |   VIEW                              |                             |     4 |    48 |

|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D664C_4C1BF2F9 |     4 |     8 |

-------------------------------------------------------------------------------------------

Note

-----

   - cardinality feedback used for this statement

22 rows selected.

rajesh@ORA11G2.2>