RE: Reality check on freelists

Hi Christian,

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.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/

-----Original Message-----
Sent: Wednesday, 14 February 2001 19:15
To: Multiple recipients of list ORACLE-L

Hi Steve,

I am not much open to freelists. I set them if it is really justified. F.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 accesed, I think transaction freelists become a problem. And you could see it with the oradebug dump of processes.

Regards.

> -----Mensaje original-----
> De: Steve Adams [SMTP:steve.adams_at_ixora.com.au]
> Enviado el: lunes 12 de febrero de 2001 18:31
> Para: Multiple recipients of list ORACLE-L
> Asunto: RE: Reality check on freelists
>
> Hi Russell,
>
> No, the number of freelist is best made prime, whereas the number of
> transaction
> slots is normally best allowed to default. Also, please note that there is
> a
> block size based limit on the number of freelists you can have, and 100
> would be
> too many for anything other than a 16K block size. Have you seen the
> script
> "suggest_table_freelists.sql" at
> http://www.ixora.com.au/scripts/cache.htm#suggest_table_freelists on the
> Ixora
> web site? If you match the conditions, it may help you to work out the
> real
> degree of block level concurrency. If not, dump a few blocks towards the
> end of
> the segment and then round up the highest interested transaction slot
> count that
> you see to a prime number, and take that as the number of freelists to
> use.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
>
>
> -----Original Message-----
> Sent: Tuesday, 13 February 2001 2:06
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
> We are working on what will be a one time conversion. One of the tables
> created only for this conversion has around 650 K inserts by around 100
> batch processes. As a reality check, is a freelist of 100 a reasonable
> value, as far as proportion of the block that will be tied up? And as I
> recall from Steve Adams' book, the prime number criteria is for inittrans,
> not freelist?
>
> Cheers,
> Russ Brooks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).