Some of the others have given you good advice about isolating your problem
sessions to see if the buffer busy waits are related to the sessions timing
out. I'd like to add a couple points to what they've said.
It has been mentioned that you are only listing number of buffer busy waits,
not time waited. Whether you're looking at an individual session or
systemwide stats, the number of waits is not very important (though, at a
query level, it can sometimes help with diagnosis). What is important is
the amount of time spent waiting. For example if the total amount of time
your system has spent on buffer busy waits is 100 seconds, and you've had
hundreds of transactions timing out after waiting 60 seconds each, then you
can figure that the buffer busy waits aren't your big problem. Time waited
is what you need to be measuring.
It has also been mentioned, but you don't seem to have addressed it- you
need to determine the reason code of the buffer busy wait (the P3 value in
v$session_wait). There are about 10 different reason codes (see Metalink
doc #34405.1) and they point to different causes of the buffer busy wait.
While sometimes a buffer busy wait can due to a freelist problem (though
I've never come across that as the problem in a production system), there
are several other causes which have nothing to do with freelists. If you
find that buffer busy waits are a significant problem (in time waited), then
find out the reason code and what it represents.
--Terry
- Original Message -----
From: "The Human Fly" <sjaffarhussain_at_gmail.com>
To: "Mark W. Farnham" <mwf_at_rsiz.com>
Cc: "Oracle-L Freelists" <oracle-l_at_freelists.org>
Sent: Wednesday, May 18, 2005 5:27 AM
Subject: Re: buffer busy waits PUZZLES - is there actually a problem?
First of all thnks for your valuable time and suggestions.
However, there is no change in our transaction rate, they are almost
the same value at any given time.
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.
The tables which I increased the freelist are the one who are
referring almost in every transaction.
Server configuration, 9gb of RAM with 5cpu, AIX 5m on D240 storage.
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.
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.
On 5/18/05, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> Did your transactional throughput go up, down, or not change much?
>=20
> Asked another way, are these higher waits insignificant side effects of
> un-jambing some other problem?
>=20
> It is not possible to know without the times, and even with the times, we
> cannot know whether this is a problem or simply a report of a change in
> statistics unless you tells us whether throughput is now changed, and how=
.
>=20
> Asked yet another way, is some process materially slower than you need it=
to
> be due to the amount of TIME spent waiting for these waits.
>=20
> Abstract attempts to change the values of statistics reports from running
> systems without correlation to a process or task that is running slower t=
han
> required have little (little includes nothing) to do with making things
> actually process faster.
>=20
> Collection and observation of some statistics without correlation to prob=
lem
> processes can be useful, but that is part of capacity planning and load
> management, not optimization. Even then (unless you're using systems
> statistics as a proxy for company business growth in an attempt to predic=
t
> stock outcomes or something like that) you need to correlate the statisti=
cs
> you observe to known scaling limits of your existing environment.
>=20
> So, are you trying to fix some process that is going slower than you thin=
k
> it should (or more appropriately, slower than your service level
> requirement),
> or are you just trying to manipulate the statistics?
>=20
> Now I will not deprecate that activity if you're doing it as a learning
> exercise or even if you're just servicing your CTD. But I would like to k=
now
> if you have a throughput problem you're trying to solve (or not).
>=20
> To answer your question, if changes you made ungated pent up demand for
> these tables, sure, you could have driven the load on the tables higher. =
If
> your throughput went up, then the higher frequency of wait events is a ha=
ppy
> side effect. If your throughput went down, the times and a correlation wi=
th
> the now slower process(es) *might* show that the increase in waits
> represents a problem to solve.
>=20
> Regards,
>=20
> mwf
>=20
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of The Human Fly
> Sent: Wednesday, May 18, 2005 2:11 AM
> To: gorbyx_at_gmail.com
> Cc: Cary.Millsap_at_hotsos.com; Oracle-L Freelists
> Subject: Re: buffer busy waits PUZZLES
>=20
> However, I haven't have buffer busy wait as my tops events nor I see
> any session execessively waiting for this event. I have already
> mentioned in my previous replies that I have grabbed these values from
> v$segment_statistics dynamic view.
>=20
> The following are today's result for the buffer busy waits.
>=20
> TRANSACTION_LOG 211143
> FM_AUDIT_FORM 39906
> C_INTIMATION 37029
> CIS_AUDIT_TRAILH 15001
> OUTWARD_CLEARING_CHEQUES 8310
> C_CUSTOMER 5793
> FM_OLTP_LOG 2727
> INWARD_CLEARING_CHEQUES 2347
> RB_RESTRAINTS 2239
> SYSTEM_PARAMETER_VALUE 1492
> RB_TRAN_HIST 1102
>=20
> I have increased default freelist 1 to 5 to those tables and now I can
> clearly see that those tables buffer busy wait event has increased
> madly. This could be due to heavy access on those tables?
>=20
> However, I have been reading Cary's book past 1 year and still I
> enjoying reading it. Thank god, now, I started undertanding the
> concept behind method R and the true power of 10046 trace for
> resolving performance issues.
>=20
> On 5/18/05, Alex Gorbachev <gorbyx_at_gmail.com> wrote:
> > My bad...
> >=3D20
> > 2005/5/17, Cary Millsap <Cary.Millsap_at_hotsos.com>:
> > > Ah! Credit to Gaja and Kirti for that one...
> > >=3D3D20
> > > Cary Millsap
> > > Hotsos Enterprises, Ltd.
> > > http://www.hotsos.com
> > > * Nullius in verba *
> > >=3D3D20
> > > Visit www.hotsos.com for curriculum and schedule details...
> > >=3D3D20
> > >=3D3D20
> > > -----Original Message-----
> > > From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists=
.o=3D
> rg=3D3D
> > ]
> > > On Behalf Of Alex Gorbachev
> > > Sent: Tuesday, May 17, 2005 2:42 PM
> > > To: sjaffarhussain_at_gmail.com
> > > Cc: K Gopalakrishnan; oracle-l_at_freelists.org
> > > Subject: Re: buffer busy waits PUZZLES
> > >=3D3D20
> > > sessions/transactions than you you've got CTD ("Compulsive Tuning
> > > Disorder") speaking Carry's language.
> >=3D20
> > --=3D3D20
> > Best regards,
> > Alex Gorbachev
> > --
> > http://www.freelists.org/webpage/oracle-l
> >=3D20
>=20
> --=3D20
> Best Regards,
> Jaffar, OCP DBA
> Banque Saudi Fransi
> Saudi Arabia
> -------------------------------------------------------------------------=
--=3D
> -------------
> "It is your atittude, not your aptitude that determins your altitude."
> --
> http://www.freelists.org/webpage/oracle-l
>=20
>=20
--=20
Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia
---------------------------------------------------------------------------=
"It is your atittude, not your aptitude that determins your altitude."
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 18 2005 - 23:15:07 CDT
