| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Shared Pool Utilization
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 0SQL> show parameter dispatch
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string max_dispatchers integer 5 mts_dispatchers string mts_max_dispatchers integer 5SQL> I corrected the script for non-MTS/DTS and got the result. SQL> @swt02a
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).Received on Wed Aug 27 2003 - 17:34:34 CDT
 |
 |