Identifying free list contention
To identify the percentage of requests that resulted in a wait for a free block run the following query:
select round( (sum(decode(w.class,'free list',count,0)) / (sum(decode(name,'db block gets', value, 0)) + sum(decode(name,'consistent gets', value, 0)))) * 100,2) from v$waitstat w, v$sysstat;
This should be less than 1%. To reduce contention for a table’s free
list the table must be recreated with a larger value in the
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.