RE: Altering Freelist Groups in 10gR2 ?

Mladen, Jonathan

Also faced a Similar issue at a High End Benchmark with 10gR2 touching about 20,000 TPS. Respective Tables undergoing INSERTs had to be moved from ASSM to NON-Assm Tablespaces.

Jonathan - Your assumption is Correct. Database currently exists in 9.2 & most objects have freelists = 99. DB also needs to be migrated to 10.2.

HTH -----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Wednesday, September 27, 2006 6:30 PM To: oracle-l_at_freelists.org
Subject: Re: Altering Freelist Groups in 10gR2 ?

I know, but based on the comments (a) he needs to make the change, and (b) he has a 3TB database, I assume the OP has migrated from an earlier version and has data in free-list managed, rather than page-table managed tablespaces.

ASSM is approximately equivalent to freelists 16 as it seems to format 16 blocks at a time when space is required. I have seen two systems recently which were suffering from serious buffer busy waits because of this limit. (Both 10.2.0.1)

A strategy error in the code means that ASSM can still fail to maintain the bitmaps properly - you can get data blocks which should be marked as full in the bitmap being constantly checked and discarded by processing attempting inserts; you can get data blocks with space in them being left as 'FULL' because of concurrent actions on the block, and only one of those actions being reflected in the bitmap.

Regards

Jonathan Lewis

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** -- http://www.freelists.org/webpage/oracle-l