The V$WAITSTAT table contains block contention statistics. By default, this table is only available to the user SYS and to other users who have SELECT ANY TABLE system privilege, such as SYSTEM. The free list statistic reflects contention for free blocks. Monitor this statistic over a period of time while your application is running with this query:
SELECT class, count
FROM v$waitstat
WHERE class = 'free list';
The result of this query might look like this:
CLASS COUNT
------------------ ----------
free list 459
Compare the number of waits for free blocks with the total number of requests for data over the same period of time. You can monitor the total number of requests for data over a period of time with this query:
SELECT SUM(value)
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets');The output of this query might look like this:
SUM(VALUE)
----------
929530
The information in V$SYSSTAT can also be obtained through SNMP.
If the number of waits for free blocks is greater than 1% of the total number of requests, you should consider adding more free lists to reduce contention.
Re-creating the table may simply involve dropping and creating it again. However, you may want to use one of these means instead: