Re: truncate command 8.1.7. locally managed tablespaces

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.
Received on Thu May 22 2003 - 06:54:21 CDT