Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: truncate command 8.1.7. locally managed tablespaces
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<vJoya.38566$1s1.544927_at_newsfeeds.bigpond.com>...
> "Bob" <where_was_you_at_btinternet.com> wrote in message
> news:733fcc55.0305191125.4d95064f_at_posting.google.com...
> > The truncate command on non uniform locally managed tablespaces leaves
> > the next extent and pct_increase intact instead of taking the default
> > values from the tablespace as for dictionary managed.
>
> I don't think I ever tested the use of PCTINCREASE with autoallocated
> locally managed tablespace in 8i.
>
> Two reasons: autoallocate was strongly not recommended at the time, and
> PCT_INCREASE was such a stupid invention way back in Oracle 7 or before,
> that you'd have had to be criminally insane to want to combine the two
> features!
>
>
> >Next time
> > records are inserted beyond the initial extent size then it uses the
> > next extent size for its calcxulation worst it then multiplies it by
> > 1.5 (50% pct_increase shrink wrapped oracle applications). This was
> > noted in a migration program that truncated all old rows for a
> > previous run before importing more and eventually reached a 12.9G next
> > extent before space could not be allocated after about 23 such
> > migrations.
> > Does anyone know if this is a bug or a feature all the stuff I've read
> > says it ignores both next extent and %increase.
>
> All I can tell you is that in 9iR2, after a quick test:
>
> I created a table with PCTINCREASE of 50%. As per the normal autoallocate
> algorithm, I got 16 extents of 64K, and the 17th was of 1MB, indicating that
> the PCTINCREASE setting was completely ignored.
>
> I then kept inserting, and got repeated 1MB extents (so PCTINCREASE was
> still being ignored). If I'd kept going, I'm sure I would have eventually
> (around the 200th extent or therabouts) shot up to 8M extents. Which is all
> just perfectly standard autoallocate behaviour.
>
> I then truncated the table.
>
> I then re-inserted some rows... and I got 16 64K extents, before it started
> allocating the 1MB extents.
>
> Therefore, if as you report (and I don't doubt it) 8i 'remembers' the extent
> sizes is was up to before the truncate, so that the new set of extents start
> off at the enormous size they had previously reached, then that is certainly
> not the behaviour in 9i.
>
> All I can suggest is that you don't use autoallocated LMT until you get to
> 9i (where it seems reasonably sound as an allocation method), and that you
> should have stopped using PCTINCREASE some time in version 7.
>
> Regards
> HJR
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. 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). 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 Tue May 20 2003 - 20:03:19 CDT