Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Locally managed tablespaces and migrated rows

Re: Locally managed tablespaces and migrated rows

From: <xmark.powell_at_eds.com.x>
Date: 7 May 2001 13:23:24 GMT
Message-ID: <9d67kc$u7m$1@news.netmar.com>

In article <989237829.18346.0.nnrp-10.9e984b29_at_news.demon.co.uk>, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> writes:
>
>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

Jonathan answered the part of your question about how Oracle gets the extent information, and he noted that Oracle uses the free list to find the block to be used to hold a migrated row. So since I missed it on first reading to make sure everyone is clear, this would mean that unless your migrated row filled all the blocks in the 64k extent then every migrated row will not result in a free space extent being consumed so your space utilization fears are groundless. You would probably want to give careful thought to what extent size you chose, and you would also want to try to store only objects that could use the same extent size without wasting too much space or any one object taking too many extents. I think the factual information for how many extents is too many and how much wasted space is too much is currently non-existent. The numbers you use should probably be 'confort' level numbers.

Received on Mon May 07 2001 - 08:23:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US