Re: buffer busy waits PUZZLES

From: The Human Fly <sjaffarhussain_at_gmail.com>
Date: Tue, 10 May 2005 16:29:07 +0300
Message-ID: <97b7fd2f05051006297d7ee7b0@mail.gmail.com>

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:

>=20

> What is the most common reason code (p3=3D?) you are noticing? Tracing
> the sessions with 10046^8 will give you more information about the
> type of the block and the reason code for buffer busy wait..

>=20

> (btw the waits are 'buffer busy waits' WAIT
> or 'buffer busy' WAIT?) If it is 'buffer busy' WAITs then the issue
> may not be with freelists.

>=20

> --
> Best Regards,
> K Gopalakrishnan
> Co-Author: Oracle Wait Interface, Oracle Press 2004
> http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
>=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

Received on Tue May 10 2005 - 09:33:36 CDT