Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Question Unanswered: Sizing SHARED_POOL in Oracle - Question

    Hi all,

    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)
    RETURN VARCHAR2
    IS
    l_retval VARCHAR2 (200);
    BEGIN
    ......
    ......

    END



    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.


    Regards
    Harry.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    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 ...

    HTH
    Gregg

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •