DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Buffer cache hit ratio low

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    If the table is not frequently updated and inserted, keeping the data in BUFFER POOL KEEP is the right choice. Please remember you need to have more memory, because DB_BLOCK_LRU_LATCH needs memory to maintain the KEEP buffer cache.

    RECYCLE is another place when the table is very rarely updated or inserted.

    If you are in shortage of memory, ALTER the table with CACHE parameter. This will keep data buffer in MRU of LRU list.

    Today memory board is not costly, buy as much memory as you can. That will help you in the long run.

  2. #12
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Talisman,

    I was not aware of the memory requirements for the LRU latch, is the amount of memory significant, or does that depend on, as you were saying, the amount of updates going on in the table? I have a couple of small lookup tables that very rarely change that I have set to cache and put them in the keep pool. Each time I bring the database down I run a script that does a select * from them to push them up into the cache.

    Would you happen to know how to determine how many lru latches that you need for each pool or should I just monitor the v$ tables and see how affective they are? I know you need to tune them but I was wondering how one determines the initial setting of them.

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Each LRU latch requires a minimum of 50 Oracle data blocks.

    To figure out how many LRU latches are set up in the database, run the following SQL:

    SELECT ID, NAME, SET_COUNT, BUFFERS, LO_BNUM, HI_BNUM
    FROM V$BUFFER_POOL;

    The column, SET_COUNT refers to the number of LRU Latch sets available to each buffer pool.

  4. #14
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Any suggestions on how to best determine how many latches to assign to a particular pool up front or do you just need to watch the sleeps and spins and tune accordingly?
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

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