Re: Altering Freelist Groups in 10gR2 ?

• Original Message ----- From: "Mladen Gogala" <mgogala_at_verizon.net>

Jonathan ASS managed tablespaces are the default with Oracle 10.2. Except for the much
more scattered blocks and slowdowns with full table scans, what other problems did you
observe with ASS managed tablespaces? Why did you say "high concurrency"?

--

Mladen Gogala
http://www.mladen-gogala.com

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
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 27 2006 - 07:59:42 CDT