free buffer waits
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: free buffer waits

Hybrid View

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    free buffer waits

    I am troubleshooting the performance issue in one of my warehouse database. There are good number of sessions running, some of them are doing FTS and some of them doing sequential reads.

    I have a 15 minutes interval stats pack setup and please find the top 5 timed wait events.
    Top 5 Timed Events Avg %Total
    ~~~~~~~~~~~~~~~~~~ wait Call
    Event Waits Time (s) (ms) Time
    ----------------------------------------- ------------ ----------- ------ ------
    free buffer waits 375,211 11,867 32 63.2
    db file sequential read 267,049 3,562 13 19.0
    db file scattered read 241,747 2,495 10 13.3
    CPU time 490 2.6
    enq: FB - contention 49 113 2303 .6
    I understood that 'free buffer waits' is an indication that DBWR process is not clearing the buffer catch fast enough.

    There are huge inserts and updates are happening in the DB. What ever it is, DBWR has to clean the dirty buffers which is not happening.

    I am just confused by reading multiple documents posted by different people with different reasons. I want to clear some of my confusions.

    1. Is only DBWR process responsible for this wait event?
    2. while inserting and updating why too many blocks are reading into buffer? is this because of bad Indexes?
    3. Is lack of stats on Indexes cause this?

    A simple query from v$buffer_pool_statistics gives me the below information.

    FBW - FREE_BUFFER_WAIT
    WCW - WRITE_COMPLETE_WAIT
    BBW - BUFFER_BUSY_WAIT

    ID NAME FBW WCW BBW
    ---------- ------------ ------------ ------------------- ----------------
    3 DEFAULT 118914584 630902 348394799
    6 DEFAULT 0 0 0
    Oracle - 10.2.0.4
    SGA Target - 24GB
    IO - Hitachi FC SAN

    And, finally when I query v$sga_resize_ops I see the below output (last few lines)

    db_cache_size 31-JAN-11 22078816256 22062039040 COMPLETE
    shared_pool_size 31-JAN-11 771751936 788529152 COMPLETE
    shared_pool_size 31-JAN-11 788529152 754974720 ERROR
    db_cache_size 31-JAN-11 22062039040 22095593472 ERROR
    no resize operations after 31st Jan and no rows in v$sga_current_resize_ops.

    That means, Oracle is not doing SGA resize operations?

    I am happy to provide if you need any more Information to guide me.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    1. There can be many reasons - basically you are accessing 'certain' blocks 'very frequently'. So, you need to identify what is the object, file, blocks and the SQLs in question. Then understand why there are so many concurrent access to the blocks.

    2. You need to identify the SQLs first and see the plan.

    When you have 22GB buffer cache, it is very difficult to believe that, your DBWR is falling behind. What is the CPU usage?

    These generic problem definition will not help any way. Take one problem at time and start investigate. It looks to me some SQLs playing naughty.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks for the update Thomas.

    I have identified the objects (especially Indexes) which are causing problem. I am working to fix them one by one.

    CPU utilization is maximum 30% I have seen so far. We are ASMM so Oracle manages the memory allocation. Regarding error in v$sga_resize_ops, Oracle addressed it in Metalink ID 556928.1. May be I need to bounce my DB to wake up MMAN again.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Then the problem could be right hand index growth - insert from a Sequences. Hash Index partitoning may help you. Also, make sure you use ASSM ( Automatic Segment Space managemet) to avoid free list contention.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    exactly, the problem I have with Indexes which has sequence generated columns. I am rebuilding the Indexes with more pct_free.

    Yes, I am using ASSM only.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    I afraid, that is not the right solution. PCT_FREE is taken only during the index rebuild, while your issue is right hand "hot blocks" during the normal operation - insert. You should use either Hash Partitioned Index or Reverse Key index. I will not suggest Reverse key index as Range Scan is not supported, so I will go for Hash partitioned Index.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks again Thomas. I will test your suggestion then implement if it has better result than what I am doing currently. Appreciate your suggestions.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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