Re: Shared Pool Utilization

Ah, I see. What you have to keep in mind is that these messages need to be simplified
for us poor overworked DBA's. We have short attention spans. :)

That script is too complex for what you're trying to do, which is track memory usages.

Throw it away and write one more to your liking, and is also understandable.

Really.

I wouldn't troubleshoot it for my own use, so I'm not too likely to do it for someone else. :)

Maybe someone else here will take a go at it.

Jared

"Corniche Park" <cornichepark_at_cwazy.co.uk> Sent by: ml-errors_at_fatcity.com
 08/27/2003 03:04 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: Shared Pool Utilization

I ran the query given, but that doesnot answer my question. I am asking what is wrong with the script(s)?

> 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;
> --
> --
> -- 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: 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).