Re: Reorg a Large Database

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1996/02/28
Message-ID: <Pine.SUN.3.91.960228170928.15398B-100000_at_seatimes>#1/1

On 29 Feb 1996, Garth deCocq wrote:
> I have a 36g database badly in need of a rebuild. The system tablespace
> has become fragmented. Have any of you out there had experience -
> successful or unsuccessful - with doing this? I'd be very interested
> in hearing what strategies you used.

My experience with a 12g database may help. First off, export and import just doesn't really cut it unless all you are looking for is putting everything into the initial extent. Even then, the system tables aren't exported as such but rebuilt when you re-create the database.

I went into sql.bsq and changed the storage parameters at the tablespace level (and some tables). Then re-created the database. Then did the import. It helped some but the following needs another solution:

1. Import just doesn't hold a candle to sqlldr in DIRECT mode.
2. The ordering of the records (while we aren't supposed to care) still caused some index cluster factors to be extremely large. In fact, it was this particular index that is hit alot. Putting neighboring keys into the same block would improve the logical to physical reads.
3. Oversized initial extent are not reduced to accomodate what is actually in the table.
4. Still doesn't help you pick a better pctfree/pctused set of numbers.

Perhaps one of the reorg product users could comment on how TSREORG or ARIS's DEFRAG handle the above items.

Ideally, each table would be unloaded to a sqlldr compatible file (and build the .ctl file also) with an optional sort on a column or set of columns. The unloader would build the CREATE statements needed to properly size the table and the indexes associated with the table.

Something along the order of a full TABLESPACE export but with enhanced functionality to sort on critical columns and use sqlldr DIRECT to put the data back in.

If you have multiple processors, you might get some advantage in spreading the work by tablespace around the processors.

That's the state of where I am at on my wish list. Say hi to Peter for me.

--Steve

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-382-8898         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |
| Seattle, WA 98111    Packet:  KG7JE_at_N6EQZ.WA       |
+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Wed Feb 28 1996 - 00:00:00 CET