Behavior change of DBMS_SPM in 11.2.0.3
It is no longer possible to create a baseline for statement from the cursor cache and associating it with the plan generated by another statement. Here is the full transcript of my test:
he first statement uses an index. SQL_ID=6tm6vjxx9wwp9
SQL> select count(*) from big_table where first_char='Z';
COUNT(*)
----------
2
Elapsed: 00:00:00.21
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6tm6vjxx9wwp9, child number 0
-------------------------------------
select count(*) from big_table where first_char='Z'
Plan hash value: 889699537
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time
|
--------------------------------------------------------------------------------
he first statement uses an index. SQL_ID=6tm6vjxx9wwp9
SQL> select count(*) from big_table where first_char='Z';
COUNT(*)
----------
2
Elapsed: 00:00:00.21
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6tm6vjxx9wwp9, child number 0
-------------------------------------
select count(*) from big_table where first_char='Z'
Plan hash value: 889699537
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time
|
--------------------------------------------------------------------------------
1