Re: truncate command 8.1.7. locally managed tablespaces

[snip]

> I don't have a choice about the state of the database since we
> inherited it as part of an outsourcing contract. Version is 8.1.7.1 on
> a Sun using HR Oracle Application. I don't know whether it came shrink
> wrapped with LMT or they were set afterwards I'd just never seen this
> behaviour before but I have exploited it by exporting the rows setting
> the next to nice values truncating coalescing and reimporting the
> rows. Works a treat and most stuff has halved in time anything with
> full table scans upto five times faster. So much for the many extents
> don't matter pr.

They don't. Don't confuse cause and effect. You've done export and import: that alone means that rows get re-packed, and any space scattered throughout the table gets removed. Therefore, the highwater mark is aligned to the end of closely-packed data, and of course you could expect performance improvements on tablescans etc to accrue as a result.

Does that have anything to do with the number of extents issue?? Not in the slightest. Performance can be expected to improve from such major re-organisations of data, regardless of the number of extents going up, down, or hovering about the same.

>Obviously there was more to it than that disabling
> constraints and triggers to do the job and the resultant test database
> needs further testing although invalid objects were the same before
> and after. It actually would allow getting uniform extents in place.
> The original problem was fixed by setting percent increase to zero and
> setting next to a suitable value. Judging by the comments on 9i then I
> presume this is a bug although I couldn't see anything pertinent on
> metalink. I actually believe the OA stuff comes with 50% default
> values so that users don't hit space problems(extents).

50% PCTINCREASE has been the default value for Oracle since year dot. It's a disgraceful default, and should always be changed to zero, without exception.

Regards
HJR
>I never used
> %increase on any database since version 6 of Oracle. The only reason
> it came to light was this import truncate cycle which bumped the next
> by 50% each time since initial was only 5 blocks(default oracle size).
> Regardless of %increase it always takes the next value as the extent
> size for any insert into the table.
>
> Thanks for your prompt Replies
>
> Regards
>
> Bob
Received on Wed May 21 2003 - 01:24:05 CDT