SHARED POOL QUERIES

TOTAL SIZE OF SGA 

select sum(value) SGA_SIZE_BYTES from V$SGA ;-- 8589934592;
select sum(value)/1024/1024/1024 SGA_SIZE_GB from V$SGA ;-- 8589934592;

SHARED POOL QUICK CHECK NOTES
select 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,
       'Request Failures = '||REQUEST_FAILURES  Logic
from     v$shared_pool_reserved
where     REQUEST_FAILURES > 0
and     0 != (
    select     to_number(VALUE)
        from     v$parameter
        where     NAME = 'shared_pool_reserved_size')
union
select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,
       'Request Failures = '||REQUEST_FAILURES Logic
from     v$shared_pool_reserved
where     REQUEST_FAILURES < 5
and     0 != (
    select     to_number(VALUE)
    from     v$parameter
    where     NAME = 'shared_pool_reserved_size');

SHARED POOL MEMORY USAGE

select     OWNER,    NAME||' - '||TYPE object,    SHARABLE_MEM
from     v$db_object_cache
where     SHARABLE_MEM > 10000
and    type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order     by SHARABLE_MEM desc;

LOADS INTO SHARED POOL NOTES

SELECT     OWNER,    NAME||' - '||TYPE OBJECT,    LOADS
FROM     V$DB_OBJECT_CACHE
WHERE     LOADS > 3
AND     TYPE IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
ORDER     BY LOADS DESC

SHARED POOL EXECUTION NOTES

select     OWNER,NAME||' - '||TYPE object,EXECUTIONS
from     v$db_object_cache
where     EXECUTIONS > 100
and     type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order     by EXECUTIONS desc;

SHARED POOL DETAIL NOTES
select    OWNER,NAME,DB_LINK,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS from v$db_object_cache order     by OWNER, NAME;

SHARED POOL V$LIBRARYCACHE STATISTIC NOTES
select     NAMESPACE,GETS,GETHITS,round(GETHITRATIO*100,2) gethit_ratio,PINS,PINHITS,round(PINHITRATIO*100,2) pinhit_ratio,RELOADS,INVALIDATIONS
from     v$librarycache;

SHARED POOL RESERVED SIZE NOTES
select     NAME, VALUE from     v$parameter where     NAME like '%reser%';

PINNED OBJECT NOTES
select     NAME,TYPE,KEPT from     v$db_object_cache where     KEPT = 'YES';

Shows chunks of memory in the shared pool 
CHUNK is the smallest unit of LOBSEGMENT allocation.It is a multiple of DB_BLOCK_SIZE
select /*+ RULE */
decode(sign(ksmchsiz-80),-1,0,trunc(1/log(greatest(ksmchsiz-15,2),2))-5) bucket_no,
decode(decode(sign(ksmchsiz-80),-1,0,trunc(1/log(greatest(ksmchsiz-15,2),2))-5),
  0,'up to 79 bytes',1,'80 to 143 bytes',2,'144 to 271 bytes',
  3,'272 to 527 bytes',4,'528 bytes to 1Kb',5,'1Kb',
  (to_char(power(2,max(trunc(1/log(greatest(ksmchsiz-15,2),2))-10))))||'Kb') chunk_size,
count(*) chunks,
sum(decode(ksmchcls,'freeabl',ksmchsiz,0))/1024 frbl_kb,sum(decode(ksmchcls,'free',ksmchsiz,0))/1024 free_kb,
sum(ksmchsiz/1024) avail_kb,min(ksmchsiz) sml_byte,max(ksmchsiz) lrg_byte
from SYS.X$KSMSP
where ksmchcls like 'free%'
group by decode(sign(ksmchsiz-80),-1,0,trunc(1/log(greatest(ksmchsiz-15,2),2))-5)
union
select
99 bucket_no,'reserved pool',count(*) chunks,
nvl(sum(decode(ksmchcls,'R-freeabl',ksmchsiz,0)),0)/1024 frbl_kb,
nvl(sum(decode(ksmchcls,'R-free',ksmchsiz,0)),0)/1024 free_kb,
nvl(sum(ksmchsiz/1024),0) avail_kb,
nvl(min(ksmchsiz),0) sml_byte,
nvl(max(ksmchsiz),0) lrg_byte
from SYS.X$KSMSP
where ksmchcls like 'R-free%'
order by 1;

TOP 10 COMPONENTS TO HAVE ALLOCATED MEMORY IN THE POOL
SELECT * FROM (SELECT NAME,BYTES/(1024*1024) BYTES_MB  FROM V$SGASTAT WHERE POOL='shared pool' order by bytes desc) where rownum<11;

select s.ksmchptr SP_CHUNK,s.ksmchsiz CH_SIZE,b.obj DATAOBJ#,b.ba BLOCKADDR,b.blsiz BLKSIZE,
       decode(b.class,1,'data block',2,'sort block',3,'save undo block',4,'segment header',5,'save undo header',6,'free list',7,'extent map',
                 8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb',11,'bitmap block',12,'bitmap index block',13,'file header block',14,'unused',
              15,'system undo header',16,'system undo block',17,'undo header',18,'undo block',class)  BLKTYPE,
      decode (b.state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated',b.state) BLKSTATE
from x$bh b,x$ksmsp s
where ( b.ba >= s.ksmchptr and to_number(b.ba, 'XXXXXXXXXXXXXXXX') + b.blsiz < to_number(ksmchptr, 'XXXXXXXXXXXXXXXX') + ksmchsiz)
and s.ksmchcom = 'KGH: NO ACCESS'
order by s.ksmchptr, b.ba;