24 | 06 | 2017
Latest Articles
Popular Articles

Administration

Cursor (no) invalidation after refreshed optimizer stats

User Rating:  / 0
PoorBest 

 

Cursor (no) invalidation after refreshed optimizer stats.



We refresh either manually either with a scheduled job the database optimizer stats.
We wonder what happens with the sql cursors which were prior to the stats refresh already parsed, available in the library cache of the Oracle instance.
What happens with the associated explain plan. Is a new plan immediatly parsed either is the old explain plan still used ?
And if the old explain plan is still used, when are the new stats then taking into account ?


Answer

1.in Oracle 9i new stats immediatly invalidate the dependant cursors.
2.in Oracle 10G new stats do NOT immediatly invalidate the dependant cursors, in stead a child cursor is created after "_optimizer_invalidation_period" seconds
( by default _optimizer_invalidation_period is set at 18000 which is 5 hours )
3.in Oracle 10G & 11G, and if one wants to "throw" out the cursors immedialty one can use the dbms_stats option "no_invalidate => false"
4.in Oracle 10G & 11G, and if one wants to keep the cursors one can use the dbms_stats option "no_invalidate => true"


In the below example is

SESSION A the user session
SESSION B the DBA session.



SQL_SESSION_A > select table_name,last_analyzed from user_tables;

TABLE_NAME               LAST_ANALYZED
------------------------------ ----------------
MY_OBJECTS
T_REPLICA

SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261

SQL_SESSION_A > /

COUNT(*)
----------
1218261


SQL_SESSION_B > select sql_id,address,hash_value,sql_text,sql_id,loaded_versions,invalidations,executions,parse_calls from v$sql where upper(sql_text) like '%COUNT%MY_OBJECTS%';


8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          0         2         2



We notice the default for invalidation for cursors after changed object stats is set to AUTO_INVALIDATE. This means that by default after 5 hours the new optimizer stats will be taken into account and that a new child cursor will then be created.



SQL_SESSION_B > select dbms_stats.get_param('NO_INVALIDATE') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE



SQL_SESSION_B > alter system set "_optimizer_invalidation_period"=60 scope=both;

System altered.





SQL_SESSION_A> begin
2  dbms_stats.gather_table_stats('YORICK','MY_OBJECTS',cascade=>true,no_invalidate => dbms_stats.auto_invalidate);
3  end;
4  /

PL/SQL procedure successfully completed.

SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261

SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261



We notice after "_optimizer_invalidation_period" - 60 seconds in this example - a new child cursor for the same v$sql.sql_id.

 

 


SQL_SESSION_B > select sql_id,address,hash_value,sql_text,sql_id,loaded_versions,invalidations,executions,parse_calls from v$sql where upper(sql_text) like '%COUNT%MY_OBJECTS%';


8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          0         3         3

8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          0         1         1

 

 

There is a child cursor for the same sql id, reason is v$sql_shared_cursor.roll_invalid_mismatch.

 

SQL_SESSION_B > select sql_id,address,child_address,child_number,roll_invalid_mismatch from v$sql_shared_cursor where sql_id='8kr80jws34swt';

SQL_ID          ADDRESS           CHILD_ADDRESS    CHILD_NUMBER R
------------- ---------------- ---------------- ------------ -
8kr80jws34swt 000000007AE3B8F8 000000007AE64C00        0 N
8kr80jws34swt 000000007AE3B8F8 000000007ACCB220        1 Y



We put _optimizer_invalidation_period back to its default value and remove it from the spfile

 

 

SQL_SESSION_B > alter system set "_optimizer_invalidation_period"=18000 scope=memory;

System altered.

SQL_SESSION_B > alter system reset "_optimizer_invalidation_period" scope=spfile sid='*';

System altered.



SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261

SQL_SESSION_A > begin
2  dbms_stats.gather_table_stats('YORICK','MY_OBJECTS',cascade=>true);
3  end;
4  /

PL/SQL procedure successfully completed.

SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261

SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261

SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261

SQL_SESSION_A > /

COUNT(*)
----------
1218261



SQL_SESSION_B > select sql_id,address,hash_value,sql_text,sql_id,loaded_versions,invalidations,executions,parse_calls from v$sql where upper(sql_text) like '%COUNT%MY_OBJECTS%';

8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          0         3         3

8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          0         6         6




SQL_SESSION_A > begin
/* WE ARE GOING TO INVALIDATE CURSORS */
dbms_stats.gather_table_stats('YORICK','MY_OBJECTS',cascade=>true,no_invalidate => false);
end;
/

PL/SQL procedure successfully completed.




SQL_SESSION_B > select sql_id,address,hash_value,sql_text,sql_id,loaded_versions,invalidations,executions,parse_calls from v$sql where upper(sql_text) like '%COUNT%MY_OBJECTS%';
/* WE NOTICE A CHANGE FOR v$sql.invalidations */


8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          1         3         3

8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          1         6         6



SQL_SESSION_A > select count(*) from my_objects;

COUNT(*)
----------
1218261


SQL_SESSION_B > select sql_id,address,hash_value,sql_text,sql_id,loaded_versions,invalidations,executions,parse_calls from v$sql where upper(sql_text) like '%COUNT%MY_OBJECTS%';


8kr80jws34swt 000000007AE3B8F8    808608665
select count(*) from my_objects
8kr80jws34swt            1          1         1         1




SQL_SESSION_B > select sql_id,address,child_address,child_number,roll_invalid_mismatch from
v$sql_shared_cursor where sql_id='8kr80jws34swt';

SQL_ID          ADDRESS           CHILD_ADDRESS    CHILD_NUMBER R
------------- ---------------- ---------------- ------------ -
8kr80jws34swt 000000007AE3B8F8 000000007AE64C00        0 N

 


Note that if you can' t afford refreshing the objects stats with the atrribute no_invalidate => false, you can throw a cursor out of the library cache using the procedure dbms_shared_pool.purge.
Example



SQL_SESSION_B > begin
dbms_shared_pool.purge('000000007E37BEC0,808608665','C');
end;
4  /

PL/SQL procedure successfully completed.