Re: buffer busy waits PUZZLES - is there actually a problem?

See inline.

2005/5/18, The Human Fly <sjaffarhussain_at_gmail.com>:

> However, I am not doing it for any testing or learning purpose. We are
> facing some transactions time-out in our production database uding
> texudo middleware.

What I would do is to isolate these "some" transactions in terms of time and Oracle session (and/or application binaries, transaction type).
It's probably not very difficult to narrow down to the certain application binary and/or transaction type. If timeouts happen on different transactions and/or Tuxedo binaries than you would probably want to concentrate on the most critical ones and most impacted. With timeframe it might be tricky... You might be tempted to seek for some correlation with other events or with some storage definitions, schema objects, etc. (like in this case). However, in my experiance I have been most of the time much better off tracing correct session with 10046 at the right time. Sometimes, in order to get to the "right time", I had to enable 10046 for significant time and then cut only very tiny part from gigs of traces (based on tim values and timeouts that we observed on application tier).
The best thing about it is that often problem is identified to be extenal to your database saving hours and days of valuable time trying to tune a well operating component.

> The tables which I increased the freelist are the one who are
> referring almost in every transaction.

Think about it - how would it help you with _SOME_ timeouts?

> I dont see 'buffer busy wait' as top 5 timed events in any statspack
> report but very seldomly I can see them in v$session_wait.

Again, chances are that you will not see your problem in Statspack reports at all! If I understand you correclty, the system is MOSTLY ok but SOME transactions experience timeouts. Solution is to limit scope of your statistic correctly (time/sessions/transactions).

> My thinking was, all these tables have only 1 freelist (default
> value), when they are involving heavy concurent insertion, it would be
> a good idea to play with its freelist.

If you get through Carry's book again, you'll find examples how actions of this kind can give you even more timeouts (real service impact and not just increase/decrease of buffer busy waits).

--=20
Best regards,
Alex Gorbachev

--
http://www.freelists.org/webpage/oracle-l