To find out the usage of Shared Pool in respect to MB.

With the below query you can get the Shared_pool usage on the database in MB's. You can able to find which Package/Function/Procedure is crunching the Shared Memory.

set pagesize 132

column owner format a16

column name  format a36

column sharable_mem format 999,999,999

column executions   format 999,999,999

prompt

prompt  Memory Usage of Shared Pool Order - Biggest First

prompt

column name format 45

select  owner, name||' - '||type name, sharable_mem from v$db_object_cache

where sharable_mem > 100000

  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')

order by sharable_mem desc

/

Output:

OWNER            NAME                                 SHARABLE_MEM

---------------- ------------------------------------ ------------

SYS              STANDARD - PACKAGE                        650,352

APPS             WSH_TRIPS_ACTIONS - PACKAGE BODY          232,064

APPS             FND_FLEX_SERVER - PACKAGE BODY            164,560

APPS             PO_COMMUNICATION_PVT - PACKAGE BODY       164,272

APPS             LA_ENHNCMTMGR_PKG - PACKAGE BODY          152,280

APPS             FND_FLEX_KEY_API - PACKAGE BODY           143,720

APPS             WSH_INTERFACE_GRP - PACKAGE BODY          124,552

SYS              DBMS_AQIN - PACKAGE                       123,504

APPS             FND_DATA_SECURITY - PACKAGE BODY          118,888

APPS             ICX_SEC - PACKAGE BODY                    100,056