I don't trust scripts I didn't write myself, and not even some of those. ;)
What does this reveal?
select a.value + ( b.value * c.value )
from
( select sum(value) value from v$parameter
where name like '%pool%'
) a
, v$parameter b
, v$parameter c
where b.name = 'db_block_buffers'
and c.name = 'db_block_size'
As long as you're not using some of the newer 9i memory allocation parameters, this
should be pretty close to the amount of memory you've allocated in init.ora.
Now see how much is in the SGA:
select sum(value) from v$sga;
Should be fairly close to the first number.
Jared
| "Corniche Park" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 08/27/2003 12:44 PM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Shared Pool Utilization |
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).
