SHARED_POOL_SIZE and DB_BLOCK_BUFFERS sizing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SHARED_POOL_SIZE and DB_BLOCK_BUFFERS sizing

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Smile

    Hi,

    Can somebody confirm the forumla for sizing SHARED_POOL_SIZE?

    select sum(sharable_mem) from v$db_object_cache;

    select sum(sharable_mem) from v$sqlarea;

    select sum(250 * users_opening) from v$sqlarea;

    Sum of all above sums...

    In my database, sum comes to 525 MB. We are running Oracle Financials apps. I have pinned all packages. Still, I wonder if it can be so big.

    I do not know if there is such formula for DB_BLOCK_BUFFERS too.

    Does any body know?

    Thanks in Adv.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    If you are using 8i do show sga from a dql window. Buffer Blocks are calculated by taking db_block_buffers * db_block_size.

    In this case it would be 1000 * 8192 = 8,192,000 or 8 Megs.

    db_block_buffers = 1000
    db_block_size = 8192

  3. #3
    Join Date
    Dec 2001
    Posts
    221
    You have to monitor it continously. At the end or after some working hours calculate the HIT RATIO for SHARED POOL (data dictionary & library cache) as well as DATABASE BUFFER CACHE. it should not be less that approx 96% .

    if it is, then you have to increase the SHARED_POOL_SIZE or DB_BLOCK_BUFFERS size accordingly and again check the HIT RATIO.

    Santosh Jadhav
    8i OCP DBA

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi both,

    I think either I have not explained my question properly or it is not understood properly.

    I do not want to know how to calculate DB_BLOCK_BUFFERS. I know it.

    I simply want to know if the forumla for sizing SHARED_POOL_SIZE

    select sum(sharable_mem) from v$db_object_cache;

    select sum(sharable_mem) from v$sqlarea;

    select sum(250 * users_opening) from v$sqlarea;

    Sum of all above sums...

    is correct or not....

    And similarly if there is any forumla for sizing (not calculating) the DB_BLOCK_BUFFERS.


    Thanks.
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    SQL> select pool, sum(bytes) from V$SGASTAT group by pool;

    POOL SUM(BYTES)
    ----------- ----------
    java pool 24002560
    large pool 2000000
    shared pool 65806948
    ...
    ...

    This is current size of pools.

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Angry

    Originally posted by Shestakov
    SQL> select pool, sum(bytes) from V$SGASTAT group by pool;

    POOL SUM(BYTES)
    ----------- ----------
    java pool 24002560
    large pool 2000000
    shared pool 65806948
    ...
    ...

    This is current size of pools.
    Hi,

    It seems I am not able to explain my question properly.

    For an old running database, how to determine the size of SHARED_POOL_SIZE and DB_BLOCK_BUFFERS.

    For SHARED_POOL_SIZE, I follow the formula, which I have posted here.

    For DB_BLOCK_BUFFERS, I don't know.



    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Ur formula not correct.

    1) shared pool has free memory (usually)
    2) not all part of shared pool u descride in this formula.
    3) simplest way to get pool's sizes --> use v$parameter view.
    in this case u can use query (for old versions of oracle):

    select to_number(nvl(value,'0')) shared_mem
    from v$system_parameter
    where name = 'shared_pool_size';

    select to_number(b.value)*to_number(a.value) db_buffer_size
    from v$system_parameter a,
    v$system_parameter b,
    where a.name = 'db_block_buffers' and
    b.name = 'db_block_size';

    4) stucture of shared memory:
    free memory 10628912
    miscellaneous 805148
    db_block_buffers 4080000 (in shared pool)
    State objects 183744
    db_files 72496
    dictionary cache 2522900
    library cache 16230044
    sql area 25545532
    processes 115200
    sessions 359040
    heap 1802412
    event statistics per sess 576640
    locks 86768

    !! And there are not all parts of shared pool.

Posting Permissions

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


Click Here to Expand Forum to Full Width