free buffer waits

[vnktummala]

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,

[Thomasps]

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.

[vnktummala]

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,

[Thomasps]

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.

[vnktummala]

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,

[Thomasps]

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.

[vnktummala]

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,