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:
>=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

Other related posts: