Re: Locally managed tablespaces and migrated rows

The act of migrating a row is (pretty much) like inserting a row - Oracle tries to insert the row into the first block on the free list. There is no need for Oracle to know anything about the 'type' of extent that the block comes from.

Information in dba_free_space information is acquired by reading the bitmap blocks at the head of each LMT file. Information in dba_extents comes from reading every single segment_header block (and where relevant 'unlimited extent' block) in the tablespace. The results are made to appear as if they are present in a couple of X$ tables, but actually have to be refreshed by db block reads if the blocks are aged out of the buffer.

Reading dba_free_space for LMT's is not much more expensive (usually) than the same task for dictionary managed tablespaces. Reading dba_extents usually is.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Nuno Souto wrote in message <3af66cf1.819932_at_news-server>...


>Or chaining, as we used to call it.  Probably already answered, but I

>can't find anything in google.

>

>Locally managed tablespaces with uniform extent allocation sound to me

>as a recipe for disaster when tables get many migrated rows.

>

>Picture this:

>

>1- Space is allocated in "chunks" (extents) of say, 64K.

>

>2- These chunks are mapped back to a bit in a bitmap.

>Bit is either on (allocated) or off (free), no other info possible.

>Or else it's not a bit!

>

>3- Punter creates tables with insufficient pctfree.  Quite common in

>the case of third party apps like Peoplesoft, where DBAs are

>encouraged to behave like trained monkeys instead of rational humans.

>

>4- The app changes the length of many rows in many blocks of the

>table.

>

>5- Obediently, ORACLE will allocate from somewhere an extra block for

>each of the migrated rows when they get "bumped" off the end of the

>block.  Where does this extra block come from?  From one single bitmap

>bit for *EVERY* single row that gets migrated in each block? Ie, get

>one row migrated, loose 64K of free space?

>

>6- If so, pretty soon punter starts complaining ORACLE is a disk space

>hog, there is no free space anywhere in his carefully crafted

>tablespace, initially sized with mathematical precision!

>

>Now, I've been through all the info on locally managed tablespaces I

>could find, trying to dispel this fear of mine.  Nothing has done it.

>Please someone more knowledgeable allay my fears?

>

>Note that this is not a case of reuse of free space in the last

>allocated extent!  We are talking potentially all blocks in table, not

>just the ones near the HWM where presumably the extent is not yet full

>and there are free blocks to use.

>

>Does ORACLE do the intelligent thing and allocate the migrated blocks

>into free blocks near the HWM?  And only allocate a new extent when

>needed, therefore adjusting again the HWM?  I sincerely hope so...

>

>And while I'm here: where the heck does ORACLE get the info for

>dba_free_space and dba_extents for a LMTS?  It ain't from FET$ and

>UET$ for sure...

>

>Many thanx for any feedback/experiences on this.

>

>Cheers

>Nuno Souto

>nsouto_at_bigpond.net.au.nospam

>http://www.users.bigpond.net.au/the_Den/index.html