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