RE: buffer busy waits PUZZLES
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 May 2005 09:49:18 -0400
Just because there is a wait does not mean there is a problem. How many
IO were there to the objects during the same time period? 8032 waits
out of 8032 requests is probably a problem but 8032 waits over 8 million
requests doesn't seem like much of a problem. I do not see how anyone
can give you sound recommendations when you only provide one statistic.
IMHO -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of The Human Fly
Sent: Tuesday, May 10, 2005 9:29 AM
To: K Gopalakrishnan
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: buffer busy waits PUZZLES
Gopal,
Actually, I was digging at v$segment_statistic level. The following
query has been used,
select object_name,statistic_name,value from v$segment_statistics where
statistic_name like '%buffer busy%'
and value > 100
order by value desc
and the output is
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ------------------------------ ----------
C_INTIMATION buffer busy waits 8032
CIS_AUDIT_TRAILH buffer busy waits 3739
TRANSACTION_LOG buffer busy waits 2185
FM_AUDIT_FORM buffer busy waits 2032
C_CUSTOMER buffer busy waits 1768
FM_OLTP_LOG buffer busy waits 1476
RB_RESTRAINTS buffer busy waits 730
RB_TRAN_HIST buffer busy waits 653
FM_ERROR_LOG buffer busy waits 532
I have added freelist as 5 to some of the tables, like
c_intimation,c_transaction_log,c_customer,fm_audit_form
these tables are oltp tables and every transactions entry goes to these
tables. We have around 45 - 55 transaction per second.
Planning to move those tables to a tablespace with ASSM feature.
Any further idea?
On 5/10/05, K Gopalakrishnan <kaygopal@xxxxxxxxx> wrote:
> Jaffar:
>=3D20
> What is the most common reason code (p3=3D3D?) you are noticing? =
Tracing
>the sessions with 10046^8 will give you more information about the =20
>type of the block and the reason code for buffer busy wait..
>=3D20
> (btw the waits are 'buffer busy waits' WAIT or 'buffer busy' WAIT?) =20
>If it is 'buffer busy' WAITs then the issue may not be with freelists.
>=3D20
> --
> Best Regards,
> K Gopalakrishnan
> Co-Author: Oracle Wait Interface, Oracle Press 2004 =20
>http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
>=3D20
--=3D20
Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia
------------------------------------------------------------------------
---=3D
-------------
"It is your atittude, not your aptitude that determins your altitude."
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
