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 -> Reorg/Resize Very Large Oracle Applications Database Performance

Reorg/Resize Very Large Oracle Applications Database Performance

From: Ron Hurley <rhurley_at_xtn.net>
Date: 14 May 2003 04:51:01 -0700
Message-ID: <ae82b4e.0305140351.6204a745@posting.google.com>


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 Received on Wed May 14 2003 - 06:51:01 CDT

Original text of this message

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