RE: impact of freelists on inserting ?

Hi Ted,

Multiple process freelist have a minimal impact on table storage requirements. On average each process freelist will have half the number of blocks specified by the _bump_highwater_mark_count, which defaults to 5 blocks. That is, the difference between 2 freelists and 10 freelists in terms of storage space is likely to be just 20 more blocks below the highwater mark.

The freelist headers themselves have no storage requirements, as they go into the freelist group block or segment header block. Having a high number of process freelists does limit the space in that block for the dynamic creation of transaction freelists to which blocks that fall below PCTUSED may be returned. However, there is a database block size based minimum to prevent the creation of too many process freelists. That minimum can be seen with

    select kviidsc, kviival from x$kvii where kviitag = 'ktsmtf';

That largely answers your main question, but does not help you to resolve your 'data block' class buffer busy waits. To make progress on this issue, you need to catch the p3 value for the 'buffer busy waits' events, preferably by enabling event 10046, level 8 in an affected process and examining the trace file. The trace_waits.sql script on my web site may help.

If p3 is 0, it indicates that a process waited for a consistent read buffer, because the buffer was in READ state. If p3 is 1014, the process needed the buffer in current mode but found it in READ state. In either case, this should be addressed by ensuring that the segment concerned is in the KEEP buffer pool. The segment can be identified by looking up the extent containing the relevant file (p1) and block (p2) numbers in dba_extents.

Otherwise, p3 is likely to be 1012. If you are doing discrete transactions, then you are receiving due recompense for your sins. Otherwise, find the segment name and type, and if it is a table either increase process freelists if it is insert intensive, or reduce row density per block otherwise. If it is an index, consider making it a reverse key index if the inserted keys are monotonically increasing.

If it is none of the above, then you have an unusual situation that you may need to get a performance specialist to look at.

Hope this helps,
Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/





-----Original Message-----


From:	tedchyn_at_yahoo.com [SMTP:tedchyn_at_yahoo.com]
Sent:	Friday, October 22, 1999 9:15 AM
To:	comp.databases.oracle.server_at_list.deja.com
Subject:	Q: impact of freelists on inserting ?

Sir, I have following waitstat in one heavy inserting instance. when I increase dbwr from 1 to 4. waitstat number did not change very much. I found out freelists on table was setup to 10. generally you set freelists to > 1 (may be 2) for heavy inserting table. my question is what kind negative impact high freelists has on a table aside from more storage requirements ?

thanks in advance Ted

CLASS                   COUNT       TIME


------------------ ---------- ----------


data block             350262     231895
sort block                  0          0
save undo block             0          0
segment header           5701      16139

 Sent via Deja.com http://www.deja.com/  Before you buy. Received on Mon Oct 25 1999 - 07:05:30 CDT