Unanswered: Sizing SHARED_POOL in Oracle - Question
I have a question to all. As you know the SHARED_POOL contains the Lirary Cache + Dictionary_Cache.
Now, how do we come to the right sizing of the SHARED_POOL?
I understand that the ideal size of a SHARED_POOL should be such that all the packages/procedures/functions must be able to remain in the library cache and the definition of all the objects must also be able to remain in the SHARED_POOL so that these are cached.
Now take an example of a DB having only only 3 objects :
1. A Function called say, TEST_FUNCTION
CREATE OR REPLACE FUNCTION TEST_FUNCTION (p_codekey NUMBER, p_locale CHAR)
l_retval VARCHAR2 (200);
2. A Table called say, TEST_TABLE
CREATE TABLE TCODE (
CODEKEY NUMBER (10) NOT NULL,
TYPE2TTYPE NUMBER (10) NOT NULL,
CODENAME VARCHAR2 (600) NOT NULL,
CODEDESC VARCHAR2 (4000) NOT NULL,
DEFAULTCODEFLAG CHAR (1) NOT NULL,
ACTIVEFLAG CHAR (1) NOT NULL,
CREATEDDATE DATE DEFAULT SYSDATE NOT NULL,
CREATEDBY2TUSER NUMBER (10) NOT NULL,
UPDATEDDATE DATE DEFAULT SYSDATE NOT NULL,
UPDATEDBY2TUSER NUMBER (10) NOT NULL,
STATUS2TRECORDSTATUS CHAR (1) DEFAULT 'A' NOT NULL)
3. An Index called say, TEST_INDEX
create index TEST_INDEX on TEST_TABLE (CODENAME);
Now, what should be the size of the shared_pool if i have only these objects in my database. This will give an idea as to how to size it.
Here's a rough guideline to sizing the Shared Pool... You will
have to look at objects that are in the pool and those that have been
reloaded in order to decide if you want to pin them ... You will also
need to look at the pool after the database is operational for a while
and see what the hit ratios are ...
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.
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
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.
Max Session Memory * No. of concurrent users
+ Total Shared SQL Area Usage
+ PLSQL Sharable Memory
+ Minimum 30% Free Space
= Minimum Allowable Shared Pool
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';
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;
1 rows selected.
+ 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