Peter, here is a script you might want to try. I got this from somewhere else (maybe metalink?, I think Steve has a similar/better one on ixora too):
set echo off
spool pool_est
/*
- *
- TITLE : Shared Pool Estimation *
- CATEGORY : Information, Utility *
- SUBJECT AREA : Shared Pool *
- DESCRIPTION : Estimates shared pool utilization *
- based on current database usage. This should be *
- run during peak operation, after all stored *
- objects i.e. packages, views have been loaded. *
- *
- *
********************************************************/
Rem If running MTS uncomment the mts calculation and output
Rem commands.
set serveroutput on;
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)
select sum(sharable_mem) into object_mem from v$db_object_cache;
- Shared SQL -- need to have additional memory if dynamic SQL used
select sum(sharable_mem) into shared_sql from v$sqlarea;
- User Cursor Usage -- run this during peak usage.
- assumes 250 bytes per open cursor, for each concurrent user.
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(value) 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 bytes into free_mem from v$sgastat
where name = 'free memory';
- For non-MTS add up object, shared sql, cursors and 20% overhead.
used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem));
- For MTS mts contribution needs to be included (comment out previous line)
- used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
select value into pool_size from v$parameter where name='shared_pool_size';
- Display results
dbms_output.put_line ('Obj mem: '||to_char (object_mem) || ' bytes');
dbms_output.put_line ('Shared sql: '||to_char (shared_sql) || ' bytes');
dbms_output.put_line ('Cursors: '||to_char (cursor_mem) || ' bytes');
- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
dbms_output.put_line ('Free memory: '||to_char (free_mem) || ' bytes ' || '('
|| 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;
/
spool off
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Schauss, Peter
Sent: Wednesday, July 13, 2005 10:45 AM
To: oracle-l_at_freelists.org
Subject: Which packages to pin
Oracle 8.1.7.4 - Aix 5.2.
As a cure for the ORA-4031 errors which we are getting (up to 5 per
day), I am looking at the possibility of pinning some of my stored
procedures and triggers. Looking at the Toad Pinned Code screen
combined with what I know about the application gives me a good idea of
what parts of the application code should be pinned, but I do have some
questions:
- Are triggers normally candidates for pinning? (Toad lists them on
its screen, so I infer that they are.)
- Should I also pin some of the system packages which are displayed on
the Toad Pinned Code screen? (e.g. DBMS_OUTPUT, STANDARD,
DBMS_STANDARD, DBMS_SQL...)
- Which of the dynamic views (V$... ) lists the packages, stored
procedures, and triggers currently in the shared pool?
Thanks,
Peter Schauss
--
http://www.freelists.org/webpage/oracle-l
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 13 2005 - 13:02:29 CDT
