Re: Reorg/Resize Very Large Oracle Applications Database Performance

Hello!

Based on your current hardware, I'd go with attempt 3. As you said, moving tables is no problem, but index creation takes too long time. You said that during the index creation, server was very slow - what exactly was slow? Did you check v$session_wait for example, to see on what the server waited? I bet you had a lot of scattered reads and direct reads/writes, because a lot of sorting has to be done in addition to full table scans when creating indexes on big tables. Maybe things got slow, because you had too many concurrent index creation processes and you IO just can't handle it?
How big was your sort_area_size during index creation? Also you might want to increase db_file_multiblock_read_count during index creation. Did you build indexes using parallel clause and nologging? Or did you just have several serial scripts which ran parallelly?

Btw. did you move your tables back to right tablespace before creating indexes?

If you want to go with export-import to a new database, note that some stuff isn't transferred with exp/imp like AQ settings, check Metalink note 204015.1 for that. Also you migh want to check my paper on 11i upgrading with short downtime: http://integrid.info/live2003.html (about 30 pages of 11i and DB related information). You might get some ideas from there as well.

Also, I saw you had 900 files for your apps database? Is it partially because you have every module/product in different tablespace? I'd put unused modules together in fewer tablespaces with smaller extents, otherwise you waste a lot of space for nothing.

So, for continuing, I'd check on what your database is waiting the most when creating indexes & try to tune it.
Also, you could spread your downtime, just by moving tables/recreating indexes module-by-module, some on the first weekend, some on the next etc..

Tanel.

"Ron Hurley" <rhurley_at_xtn.net> wrote in message news:ae82b4e.0305140351.6204a745_at_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 - 09:11:54 CDT