Re: Shared Pool Utilization

So it was the MTS. Hadn't thought of that.

Jared

"Tanel Poder" <tanel.poder.003_at_mail.ee>
Sent by: ml-errors_at_fatcity.com
 08/27/2003 03:49 PM
 Please respond to ORACLE-L  

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

You're welcome!

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, August 28, 2003 1:34 AM

> Tanel
> You were on target!!!!
>
> SQL> show parameter shared_serv
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> max_shared_servers integer 20
> shared_server_sessions integer 0
> shared_servers integer 0
> SQL> show parameter dispatch
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> dispatchers string
> max_dispatchers integer 5
> mts_dispatchers string
> mts_max_dispatchers integer 5
> SQL>
>
> I corrected the script for non-MTS/DTS and got the result.
> SQL> @swt02a
> Object mem : 67.73 Mb
> Cursors : 3.02 Mb
> MTS session/UGA memory max: 433.93 Mb
> Free memory: 105.98 Mb (105.98MB)
> Shared pool utilization (total): 96440007 bytes (91.97MB)
> Shared pool allocation (actual): 201326592bytes (192MB)
> Percentage Utilized: 48%
>
> PL/SQL procedure successfully completed.
>
> Thanks
>
>
> > Hi!
> >
> > I wonder whether you are running in dedicated server mode? That way
UGA
> > isn't allocated from SGA and the calculation
used_pool/shared_pool_size
is
> > gives wrong results.
> >
> > Tanel.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, August 27, 2003 10:44 PM
> >
> >
> >> 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/a96
> > 536/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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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