Oracle Expert

–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 NOTES:
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’