RE: data block waits

Hi Gaja (and list),

There are a few points in your post with which I would like to disagree.

There is no great overhead in having relatively large numbers of process freelists in a segment. However, 400 would not normally be possible because of space limits in the freelist header block. 400 would only just fit in a 16K dedicated freelist header block. If you need to support 400 concurrently inserting sessions, then I would still recommend 400 free lists, but would do it using hash partitioning and a modest number of process freelists per partition.

By default, each process free list has the effect of raising the segment high-water mark by 2.5 blocks on average - worth knowing about as a disincentive to going overboard sure, but hardly enough to stress the I/O subsystem during sequential I/O.

Blocks are not allocated to freelists when an extent is allocated, nor yet when the high-water mark is raised and the blocks formatted. Even though the number of blocks formatted when the high-water mark is raised is a function of the number of process freelist, those block are placed on the master free list in the first instance and only allocated to individual process freelists as required.

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

@

-----Original Message-----
From: Gaja Krishna Vaidyanatha [mailto:gajav_at_yahoo.com] Sent: Thursday, 31 August 2000 11:51
To: Multiple recipients of list ORACLE-L Subject: RE: data block waits

[snip]

For e.g., if you have an environment with 400 concurrent sessions inserting data into a table, creating 400 freelists is unreasonably high. This is because, the potential overhead incurred in tracking and accessing so many freelists when concurrent inserts occur on the system, is very high.

Also, configuring too many freelists, will have the effect of "artificially increasing the high water mark" of a table. An "artificially high" high-water-mark for a table, will cause Oracle to perform more I/O on the table than required for full-table scans, there by imposing an undesired stress on the I/O sub-system.

Realize, that if you have multiple freelists, when an extent gets allocated, the blocks of the allocated extent, get disbursed across the number of freelists for the table. An optimal value of "2 x # of CPUs", is more than adequate on most Received on Thu Aug 31 2000 - 01:17:09 CDT