Diagnosing Free List Contention
The V$SYSTEM_EVENT and V$WAITSTAT dynamic performance views are used to diagnose free list contention problems.
Select class,count,time
From v$waitstat
Where class = ‘segment header’;
Select event,total_waits
From v$system_event
Where event =’buffer busy waits’;
Identify the segment and determine the number of free list that currently exist for the segment identified by querying:
Select s.segment_name,s.segment_type,s.freelists,w.wait_time,w.seconds_in_wait,w.state
from dba_segments s, V$session_wait w
where w.event ='buffer busy waits'
and w.p1 = s.header_file
and w.p2 = s.header_block;
Resolving Free List Contention
In order to change the freelist storage just alters the segment:
Alter table xyz storage( freelists 2);
In oracle9i free list contention can be solved if you move the infected table into a tablespace with space segment management auto
