/* Tracks allocations in the shared pool that cause existing objects in the pool to be aged out */

select * from x$ksmlru
order by ksmlrsiz desc;

/* Existing objects in the Shared Pool with size greated than 10K */

select * from v$db_object_cache where sharable_mem > 10000;

/* Large pl/sql objects currently loaded in the shared pool and are not markes as kept */

select name, sharable_mem 
from v$db_object_cache 
where sharable_mem > 10000 
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION' 
or type = 'PROCEDURE') 
and kept = 'NO'; 

/* Queries with more than 1 version */

select sql_text,loaded_versions, version_count, sharable_mem
   from v$sqlarea where loaded_versions > 3
   order by sharable_mem;

/* Percentage of share pool space used */

select 'Shared Pool Used Percentage : '||((value-bytes)/value)*100 from
(select value from v$parameter where name='shared_pool_size'),
v$sgastat where pool='shared pool' and name='free memory';