_cursor_obsolete_threshold 模拟

创建对象
drop table a0001;
create table a0001  as select * from dba_objects;
create table a0002  as select * from dba_objects where 1=2;
create index iOBJECT_ID on a0001(OBJECT_ID)
create index iCREATED on a0001(CREATED)

收集统计信息
begin
     dbms_stats.gather_table_stats(ownname => user,
                                   tabname => 'A0001',
                                   cascade => true,
                                   estimate_percent => 100,
                                   method_opt => 'for all columns size 1', 
                                   no_invalidate => false,
                                   degree => 1,
                                   force => true);
end;
/

语句执行和收集统计信息交替

set linesize 300
set pagesize 10000
alter session set "_cursor_obsolete_threshold"=3;
var a1 number;
exec :a1:=1001;
select * from a0001 where OBJECT_ID=:a1;
select * from table(dbms_xplan.display_cursor('dfxjn2nq1x3pa',null,format=>'advanced'));

查询确认：
select a.CHILD_NUMBER,a.SQL_TEXT,a.USERS_OPENING,a.INVALIDATIONS,a.IS_OBSOLETE from v$sql a 
where sql_id='dfxjn2nq1x3pa';