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’)
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’
Script to Estimate Shared Pool Utilization
This script estimates Shared Pool utilization.
Sample Output
Copy Script to Clipboard
REM LOCATION: Database TuningShared Pool Reports
REM FUNCTION: Estimates shared pool utilization
REM TESTED ON: 7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM: non-specific
REM REQUIRES: v$db_object_cache, v$sqlarea, v$sesstat, v$statname,
REM v$sgastat, v$parameter
REM
REM This is a part of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C) 2008 Quest Software
REM All rights reserved.
REM
REM******************** Knowledge Xpert for Oracle Administration ********************
REM
REM NOTES: Based on current database usage. This should be
REM run during peak operation, after all stored
REM objects i.e. packages, views have been loaded.
REM
REM 08/02/08 Robert Freeman - Modified to use v$sgastat instead v$parameter for
REM shared pool size.
REM***********************************************************************************
REM
REM If running Shared Server uncomment the mts calculation and output commands.
SET serveroutput on;
DECLARE
object_mem NUMBER;
shared_sql NUMBER;
cursor_mem NUMBER;
mts_mem NUMBER;
used_pool_size NUMBER;
free_mem NUMBER;
pool_size VARCHAR2 (512); -- Now from V$SGASTAT
BEGIN
-- Stored objects (packages, views)
SELECT SUM (sharable_mem)
INTO object_mem
FROM v$db_object_cache;
-- Shared SQL -- need to have additional memory if dynamic SQL used
SELECT SUM (sharable_mem)
INTO shared_sql
FROM v$sqlarea;
-- User Cursor Usage -- run this during peak usage.
-- assumes 250 bytes per open cursor, for each concurrent user.
SELECT SUM (250 * users_opening)
INTO cursor_mem
FROM v$sqlarea;
-- For a test system -- get usage for one user, multiply by # users
-- select (250 * value) bytes_per_user
-- from v$sesstat s, v$statname n
-- where s.statistic# = n.statistic#
-- and n.name = 'opened cursors current'
-- and s.sid = 25; -- where 25 is the sid of the process
-- MTS memory needed to hold session information for shared server users
-- This query computes a total for all currently logged on users (run
-- multiply by # users.
SELECT SUM (VALUE)
INTO mts_mem
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.NAME = 'session uga memory max';
-- Free (unused) memory in the SGA: gives an indication of how much memory
-- is being wasted out of the total allocated.
SELECT BYTES
INTO free_mem
FROM v$sgastat
WHERE NAME = 'free memory' AND pool = 'shared pool';
-- For non-MTS add up object, shared sql, cursors and 20% overhead.
used_pool_size := ROUND (1.2 * (object_mem + shared_sql + cursor_mem));
-- For MTS mts contribution needs to be included (comment out previous line)
-- used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
SELECT SUM (BYTES)
INTO pool_size
FROM v$sgastat
WHERE pool = 'shared pool';
-- Display results
DBMS_OUTPUT.put_line ('Shared Pool Memory Utilization Report');
DBMS_OUTPUT.put_line ('Obj mem: ' || TO_CHAR (object_mem) || ' bytes');
DBMS_OUTPUT.put_line ('Shared sql: ' || TO_CHAR (shared_sql) || ' bytes');
DBMS_OUTPUT.put_line ('Cursors: ' || TO_CHAR (cursor_mem) || ' bytes');
-- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
DBMS_OUTPUT.put_line ( 'Free memory: '
|| TO_CHAR (free_mem)
|| ' bytes '
|| '('
|| TO_CHAR (ROUND (free_mem / 1024 / 1024, 2))
|| 'MB)'
);
DBMS_OUTPUT.put_line ( 'Shared pool utilization (total): '
|| TO_CHAR (used_pool_size)
|| ' bytes '
|| '('
|| TO_CHAR (ROUND (used_pool_size / 1024 / 1024, 2))
|| 'MB)'
);
DBMS_OUTPUT.put_line ( 'Shared pool allocation (actual): '
|| pool_size
|| ' bytes '
|| '('
|| TO_CHAR (ROUND (pool_size / 1024 / 1024, 2))
|| 'MB)'
);
DBMS_OUTPUT.put_line ( 'Percentage Utilized: '
|| TO_CHAR (ROUND (used_pool_size / pool_size * 100))
);
END;
/
Sample Output
Shared Pool Memory Utilization Report
Obj mem: 64949920 bytes
Shared sql: 21737236 bytes
Cursors: 14250 bytes
Free memory: 54037748 bytes (51.53MB)
Shared pool utilization (total): 104041687 bytes (99.22MB)
Shared pool allocation (actual): 180359768 bytes (172MB)
Percentage Utilized: 58
Technology Blog 