Hi we are using version - 11.2.0.4.0 of oracle. We have procedures in which we are gathering stats on the table after data load using below signature, now somehow the SELECT query running immediately after the stats gathering was not able to use the proper plan which should gets generated with the updated stats and the possible cause of that seems to be the default value of the parameter NO_INVALIDATE , so we are planning to forcibly make the parameter 'FALSE' so that the child cursor would gets invalidated and the immediate query will get evaluated using the corrects stats of the tables.
btw, i was under impression that we can achieve this without chnaging the code(stats gathering code), i.e by setting the table preferences so that during stats gathering it will use the value of NO_INVALIDATE as FALSE automatically from the set preferences, but not sure why its not working as expected.can you help me understanding this? here i have tried replicating the scenario, case-1 and case-2 are expected but in case-3 i was expecting the child cursor to be invalidated but its not happening.
create table t1 as select * from dba_objects;
--case 1
SELECT DBMS_STATS.GET_PREFS('NO_INVALIDATE','USER1','T1') from dual -- DBMS_STATS.AUTO_INVALIDATE
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME => 'user1' ,TABNAME => 'T1' ,
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ,METHOD_OPT => 'FOR columns object_name', cascade => TRUE , force => TRUE);
END;
/
--execute the select
select /*test1*/ * from t1 where rownum<1;
-- verofying status of the child cursor. Its showing invalidations as 0.
select object_status, child_number,address,hash_value,Parse_calls,executions,invalidations from gv$sql where sql_id='g5g1cbscu5r1s';
VALID 0 000000006B9CD468 430103608 2 1 0
-- case 2
Now i am setting the no_invalidate as FALSE in the stats gather signature, so that the child cursor will be invalidated immediately
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME => 'user1' ,TABNAME => 'T1' ,
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ,METHOD_OPT => 'FOR columns object_name', cascade => TRUE , force => TRUE, no_invalidate=>FALSE);
END;
/
--execute the select
select /*test1*/ * from t1 where rownum<1;
-- verifying status of the child cursor. Its showing invalidations as 1.
select object_status, child_number,address,hash_value,Parse_calls,executions,invalidations from gv$sql where sql_id='g5g1cbscu5r1s';
VALID 0 000000006B9CD468 430103608 1 1 1
-- case 3
Now i am setting the no_invalidate in thetable preferences as FALSE and exPecting the stats gather will Pick that from the table Preferences even i will not set the 'no_invalidate' in the stats gathering signature , so here too , the child cursor should gets invalidated immediately
begin
DBMS_STATS.SET_table_PREFS('user1','T1','NO_INVALIDATE','FALSE') ;
end;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME => 'user1' ,TABNAME => 'T1' ,
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ,METHOD_OPT => 'FOR columns object_name', cascade => TRUE , force => TRUE);
END;
/
--execute the select
select /*test2*/ * from t1 where rownum<1;
-- verofying status of the child cursor. Its showing invalidations as 0.
select object_status, child_number,address,hash_value,Parse_calls,executions,invalidations from gv$sql where sql_id='739jtuna9x9cr';
VALID 0 000000006B8F1968 345941399 1 1 0