Re: interpreting tablespace settings

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:ae0hvu$9i0$1_at_lust.ihug.co.nz...
>> Here's where it gets
> > interesting... If the value of PCT_INCREASE is greater than 1, Oracle
> > will then base future extent growth on the value of the LAST EXTENT
> > created TIMES the value of PCT_INCREASE. While a hotly debated issue,
> > most DBAs prefer to specify either zero or one for this parameter,
> > thereby establishing control over the possible boundless growth extents
> > can experience. There are other reasons, but for this discussion, it
> > will suffice.
>
> I think most DBAs would accept that setting it to anything other than zero
> is pretty odd (except where you can't be on hand to manage the database
> intensively all the time). And setting it to 1 is quite probably the very
> worst setting that you could pick. You get none of the benefits of extent
> growth for fast-growing tables, and all the drawbacks of SMON
automatically
> coalescing tablespace free space whenever it feels like it, regardless of
> the detrimental effect it may have on your users' I/O activity.

I rather suspect that many, if not most DBA's, will go with 1% because that's what many books (and some trainers) say you should do. If I'm right there's a big market opportunity for consultants to go in export a db, recreate it with LMT's and re-import, selling it as the last re-org that the db will need for space mangement reasons. (Obviously I'd be prepared to do this for a suitable fee!)

I am alarmed at the statement that "If the value of PCT_INCREASE is greater than 1, Oracle will then base future extent growth on the value of the LAST EXTENT created TIMES the value of PCT_INCREASE. " since this is entirely untrue. Oracle will (assuming dictionary managed tablespaces) grow extents at the rate specified by the storage parameters specified in the objects storage clause if there is one or else at the the rate specified by the tablespaces default storage clause. In both cases the size of the next extent is (1 + PCTINCREASE/100)* last extent size. in other words Oracle ALWAYS bases extent growth upon (the relevant) PCINCREASE clause. What happens with non-zero (not > 1%) values for PCTINCREASE is that SMON when it wants to, to coalesce free space. if you don't do this the DBA has to do the coalesce. Why the materials that recommend this haven't thought of DBMS_JOB I'll never know.

--
Niall Litchfield
Oracle DBA
Audit Commission UK