Administration
Cursor (no) invalidation after refreshed optimizer stats
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 13 October 2011 14:51
-
Last Updated on Wednesday, 05 November 2014 16:44
-
Published on Thursday, 13 October 2011 14:51
-
Written by Guy Lambregts
-
Hits: 7781
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
2 /* WE ARE GOING TO INVALIDATE CURSORS */
3 dbms_stats.gather_table_stats('YORICK','MY_OBJECTS',cascade=>true,no_invalidate => false);
4 end;
5 /
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
2 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
2 dbms_shared_pool.purge('000000007E37BEC0,808608665','C');
3 end;
4 /
PL/SQL procedure successfully completed.