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: Database space grawth decreses 100%

Re: Database space grawth decreses 100%

From: Martin <mjarosinski_at_alldata.net>
Date: 16 Jul 2004 11:16:48 -0700
Message-ID: <a9c546d4.0407161016.29ce2cc9@posting.google.com>


joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0407141313.a158ed4_at_posting.google.com>...
> mjarosinski_at_alldata.net (Martin) wrote in message news:<a9c546d4.0407130640.2d404902_at_posting.google.com>...
> > Hi All,
> >
> > We have recently completed a re-organization of most of our production
> > databases (oracle 8.1.7.2), this was the first ever re-org. to happen
> > on these database (for reason I do not want to go into).
>
> Aw, come on, we love horror stories! Substitute cox.net for home.com
> in my email, send me the gory details and I'll post it anon. I'm
> amazed you could get to 500G without proper DBA work.
>

LOL, no Joel I would rather not.

> >
> > First, what do I mean by re-organization, we introduce locally managed
> > tablespace (excluding the system tablespace) and moved all the tables
> > and rebuild all the indexes on the new tablespace, also we introduced
> > standard for extent sizes, organized segment into groups by sizes and
> > did our best to standardise storage parameters in general.
> >
> > Besides reclaiming around 20% of disc space, this was the mean reason
> > this was done (this particular DB was 500GB before the "re-org") we
> > are experiencing (consistently for 2 moths now) a decreased database
> > growth. Before the re-org. exercise we were growing by 4GB a month,
> > now we grow by 2GB. There were on major changes to our batch
> > processes or the way our clients do business.
>
> Before reorg, did you check for chained rows? I'm taking a wild stab
> that if no administration had ever been done, there were many cases of
> updating rows not fitting into blocks, so they would be chained to
> additional blocks. With strange extent sizes, there could be lots of
> wasted space.

I belive the channig was at 10%

> Also, if pctincrease wasn't 0, you might have some very
> strange things happen after several increases, with extentions
> happening much larger than will ever be needed.
>

PCTINCREASE was 0, that i sure of.

> Also, did you re-create the database from scratch?

No, we did not.

> Did you check to
> see if user objects were in the system tablespace? You may have had
> strange things happening because of the tables used to track
> dictionary managed tablespaces being over-extended and playing
> hopscotch with user objects.

System tablespace had a few small user objects.

>
> There's probably a lot more I'm not thinking of, as Daniel said,
> insufficient data. Now listen to him and upgrade.
>
> >
> > Initial saving of 20% is easy to explain, however we have not been
> > able to explain why the on-going growth has decreased.
>
> Sounds like by 50%, not 100% :-) Also, some purging algorythms could
> leave holes which might be fixed by proper extent management.

50% it is, sorry. :)
This client does not user the purge process.

>
> >
> > Anyone run into the same thing?
> > Are LM tablespace this match better at space management?
>
> Yes, they are, from uniform (or reasonable) extent sizes alone.
> Google this group for LMT for more details.
>
> >
> > Thank you,
> > Martin
>
>
> jg

Thank you for your input,
martin j. Received on Fri Jul 16 2004 - 13:16:48 CDT

Original text of this message

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