SHARED POOL MEMORY USAGE in bytes

Memory Usage V$DB_OBJECT_CACHE This view provides object level statistics for objects in the library cache (shared pool). This view provides more details than V$LIBRARYCACHE and is useful for finding active objects in the shared pool. Useful Columns for V$DB_OBJECT_CACHE Most of the columns of this table provide current state information. OWNER: Object owner NAME: Object name (First 1000 characters of SQL text for anonymous blocks/cursors) TYPE: Type of object (for example, sequence, procedure, function, package, package body, trigger) KEPT: Tells if the object is pinned in the shared pool (yes, no) SHARABLE_MEM: Amount of sharable memory used PINS: Sessions currently executing this object LOCKS: Sessions currently locking this object QuickSql: --generate sql to pin objects in the shared_pool which are not currently pinned.   select 'exec DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(39)||');' as sql_to_run  from V$DB_OBJECT_CACHE where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') and loads > 50 and kept='NO' and executions > 50;     SQL_TO_RUN -------------- exec dbms_shared_pool.keep('SYS.DBMS_JAVA','P');  exec dbms_shared_pool.keep('SYS.DBMS_OUTPUT','P');  exec dbms_shared_pool.keep('SYS.DBMS_PIPE','P');  exec dbms_shared_pool.keep('SYS.DBMS_REGISTRY','P');  exec dbms_shared_pool.keep('SYS.DBMS_RLS','P');  exec dbms_shared_pool.keep('SYS.OWA_MATCH','P');  exec dbms_shared_pool.keep('SYS.OWA_SEC','P');  exec dbms_shared_pool.keep('SYS.OWA_UTIL','P');  exec dbms_shared_pool.keep('SYS.PLITBLM','P');  exec dbms_shared_pool.keep('SYS.STANDARD','P');  exec dbms_shared_pool.keep('SYS.SYSEVENT','P');   --show distribution of shared pool memory across different types of objects. --show if any of the objects have been pinned using the procedure DBMS_SHARED_POOL.KEEP().  col type for a20  col kept for a4  select type,count(*),kept,round(SUM(sharable_mem)/1024,0) share_mem_kilo  from V$DB_OBJECT_CACHE where sharable_mem != 0  GROUP BY type, kept order by 3,4;    TYPE COUNT(*) KEPT SHARE_MEM_KILO  -------------------- ---------- ---- --------------  APP CONTEXT 1 NO 1  SEQUENCE 2 NO 3  NON-EXISTENT 3 NO 3  PIPE 5 NO 6  PUB_SUB 5 NO 8  TRIGGER 4 NO 14  FUNCTION 4 NO 21  SYNONYM 12 NO 56  VIEW 29 NO 66  TABLE 78 NO 161  PACKAGE BODY 11 NO 166  PACKAGE 12 NO 623  CURSOR 42270 NO 332424  INDEX 4 YES 5  CLUSTER 6 YES 12  TABLE 20 YES 43   --find objects with large number of loads   col name for a80 trunc    SELECT owner,sharable_mem,kept,loads,name  from V$DB_OBJECT_CACHE WHERE loads > 2 ORDER BY loads DESC;  OWNER SHARABLE_MEM KEP LOADS NAME  -------------------- ------------ --- ---------- ----------------------------------------  SYS 29304 NO 89 DBMS_SESSION  GENERAL 2567 NO 84 GJBPRUN  BANSECR 22471 NO 78 G$_SECURITY_PKG  BANINST1 27690 NO 66 GB_COMMON  BANINST1 27005 NO 61 GB_MESSAGING  SYS 16496 NO 61 DUAL  GENERAL 2127 NO 52 GUBINST  BANSECR 22464 NO 48 G$_VPDI_SECURITY --find objects using large amounts of memory. pin using DBMS_SHARED_POOL.KEEP( ).     SELECT owner,name,sharable_mem,kept FROM V$DB_OBJECT_CACHE  WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC;    OWNER NAME SHARABLE_MEM KEP  ---------- ---------------------------------------- ------------ ---  select /*+ Rule */ sum(f.bytes)/1024, fl 1463864 NO  select /*+ Rule */ sum(f.bytes)/1024, fl 1461928 NO  select /*+ Rule */ sum(f.bytes)/1024, fl 1357936 NO  select /*+ Rule */ sum(f.bytes)/1024, fl 1353400 NO  SELECT PHVTIME_ID,PHVTIME_LAST_NAM 1249000 NO  insert into SMTCRSE (SMTCRSE_PIDM,SMTCRS 1215256 NO  insert into SMTCRSE (SMTCRSE_PIDM,SMTCRS 1189144 NO  SELECT PHVTIME_ID,PHVTIME_LAST_NAME,PHVT 1182456 NO     --sharable memory in shared pool consumed by the object  col name for a40  col type for a30    select OWNER,NAME,TYPE,SHARABLE_MEM from V$DB_OBJECT_CACHE  where SHARABLE_MEM > 10000  and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')  order by SHARABLE_MEM desc;    OWNER NAME TYPE SHARABLE_MEM  ---------- ---------------------------------------- ------------------------------ ------------  SYS STANDARD PACKAGE 499812  SYS DBMS_JAVA PACKAGE 82685  SYS OWA_UTIL PACKAGE BODY 66732  BANINST1 BWCKFRMT PACKAGE BODY 62009  BANINST1 RB_AWARD_DISBURSEMENT PACKAGE BODY 60606  WTAILOR TWBKBSSF PACKAGE BODY  35152 --determine which objects to pin execute when database is in steady state.    set linesize 150  col Oname for a40  col owner for a15  col Type for a20    SELECT owner||'.'||name Oname,substr(type,1,12) "Type", sharable_mem "Size",executions,loads, kept  FROM V$DB_OBJECT_CACHE  WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')  AND executions > 0 ORDER BY executions desc,loads desc, sharable_mem desc;    ONAME Type Size EXECUTIONS LOADS KEP  ---------------------------------------- -------------------- ---------- ---------- ---------- ---  BANINST1.DML_COMMON PACKAGE BODY 7651 1361387 1 NO  SYS.STANDARD PACKAGE BODY 32684 994931 1 NO  SYS.PLITBLM PACKAGE 7971 742622 6 NO  BANSECR.G$_VPDI_SECURITY PACKAGE BODY 9472 339687 1 NO  BANINST1.ROKLOGS PACKAGE BODY 10568 78872 1 NO  BANINST1.GB_COMMON PACKAGE BODY 15978 45413 8 NO  SYS.DBMS_STANDARD PACKAGE 41969 40198 44 NO  BANSECR.G$_SECURITY_PKG PACKAGE BODY 26807 37695 11 NO  BANINST1.GB_MESSAGING PACKAGE BODY 14053 24138 8 NO  SYS.DBMS_SESSION PACKAGE BODY 10472 14484 8 NO  SYS.DBMS_PIPE PACKAGE BODY 8229 11156 1 NO  BANINST1.ROKPVAL PACKAGE BODY 95696 10021 1 NO  SYS.DBMS_APPLICATION_INFO PACKAGE BODY 4641 9568 10 NO  WTAILOR.TWBKBSSF PACKAGE BODY 35152 7820 1 NO  SYS.HTP PACKAGE BODY 24679 7108 1 NO  BANINST1.RB_AWARD_DISBURSEMENT PACKAGE BODY 60606 6122 1 NO --list large, un-pinned objects.    set linesize 150  col sz for a10  col name for a100  col keeped for a6    select to_char(sharable_mem / 1024,'999999') sz_in_K, decode(kept, 'yes','yes ','') keeped, owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name, null extra, 0 iscur  from v$db_object_cache v where sharable_mem > 1024 * 1000;      --list large, un-pinned procedures, packages, functions.    col type for a25  col name for a40  col owner for a25    select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K  from v$db_object_cache where kept = 'NO'  and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE') group by owner,name,type order by 4;   OWNER NAME TYPE SHARABLE_MEM_K  ------------------------- ---------------------------------------- ------------------------- --------------  SYS DICTIONARY_OBJ_NAME FUNCTION 16.1  SYS DICTIONARY_OBJ_TYPE FUNCTION 16.2  SYS SYSEVENT FUNCTION 16.6  SYS DBMS_APPLICATION_INFO PACKAGE 20.5  SYS DBMS_OUTPUT PACKAGE 21.2  SYS DBMS_STANDARD PACKAGE 36.8  SYS STANDARD PACKAGE 428.2