Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Shared Pool Size
Kurt,
That would depend upon when you ran your query. Is this during peak levels of database activity, or is it during a slow part of the day? I would suggest running the following query to determine the shared_pool_size you need for your instance:
rem shared_pool.sql
rem
rem Estimates the required SGA for the current database instance
rem
rem Estimates include a 30% increase in calculated values
rem Ask for a user name and the number of concurrent logins
rem
rem To obtain a fairly "padded" value, select a user who uses a large
rem allocation of memory, and is currently occupying one user session;
rem this can, however, backfire, as the SGA calculated could well exceed
rem the total memory of the system
rem
rem Example
rem
rem User : ims
rem # of users: 40
rem
rem Tuesday , January 13, 1998 10:38:47
rem
rem
rem
rem
rem Per user memory requirement: 880,352 rem Number of users : 40rem
rem Total memory for users : 35,214,080 rem Size of stuff in shared SQL: 5,872,413rem
accept username prompt 'User : '
accept numusers prompt '# of users: '
rem Get the users SID
set term off
col a new_value snum
create table maxmem
tablespace tools
as select sid, max(value) value
from v$sesstat
where statistic# = 16
group by sid;
select m.sid a
from v$process p, v$session s, maxmem m
where s.sid = m.sid
and p.addr = s.paddr and s.username = upper('&username') and m.value = (select max(value) from maxmem);
rem With the SID in hand, we can now obtain
rem the memory allocation for this user from
rem v$sesstat with a STATISTIC# = 16
rem which is the MAX SESSION MEMORY per user
col b new_value pumem
select value b
from v$sesstat
where statistic# = 16
and sid = &snum;
rem Get the amount of memory in the shared pool rem currently in use
col c new_value spl
select sum(sharable_mem) c
from v$sqlarea;
rem Calculate optimal shared pool size
rem
rem optimal size = 1.3 * ((per_user_memory * # of users) + size of sql
in pool)
col d new_value size1
col e new_value size2
select ((&pumem*&numusers)+&spl) d,
((&pumem*&numusers)+&spl) + 3/10 *((&pumem * &numusers) + &spl) e from dual;
rem Output results
rem
rem Also spool to a report file
col pmem form 999,999,990
col nu like pmem
col sss like pmem
col tmu like pmem
col s1 like pmem
col s2 like pmem
set term on
set echo off verify off feedback off pagesize 0
spool $ORACLE_HOME/tuning/reports/shared_pool.rpt
select to_char(sysdate, 'Day, Month DD, IYYY HH24:MI:SS')
from dual;
prompt
prompt
prompt
prompt
drop table maxmem;
exit
This will give you a shared_pool_size you can live with based upon current resource consumption.
David Fitzjarrell
In article <37667D3E.69BC56F9_at_bcsew.edu>,
Kurt Troyer <katroyer_at_bcsew.edu> wrote:
> I have run a query I found in a tuning book to review the amount of
> memory left for Shared Pool Size. The results are below. Given that
I
> am not even using half of the size that I allocated, would I be better
> off reducing the size of the shared pool by say 10 meg and
reallocating
> that amount to buffer cache?
>
> Shared Pool Size Free Bytes Percent Free
> ---------------- ------------ ------------
> 30,000,000 19,520,736 65.06912
>
> Thanks,
>
> Kurt Troyer
>
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Tue Jun 15 1999 - 13:35:58 CDT