RE: buffer busy waits PUZZLES

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 10 May 2005 09:49:18 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF41050395B8B6@usahm236.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of The Human Fly Sent: Tuesday, May 10, 2005 9:29 AM
To: K Gopalakrishnan
Cc: oracle-l_at_freelists.org
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_at_gmail.com> 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."

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

Received on Tue May 10 2005 - 09:54:35 CDT