Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: truncate command 8.1.7. locally managed tablespaces

Re: truncate command 8.1.7. locally managed tablespaces

From: Bob <where_was_you_at_btinternet.com>
Date: 20 May 2003 18:03:19 -0700
Message-ID: <733fcc55.0305201703.6ffb1878@posting.google.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US