Unshared Queries

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/02/24
-- # Name: unshared_queries.sql
-- ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # Display unshared queries
-- # To find the reason look at V$SQL_SHARED_CURSOR :
-- # http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
-- #
-- ################################################
 
set lines120 pages2000
spool non_shared.log
SELECT sysdate, a.hash_value, a.version_count , a.users_opening , a.users_executing, a.sharable_mem, a.sql_text, c.*
FROM v$sqlarea a, v$sql_shared_cursor c
WHERE a.address=c.address
and a.version_count > 5
order by a.version_count desc;
spool off

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.