I have two databases, one in which the shared_pool utilization is not
exceeded (development) and the other one in which it appears that the
shared_pool utilization is exceeded.
The queries i am using are
1. COL Free_Mem FORMAT 999999.99 heading 'Free|Mem|(Mb)'
SELECT pool, name, bytes/(1024*1024) Free_Mem FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool'
/
2. Metalink note
SGA/Shared Pool Utilization - Metalink Note : 105004.1 (modified for
bytes--> Mb and nvl clauses)
set serveroutput on size 1000000;
DECLARE
object_mem NUMBER;
shared_sql NUMBER;
cursor_mem NUMBER;
mts_mem NUMBER;
used_pool_size NUMBER;
free_mem NUMBER;
pool_size VARCHAR2(512); -- same AS V$PARAMETER.VALUE
BEGIN
-- Stored objects (PACKAGEs, views)
--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.
--http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch346.htm#1113348
SELECT SUM(NVL(sharable_mem,0)) INTO object_mem FROM v$db_object_cache;
--
--
-- User Cursor Usage -- run this during peak usage.
-- assumes 250 bytes per open cursor, FOR each concurrent user.
-- V$SQLAREA lists statistics on shared SQL area and contains one
row per SQL string.
-- It provides statistics on SQL statements that are in memory,
parsed, and ready for execution.
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
-- during peak period). Alternatively calculate FOR a single user and
-- multiply by # users.
SELECT SUM(NVL(value,0)) 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 NVL(bytes,0) INTO free_mem FROM v$sgastat
WHERE name = 'free memory' AND pool='shared pool';
-- For non-MTS add up object, shared sql, cursors AND 30% overhead.
--used_pool_size := ROUND(1.3*(object_mem+cursor_mem));
-- For MTS mts contribution needs to be included (comment OUT previous
line)
used_pool_size :=
ROUND(1.3*(object_mem+NVL(shared_sql,0)+cursor_mem+mts_mem));
SELECT NVL(value,0) INTO pool_size FROM v$parameter WHERE
name='shared_pool_size';
-- Display results
DBMS_OUTPUT.PUT_LINE ('Object mem :
'||TO_CHAR(ROUND(object_mem/(1024*1024),2)) || ' Mb');
DBMS_OUTPUT.PUT_LINE ('Cursors :
'||TO_CHAR(ROUND(cursor_mem/(1024*1024),2)) || ' Mb');
DBMS_OUTPUT.PUT_LINE ('MTS session:
'||TO_CHAR(ROUND(mts_mem/(1024*1024),2)) || ' Mb');
DBMS_OUTPUT.PUT_LINE ('Free memory:
'||TO_CHAR(ROUND(free_mem/(1024*1024),2)) || ' Mb ' ||
'('|| 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;
/
The dev instance shows properly (Free Mem seems to be tallying)
(F) Approx = (B) - (A)
Free
Mem
POOL NAME (Mb)
----------- -------------------------- ----------
shared pool free memory 18.27 (F)
and
Object mem : 10.5 Mb
Cursors : .07 Mb
MTS session: 9.47 Mb
Free memory: 18.29 Mb (18.29MB) (F2) for Shared Pool
Shared pool utilization (total): 27316566 bytes (26.05MB) (A)
Shared pool allocation (actual): 50331648bytes (48MB) (B)
Percentage Utilized: 54%
BUT in Prodn it does not seem to be the case.
Free
Mem
POOL NAME (Mb)
----------- -------------------------- ----------
shared pool free memory 120.17 (F)
Object mem : 59.43 Mb
Cursors : 2.14 Mb
MTS session: 302.59 Mb
Free memory: 120.13 Mb (120.13MB) (F2) for Shared Pool
Shared pool utilization (total): 496410073 bytes (473.41MB) (A)
Shared pool allocation (actual): 201326592bytes (192MB) (B)
Percentage Utilized: 247%
PL/SQL procedure successfully completed.
I understand F2 and F are tallying. But how can one say that
there exists some free memory in Shared Pool when the Utilization
seems to have exceed the actual allocation.
Where is the mismatch?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Corniche Park
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
