exec dbms_stats.set_system_stats('sreadtim',10000)
exec dbms_stats.set_system_stats('mreadtim',0.01)
exec dbms_stats.set_system_stats('mbrc',512)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)

create table sys.aux_stats$_backup as select * from sys.aux_stats$;

exec dbms_stats.set_system_stats('sreadtim',10000)
exec dbms_stats.set_system_stats('mreadtim',0.01)
exec dbms_stats.set_system_stats('mbrc',512)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)

alter system flush buffer_cache;
alter system flush shared_pool;
alter session set "_serial_direct_read"=never;

explain plan for select * from dept where deptno=10;
select * from table(dbms_xplan.display);

--backup the stats
create table sys.aux_stats$_backup as select * from sys.aux_stats$;

--set up the test: a biggish table, indexed on a not null column
drop table t1;
create table t1 (c1 number);
insert into t1 (select rownum from dual connect by level < 500000);
create index i1 on t1(c1);
alter table t1 modify c1 not null;
exec dbms_stats.gather_table_stats(user,'T1')

--tell the CBO that scan operations are going to be really REALLY slow
--in comparison with single block IO operations

exec dbms_stats.set_system_stats('sreadtim',0.01)
exec dbms_stats.set_system_stats('mreadtim',10000)
exec dbms_stats.set_system_stats('mbrc',1)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)

--clean up the SGA, disable direct reads
alter system flush buffer_cache;
alter system flush shared_pool;
alter session set "_serial_direct_read"=never;

--and I get an index full scan
explain plan for select c1 from t1;
select * from table(dbms_xplan.display);

--now tell the CBO that scans are much faster than single block reads
exec dbms_stats.set_system_stats('sreadtim',10000)
exec dbms_stats.set_system_stats('mreadtim',0.01)
exec dbms_stats.set_system_stats('mbrc',512)
exec dbms_stats.set_system_stats('cpuspeed',1000)
exec dbms_stats.set_system_stats('maxthr',40000000)
exec dbms_stats.set_system_stats('slavethr',10000000)

alter system flush buffer_cache;
alter system flush shared_pool;
alter session set "_serial_direct_read"=never;

--and now I get a table scan 
explain plan for select c1 from t1;
select * from table(dbms_xplan.display);

--put the stats back
delete from sys.aux_stats$;
insert into sys.aux_stats$ select * from sys.aux_stats$_backup;
commit;