Re: Database space grawth decreses 100%

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.

>
> 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. 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.

Also, did you re-create the database from scratch? 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.

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.

>
> 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

--
@home.com is bogus.
Salsa with chips:  http://www.signonsandiego.com/uniontrib/20040714/news_1n14silicon.html