buffer busy waits

[Scunningham99]

Hi

Before I make some changes I would like to know the thoughts of other dba's out there in the workplace!

We have an oltp 9i database which has been up just over 3 weeks now. I have noticed an extremely high number of bufer busy waits in v$segment_statistics:-

heres the top contender:-
SQLWKS> select owner, object_name, value, statistic_name
2> from v$segment_statistics
3> where statistic_name = 'buffer busy waits'
4> order by value desc
5>
OWNER OBJECT_NAME VALUE STATISTIC_NAME
------------------------------ ------------------------------ ---------- ----------------------------------------------------------------
PREMIER P_LEAD_ADDRESS 12208639 buffer busy waits


Now i intend to set the freelists to 4 and initrans to 4 for this table and monitor the number of waits in a week to see if they are not increasing as drematically.

My Question is ... should i keep on altering these values untill the number of buffer busy waits has dramatically reduced or stopped increasing for this table.

Thanks in Advance!!!

ps i am aware of segment space management auto which uses smon to superceed freelists and pctused, however for reasons we do not wish to use this!

Sy UK

 

[Scunningham99]

anyone any ideas on THIS?????

Sy UK

 

[Scunningham99]

i have tested this myself and it works a treat... only issue i had was aquiring lock on alter table statements!!!

as object is heavily used!!!!

Sy UK