Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Shared Pool Size

Re: Shared Pool Size

From: <ddf_dba_at_my-deja.com>
Date: Tue, 15 Jun 1999 18:35:58 GMT
Message-ID: <7k66ed$tud$1@nnrp1.deja.com>


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            :            40
rem

rem Total memory for users     :    35,214,080
rem Size of stuff in shared SQL:     5,872,413
rem


rem Base shared pool size : 41,086,493 rem Pool size with 30% free : 53,412,441 rem


rem
rem The final value, 53,412,441, is a calculated value based on the actual shared
rem SQL pool and the total EXPECTED memory usage for the number of users specified
rem based on the ACTUAL memory usage for the user session provided for reference
rem
rem The shared_pool_size parameter should be rounded up to the nearest whole figure,
rem i.e., 53,412,441 should be result in the following entry in the init*.ora file:
rem
rem shared_pool_size = 60000000
rem
rem The database should be restarted with this new parameter from the svrmgrl or sqldba
rem prompt, to view the messages provided by Oracle at startup. If the database won't
rem start with the new shared pool size, UNIX system parameters and actual physical
rem memory should be analyzed and adjustments made accordingly rem

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




select 'Per user memory requirement: ', &pumem pmem from dual;
select 'Number of users : ', &numusers nu from dual;
prompt


select 'Total memory for users : ', &numusers*&pumem tmu from dual;
select 'Size of stuff in shared SQL: ', &spl sss from dual;
prompt


select 'Base shared pool size : ', &size1 s1 from dual;
select 'Pool size with 30% free : ', &size2 s2 from dual;
prompt


spool off

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US