From:Steve Adams
Date:15-Feb-2001 10:50
Subject:   Process freelists v. transaction freelists

Yes, theoretically it is possible for a delete or update transaction to need to return a block to a transaction free list and find that they are all in use by active transactions. If so that process will select a transaction freelist pseudo-randomly and wait for an S lock on the TX enqueue for that transaction. You can set up a test to demonstrate this easily enough.

However, given the cautious maximums that Oracle enforces for process freelists, I don't think that there is much risk of this happening in real life, except as a secondary problem. And if it were to happen, it can be tuned/managed dynamically by lowering the PCTUSED value for the table. Therefore, the inverse relationship between the number of process and transaction freelists ought not dissuade you from using an appropriate number of process freelists on insert intensive tables.

I am not much open to freelists. I set them if it is really justified. I.e. in OPS. I said that because of the transaction freelists. I mean when you have an application that used to inserting and deleting and updating a lot with batches and also it is heavy accessed, I think transaction freelists become a problem. And you could see it with the oradebug dump of processes.