Re: index fragmentation

But surely the only time you access the uet$ is when you run queries against dba_extents and dba_segments ?

When accessing the table/index, Oracle has an extent map in the segment header and doesn't need to know about the contents of uet$.

I have only ever found two points to be a problem - dropping an object with a large number of extents - which effectively means lots of transfers from uet$ to fet$, and the cost of large numbers of fet$ entries in the c_ts# cluster which adds to the
cost of the SMON scan for coalescing.

--

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

Howard J. Rogers wrote in message <39c7f5aa_at_news.iprimus.com.au>...


>This old one keeps cropping up.  Lots of extents introduces chaining on the

>UET$ table (assuming dictionary managed tablespaces), and *that's* the

>source of performance degradation.  Really nothing to do with the amount of

>trips to the disk for the segment itself you have to do.  And chaining

>starts happening after the sixth extent, unless you feel brave about

>customising sql.bsq before you create your database.

>

>FET$ will also chain if there are more than about 500 free extents in the

>entire tablespace.

>

>Locally Managed tablespaces are, as you say, immune from the problem.

>

>Whether all that amounts to a 'good argument for a number such as 10' (or,

>in my case, 6), I couldn't say: the impact of the 7th extent or the 11th

>isn't going to be huge.  But it is there.

>

>Regards

>HJR

>--