Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Indexed Tablespace Fragmentation
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 ...Received on Mon Apr 29 2002 - 11:53:03 CDT
>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...
>