If you use dbms_stats in 10g it will not automatically invalidate dependant
cursors. This is the default behaviour as of 10.
To revert to older previous behaviour you will have to set the parameter
no_invalidate to false.
.
no_invalidate
Does not invalidate the dependent cursors if set to TRUE.
The procedure invalidates the dependent cursors immediately if set to FALSE.
This is the default.
Begin
dbms_stats.gather_table_stats(……., no_invalidate => false);
End;
/
or you can set defaultparameters DBMS_STATS.SET_PARAM:
This procedure sets default values for parameters of DBMS_STATS procedures.
You can use the GET_PARAM Function to get the current default value of a parameter.
Syntax
DBMS_STATS.SET_PARAM (
pname IN VARCHAR2,
pval IN VARCHAR2);
Solution
Testcase/Examples
==============
alter system flush shared_pool;
drop table t;
create table t( c1 number);
Begin
For i In 1..1000 Loop
Insert Into t
values (I);
End Loop;
End;
/
select * from t where c1=10;
select sql_text, invalidations from v$sql
where sql_text like ‘%select * from t%’
/
–==> invalidations 0
Begin
dbms_stats.gather_table_stats(‘SYSTEM’,’t’,estimate_percent => 100,
cascade => True);
End;
/
select * from t where c1=10;
select sql_text, invalidations from v$sql
where sql_text like ‘%select * from t%’
;
— ==> invalidations 0
Begin
dbms_stats.gather_table_stats(‘SYSTEM’,’t’,estimate_percent => 100,
cascade => True, no_invalidate => false);
End;
/
select * from t where c1=10;
select sql_text, invalidations from v$sql
where sql_text like ‘%select * from t%’
;
— ==> invalidations 1
RESULT:
11 INVALIDATIONS is always 0 and 1 if no_invalidate => false
10.2.0.3 INVALIDATIONS is always 0 and 1 if no_invalidate => false
9.2.0.8 INVALIDATIONS is always 1 after the gather stats
.