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