"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<zI2za.39771$1s1.564581_at_newsfeeds.bigpond.com>...
> It's not PR. It's been tested to destruction many, many times.
>
> The number of extents was NEVER an issue for the segment. EMP couldn't care
> less whether it comes in 5 extents or 5000. But what does care is the data
> dictionary, since 5000 extents means 5000 rows in a data dictionary cluster
> that's been sized to expect no more than 5. That introduces cluster chaining
> ion the data dictionary, and if the chaining is bad enough, then performance
> impacts involving dictionary access will be measurable.
>
> Since LMTs don't touch the data dictionary (at least for the purposes of
> recording extent acquisition), they cannot suffer from cluster chaining, and
> therefore cannot experience the performance degradation that it can give
> rise to. Therefore, 5 or 5000 makes absolutely no difference to the table
> (which it never did anyway) and no difference to the data dictionary (which
> is new).
>
> (Now, before anyone jumps in, a huge number of extents *could* upset the
> table, and still could. It's the issue of reading the extent map in a single
> I/O instead of multiple I/Os.... since the extent map is stored inside an
> Oracle block, which is of finite size, too many extents mean that the map
> has to be stored in multiple blocks... and any operation that needs to
> consult the extent map would therefore need multiple I/O operations to do
> so. You would really need thousands of extents, though, before that became a
> major drama).
>
> At the end of the day, there's very little performance improvement to be had
> with LMTs over DMTs, nor much performance degradation with DMTs over LMTs.
> You'd have to get into the multi-thousand extents before it became easily
> measurable.
>
> The key advantage of LMTs is that they avoid a potential single point of
> contention on the data dictionary (but you'd have to have dozens of segments
> all simultaneously extending before that was an issue in the first place).
> And (better) they mean fragmentation is a thing of the past. And (the real
> killer) concern about a reasonable number of extents is wasted concern
> (reasonable being in the high hundreds to low thousands).
>
> Regards
> HJR
>
>
> "Bob" <where_was_you_at_btinternet.com> wrote in message
> news:733fcc55.0305220337.83ef5c4_at_posting.google.com...
> > I'd need more convincing about the many extents don't matter pr
> > because We did a full export import of the same data base with a
> > complete new setup. The result did reduce full scans somewhat but not
> > by anything like 5 times and the long running 'on line' query for OTA
> > went from one hour to 2 hours. Like I said currently there is no
> > choice but to stick with the existing set up for several months. What
> > we seem to have here is an hybrid neither dictionary managed or
> > locally managed has documented. Way to prove it is to export the date
> > truncate and bring the tables back using small next extent size and no
> > increase. By the logic above two extents should give the same
> > performance as 500 if I get the chance I will repeat the exercise.
> > Some of the performance issues are down to pure bad sql (Oracle's)
> > which have been partially fixed in later releases but not currently an
> > option for this 'frozen site'. Pr is ok as long as it translates into
> > experience. I only raised the original topic to address the 'feature'
> > on truncate not start a forest fire on the totally inadequate Oracle
> > maintenance routines.
Thanks for the time that you put in on this Howard and I will further
test out the 'extents don't matter' when I get time. There is more
logic to don't on raid based systems using cabinet interfaces than
there is on single disks where the data can be streamed from a single
extent. The tests I did where based on the second type. The former
logical relationships with hard drives can write and read absolutely
anywhere. This is one of the problems with tuning what looks good on
a small scale development host often doesn't translate into the same
gains under production conditions with greater volumes and different
hot spots.
Received on Thu May 22 2003 - 19:15:26 CDT
