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: Indexed Tablespace Fragmentation

Re: Indexed Tablespace Fragmentation

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 29 Apr 2002 17:53:03 +0100
Message-ID: <1020099119.8004.0.nnrp-12.9e984b29@news.demon.co.uk>

Interesting point, and one that I hadn't thought of.

I would think that the fact that it is an HW enqueue is (roughly speaking) coincidental - the problem is the time it takes to add an extent when the hwm cannot be bumped.

The comparison on an otherwise empty tablespace is then between:

    Reading the bitmap from start to next free bit.     which could be a handful of blocks and a lot     of CPU down the file
and

    Hitting the one fets$ block in the c_ts# cluster     to find the one fet$ row which represents the     free space left at the end of the tablespace.

I think your comment tells us which one is faster.

Still, as you say, the correct answer is "Don't Do That Then", and I would hope that any sensible DBA would have moved the guilty object to the correct tablespace before it got to more than a couple of hundred extents.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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



Andrew Mobbs wrote in message ...

>I'm becoming increasingly suspicious that LMTs become less efficient
>than DMTs for very large numbers of extents, i.e of the order 100,000
>extents. Symptoms are that HW enqueues take much longer, I'd guess
>Oracle has to trawl through the bitmaps sequentially instead of
>following a B*Tree.
>
>One obvious answer is Don't Do That Then. However, sometimes the
>developers forget to mention a table...
>
Received on Mon Apr 29 2002 - 11:53:03 CDT

Original text of this message

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