Re: Shared Pool Size

I had a go at using your 'script'

I had two problems

First I got errors if I selected a USERNAME that did not have the Max value - so I amended the relevant select to the following

        col a new_value snum

        select max(m.value) 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')

        REM select m.sid a
        REM from v$process p, v$session s, maxmem m
        REM where s.sid = m.sid
        REM and p.addr = s.paddr
        REM and s.username = upper('&username')
        REM and m.value = (select max(value)
        REM       from maxmem);

Secondly when used in SQLPlus 8.0 as a @file to be run the script would not recognize the variables in each following select. For instance 'Col A new_value SNUM' the SNUM was not recongnized in the appropriate select as follows.

        select value b
        from v$sesstat
        where statistic# = 16
        and sid = &snum;

Is this a 'Feature' of the @file use or what might it be.

Regards
--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

                        <KevinALewis_at_HotMail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. <ddf_dba_at_my-deja.com> wrote in message news:7k66ed$tud$1_at_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 Wed Jun 16 1999 - 11:18:48 CDT