Re: buffer busy waits PUZZLES - is there actually a problem?
- From: Alex Gorbachev <gorbyx@xxxxxxxxx>
- To: sjaffarhussain@xxxxxxxxx
- Date: Thu, 19 May 2005 00:03:18 +0200
See inline.
2005/5/18, The Human Fly <sjaffarhussain@xxxxxxxxx>:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
