You're welcome! Tanel.
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > > 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: [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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
