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:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
