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
- [ID 123214.1]: Truncate - Causes Invalidations in the LIBRARY CACHE
- WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK [ID 115656.1]
Labels: Performance Tuning, v$sqlarea
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home