Subject: Calculating the size of the Shared Pool
Modified: 03 Oct 94 02:05:28
Author: EEHRSAM
___________________________________________________________________________
Shared Pool Calculation
Shared pool is the amount of fixed, preallocated space in the SGA for
use by multi-threaded server session PGA, shared SQL area, and other
small, dynamically allocated SGA data structures.
Shared Pool
===========
Since shared pool usage is highly application dependent, it is necessary
to examine each database application individually in order to project a
recommended shared pool value.
While analyzing shared pool sizing, it is helpful to first increase the
shared pool to a very large value, so that the dynamically allocated SGA
structures may be allowed to expand to a desirable size. Once this sizing
exercise has been completed, the shared pool may be downsized to the
appropriate value.
Shared pool calculation is especially critical when the multi-threaded
server is in use because the PGA for each multi-threaded server database
user will be allocated from shared pool.
Formula
-------
Max Session Memory * No. of concurrent users
+ Total Shared SQL Area Usage
+ PLSQL Sharable Memory
+ Minimum 30% Free Space
----------------------------------------------
= Minimum Allowable Shared Pool
Example
-------
Find the SID for an example user session:
SQLDBA> select sid from v$process p, v$session s
2> where p.addr=s.paddr and s.username='OPS$JSMITH';
SID
----------
29
1 rows selected.
Get the maximum session memory for this session:
SQLDBA> select value from v$sesstat s, v$statname n
2> where s.statistic# = n.statistic#
3> and n.name = 'session uga memory max'
4> and sid=29;
VALUE
----------
273877
1 rows selected.
Get the total shared SQL area:
SQLDBA> select sum(sharable_mem) from v$sqlarea;
SUM(SHARAB
----------
8936625
1 row selected.
Get the PLSQL sharable memory area:
SQLDBA> select sum(sharable_mem) from v$db_object_cache;
SUM(SHARAB
----------
4823537
1 row selected.
Example shared pool calculation:
274K shared memory * 400 users
+ 9M Shared SQL Area
+ 5M PLSQL Sharable Memory
+ 60M Free Space (30%)
---------------------------------
= 184M Shared Pool
In this example, the recommended shared pool value is 184M.
Out of Shared Memory (Error ORA-4031)
-------------------------------------
The 2 most common causes of the error "Out of shared memory" are lack of
available shared pool and lack of available contiguous shared pool into
which to map large PL/SQL packages. In order to avoid the latter, it is
recommended that the application DBA pin all large packages using the
sys.dbms_shared_pool.keep procedure.