| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Shared Pool Utilization
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" <cornichepark_at_cwazy.co.uk>
Sent by: ml-errors_at_fatcity.com
08/27/2003 12:44 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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;
-- --Received on Wed Aug 27 2003 - 16:34:34 CDT
-- 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: cornichepark_at_cwazy.co.uk 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: ListGuru_at_fatcity.com (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com 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: ListGuru_at_fatcity.com (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).
 |
 |