Monitoring shared pool usage

with a few more useful links:
ORA-04031 Due To High Shared Pool Usage To “GCS DYNAMIC RESOURCES FG” With In-Memory Option (DBIM) (Doc ID 2200825.1)
Bug 27824540 – ORA-4031 Error In Shared Pool Due To Leakage Of ‘ges resource dynamic’ Chunk In RAC Env (Doc ID 27824540.8)
http://blog.chinaunix.net/uid-22948773-id-3284131.html
https://forums.oracle.com/ords/apexds/post/too-much-shared-pool-usage-3456

Technology Blog

Monitoring shared pool usage

–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:>
V$DB_OBJECT_CACHE
This view displays database objects that are cached in the library cache. Objects include tables, indexes,
clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

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’)

View original post 746 more words