oracle 12c自适应游标,自适应游标共享的失效（11g）

TEST2：

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

SQL> drop table t;

purge recyclebin;

create table t(id,owner,object_id) as  select rownum,owner,object_id from all_objects where rownum<=1000;

update t set id=999 where id>1;

commit;

create index idx_t on t(id);

exec dbms_stats.gather_table_stats(USER,'T',cascade => true,METHOD_OPT => 'FOR COLUMNS SIZE SKEWONLY ID');

alter system flush shared_pool;

alter system flush buffer_cache;

variable v_id number;

exec :v_id:=999

select sum(object_id) from t where id=:v_id;

select * from table(dbms_xplan.display_cursor(null,null,'PEEKED_BINDS'));

variable v_id number;

exec :v_id:=1

select sum(object_id) from t where id=:v_id;

select * from table(dbms_xplan.display_cursor(null,null,'PEEKED_BINDS'));

Table dropped.

SQL>

Recyclebin purged.

SQL>

Table created.

SQL>

999 rows updated.

SQL>

Commit complete.

SQL>

Index created.

SQL>

PL/SQL procedure successfully completed.

SQL>

System altered.

SQL>

System altered.

SQL> SQL> SQL>

PL/SQL procedure successfully completed.

SQL>

SUM(OBJECT_ID)

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

561799

SQL>

PLAN_TABLE_OUTPUT

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

SQL_ID  fg5bduu6bvz55, child number 0

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

select sum(object_id) from t where id=:v_id

Plan hash value: 2966233522

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    12 |            |          |

PLAN_TABLE_OUTPUT

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

|*  2 |   TABLE ACCESS FULL| T    |   999 | 11988 |     3   (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :V_ID (NUMBER): 999

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

2 - filter("ID"=:V_ID)

24 rows selected.

SQL> SQL> SQL>

PL/SQL procedure successfully completed.

SQL>

SUM(OBJECT_ID)

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

20

SQL>

PLAN_TABLE_OUTPUT

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

SQL_ID  fg5bduu6bvz55, child number 0

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

select sum(object_id) from t where id=:v_id

Plan hash value: 2966233522

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    12 |            |          |

PLAN_TABLE_OUTPUT

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

|*  2 |   TABLE ACCESS FULL| T    |   999 | 11988 |     3   (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :V_ID (NUMBER): 999

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

2 - filter("ID"=:V_ID)

24 rows selected.

SQL> SQL>

SQL>

SQL>

SQL> variable v_id number;

SQL> exec :v_id:=1

PL/SQL procedure successfully completed.

SQL> select sum(object_id) from t where id=:v_id;

SUM(OBJECT_ID)

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

20

SQL> select * from table(dbms_xplan.display_cursor(null,null,'PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

SQL_ID  fg5bduu6bvz55, child number 0

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

select sum(object_id) from t where id=:v_id

Plan hash value: 2966233522

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    12 |            |          |

PLAN_TABLE_OUTPUT

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

|*  2 |   TABLE ACCESS FULL| T    |   999 | 11988 |     3   (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :V_ID (NUMBER): 999

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

2 - filter("ID"=:V_ID)

24 rows selected.