DB_Block-Buffers-help
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: DB_Block-Buffers-help

  1. #1
    Join Date
    Sep 2001
    Posts
    62
    Hi,

    I need to change the db_block_buffers because i have found that the cach hit ratio is far too low.

    I have enough memory available on the unix box.

    My question is should the db_block_buffer be a multiple of db_block_size.

    e.g. my block size is 8192K and my buffer size is 8192K, this gives me about 6mb of space. should i increase buffer size to 16384K(2*db_block_size) as the next figure for db_block_buffer.


    look forward to reading the advice.

    thanks





  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    db_block_buffers is NOT in bytes, it just says how many database buffers are available in the buffer cache ...

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    The integer you specify for db_block_buffer need not be a multiple of db_block_size. If db_block_buffer is 100, then you have 100*8192 bytes of Oracle memory. What is your buffer hit ratio? Is it less than 90%?




  4. #4
    Join Date
    Sep 2001
    Posts
    62
    Hit ratio is currently 70-75 %

    Thanks for your help.

    makes more sense now.

  5. #5
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    To generalize Julian's comments:

    db_buffer size == db_block_buffers * db_block_size

    Even if you have lots of memory to spare, it is not always advisable to allocate more memory to Oracle because you can cause swapping and decrease performance.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    70% ???
    increase db_block_buffer !!!
    try 11-12000 and see how your hit ratio improves ... generally speaking, it should never be lower than 90% (at least)

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    True. If your buffer Hit Ratio is very high (say more than 90%) you might want to decrease a bit DB_BLOCK_BUFFERS by 5% for example and monitor if the Hit Ratio decreases. At some point you get an optimal hit ratio. I personally like 95 :-)

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Try to be propotion with your actual physical memory.
    Your SGA size should be about 1/3 of physical memory.

    Query first the SGA size:
    select * from v$sga;
    or
    select sum(value) from v$sga;

    Remember not to over size your db buffer, your system might
    end up using swap file in which case performance suffer.

    Try to maintain your indexes first before any adjustments you have to make in db_block_buffers. In this case, you are sure that the hit ratio
    you want to achieve is because of db_block_buffers configuration and not indexes issues.


  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by reydp
    Your SGA size should be about 1/3 of physical memory.
    In Oracle 7 that used to be the best possible ratio.
    In Oracle 8 and 9 SGA can be half of the OS memory.

    The goal for the future is to get the complete DB in memory!

    P.S. I keep 2 big tables in memory of all instances on an Oracle Parallel Server. It improved performance a lot. Especially with some reports we run from Node 2. I did increase DB_BLOCK_BUFFER to 50.000.


  10. #10
    Join Date
    Sep 2001
    Posts
    112
    My db_block_buffers is 30,000 and the db_block_size is 2048.

    So I have 61440000 (30,000*2048) bytes reserved for blocks. (approx 58 Megabytes).

    This makes up part of the SGA. You need to know how much physical ram your machine has before you make drastic changes to the SGA.

    I think the SGA is made up primarily of the Buffer Cache ( discussed earlier in my case it was 58 meg), the shared_pool_size, the large_pool_size, the log_buffer. Totalling these gives the SGA, make sure there is enough room for this in ram and then some as there are a few more uses for the SGA that are minimal but must be accounted for.

    I think my total SGA is about 100 Meg.

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