Re: SGA Size
Date: 1996/09/10
Message-ID: <32350026.62545131_at_news.concentric.net>
On 8 Sep 1996 16:57:11 GMT, Winston Douglas <wdouglas_at_istar.ca> wrote:
>So far I have been setting the SGA size by merely trial & error and
>guessing. Is there a way of calculating exactly how much large my SGA
>should be ?
>
>Winston Douglas
>Apotex Inc.
>Toronto, Canada
Here's a bulletin from Oracle Support on the subject...
Document ID: 106104.414 Title: Shared Pool Calculation Creation Date: 29 October 1993
Last Revision Date: 10 August 1994
Revision Number: 0
Product: RDBMS
Product Version: 7.x
Platform: All
Information Type: Advisory
Impact: High
Abstract: This document describes a method for calculating
the
amount of shared pool resources needed by a
database
instance.
Keywords: SHARED;POOL;SHARED;MEMORY;4031;SQL;AREA
___________________________________________________________________________
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.
Oracle Worldwide Customer
Support
Received on Tue Sep 10 1996 - 00:00:00 CEST