How to improve the Buffer Cache hit ratio ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to improve the Buffer Cache hit ratio ?

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    :D Hi Folks,

    I wounder what are the measures that are needed to be taken improve the Buf. Cache hit ratio, for an instance whose tables and indices are analyzed every day. The current hit ratio for an 33 sessions is 88%.

    I know one of the solutions is to increase the db_block_buffers. But I'm looking for a way to do things without increasing the db_block_buffers. The sessions are read and write intensive.

    The current setup of it is

    DB version : 8.1.6 STD for sun 2.8

    8K block size
    64M shared pool
    100M Buffer cache
    15M Large pool
    20M Java pool

    Max# SESSIONs 250
    MTS setup

    Any suggestions would really be appreciated.

    Thanx,
    Sam

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    Your applications also counts very much.
    Radhakrishnan.M

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The appilication is regular sql nothing fansier. It uses the net8 to interact with the instance.

    I wounder whether there is a way to find out what is causing this low hit ratio. In short, the application that uses the OCI would make connection to the database and would use the database tables to keep track of IMP logins and sessions on the database. When ever a user opens/reads their email on the internet, the program would insert/update or select the data from the table.

    Thats all the program does on the database side. I wonder whether there are any ways to find out where the contention for the Buffer cache lies at.

    Thanx,
    Sam

  4. #4
    Join Date
    Nov 1999
    Posts
    226

    chack this out

    [url]http://www.geocities.com/ageldutes/references/pt_buffer_cache.html[/url]

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thanx for the information puneet and omegamark. But still I'm not satisfied with the 88% performance for an 33 sessions system.

    Does any one aware of any places that I could twiege, to increase the buffer cache hit without increasing the #of db_buffers.

    Thanx,
    Sam

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    trace a session, tkprof the trace file, check the explain plan of statements.
    may be you have many full table scans, or putting useless data in the data buffer that fill the buffer so fast.

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