Re: Reorg/Resize Very Large Oracle Applications Database Performance

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. Received on Wed May 14 2003 - 08:37:56 CDT