Re: Reorg/Resize Very Large Oracle Applications Database Performance

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<nig4cvc0h4b3i75i3lrljmjr9pj4hretvp_at_4ax.com>...
> On 14 May 2003 04:51:01 -0700, rhurley_at_xtn.net (Ron Hurley) wrote:
>
> >I am attempting to reorg/resize an Oracle Applications Database. I
> >have 72 hours to completely reorg/resize this database.
> >
> >Environment:
> >
> >IBM RS/6000 (AIX 4.3.3)
> >Procs - 8
> >Memory - 12GB
> >DB Datafiles - 900
> >SUM(DB Datafile Size) - 300GB
> >Oracle Server - 8.1.7.4
> >
> >Purpose: After purging applications data, tables/indexes need to be
> >reorg'd and tablespaces need to be resized. I must produce a database
> >that is less than 200GB or less if possible.
> >
> >Problem: Time. It is taking too long to relocate the objects from
> >the tablespaces. It is also taking too relocate them back to the new
> >tablespaces. New tablespaces must have the same name as the original,
> >due to registeration of the Tablespace names in the applications.
> >Cannot export/import, move/rebuild to new tablespace as a permanent
> >Tablespace name.
> >
> >Attempt #1: Create a VLTS (Very Large TableSpace) 300GB.
> > Move Tables, Table Partitions, Lobsegments to VLTS.
> > Rebuild Indexes, Index Partitions to VLTS.
> > Export (exp) whatever is remaining in the original TSs.
> > Drop original TSs.
> > Create new TS (smaller, based on # extents, plus 25%)
> > Import (imp) objects exported.
> > Move Tables, Table Partitions, Lobsegments to new TSs
> > Rebuild Indexes, Index Partitions to new TSs.
> > Gather Stats.
> >
> > Tried this with the 60GB Inventory tablespaces
> > (INV_DATA, INV_INDEX). This took over 48 hours to
> > complete.
> >
> >Attempt #2: Export database to tape (IBM Magstar 3570), due to not
> > enough disk space to export to as well as create a new
> > database in.
> >
> > After 30 hours, core dump. Export aborted.
> >
> >Attempt #3: Using parallel processes (17).
> > Create VLTS.
> > Move/Rebuild all objects from 34 of the largest
> > tablespaces to VLTS. Most tables were moved within a
> > couple hours, but after 2 days, the indexes were still
> > being rebuilt. Server was extremely slow during the
> > Index rebuild.
> >
> >Attempt #4: Export to Disk.
> > After 14 hours, it was at about the same place as it
> > was when I tried to export to tape. Killed it.
> >
> >Question: Is there a faster way to completely reorg/resize a
> > 300GB database? Or is this as good as it gets.
> >
> >I have reviewed the datafiles highwater mark and can (without
> >reorg'ing) "have" reduce the datafiles sizes to about 23% of the total
> >size, just by resizing to the highwater mark. This saved me about
> >70GB (230GB now), but my objects still need to be reorg'd for
> >performance and tablespaces need to be resized to fit on a new server
> >which has less space than current (instance strategy).
> >
> >I've review several can'd packages that can do reorgs, but none
> >provide a way to reduce a tablespace's size.
> >
> >If there are any experienced Oracle DBA's who have been able to do
> >this with large databases, please post a response.
> >
> >Sincerely,
> >
> >Ron
>
> I don't have any experience with some of what you're tryinig to do,
> but I'm going to go out on a limb here because a couple of things kind
> of jumped out at me.
>
> First, you say "my objects still need to be reorg'd for
> performance". What specific performance bottleneck to you expect to
> be relieved by doing a re-org? Check the ng archives for a discussion
> called "oracle myths." Until you identify the performance bottleneck,
> you're just guessing that a reorg will solve it. One could just a
> easily guess that it will be solved by throwing a bunch of indexes at
> it, or by turning the lights out in the data center. Once you *do*
> identify the bottleneck, I'm pretty certain you'll see that a reorg
> will not solve it.
>
> Second, have you done any sizing calculations to see if it is even
> *possible* to get the db as small as you want it? Sounds like you
> could very well be trying to get 50 gallons of s.. ah, fertilizer
> into a 30 gallon barrel. Have you looked at adjusting pct free?
> Reducing this *could* result in more rows per block, which translates
> to higher data density and fewer blocks/less disk space required to
> hold the data. It could also lead to chained rows, so it is a
> balancing act, but something to throw into the calculations of just
> how small can you get this thing. Don't forget indexes. A heavily
> indexed table can easily end up requireing more blocks for all of the
> indexes than the data itself.
>
> Third, my gut feel is that your trials are taking *waaayyy* to long,
> thus pointing to some underlying performance bottleneck. Solve *that*
> and your presumed need for a reorg may go away.

You should IMHO have something measurable you want to change .i.e have a metric which you can measure before and after your reorg . Remember you can also rebuild indexes online and that should take less time than doing it from scratch.
-Make your sort area size pretty big to handle your move. -Keep out users.
You could free up temp tablespace intermittently using a cron job to  change pctincrease to y from x intermittently .Since this "wakes" up smon to clean up the temp tablespace for you . If reclaiming space is a big issue you could also think about resizing datafiles after checking your percent_free Though this is a temporary some datafiles could be less than 10% used in the database

Michael Tubuo Ngong Received on Thu May 15 2003 - 10:39:49 CDT