Friday, January 22, 2010

SQL Invalidation

Use the following query to identify cursors that are invalidated:
*****************************
set lines 300
col sql format a50
select substr(sql_text, 1, 40) "SQL", sql_id, invalidations 
from v$sqlarea 
where invalidations > &value 
order by invalidations;

Note: use the "&value" to dynamically enter a value to limit the output

Or use the following SQL to observer invalidations:

select sql_text,version_count,loads,invalidations,parse_calls
from v$sqlarea 
where sql_text like '%xxxxx%';

*****************************
What will cause cursor invalidation?
Activities like TRUNCATE, ANALYZE or DBMS_STATS.GATHER_XXX on tables or indexes, grants changes on unerlying objects.  The associated cursors will stay in the SQL area but when it will be reference next time, it should be reloaded and reparsed fully, so it will have negative impact on library cache.
****************************
Related metalink notes

  1. [ID 123214.1]Truncate - Causes Invalidations in the LIBRARY CACHE 
  2. WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK [ID 115656.1]

Labels: ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home