RE: Reorganizing a database

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Tue, 10 Sep 2002 10:58:08 -0500
Message-Id: <22528.293328@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_047A_01C258B8.F64B1E20 Content-Type: text/plain;

charset="us-ascii"
Content-Transfer-Encoding: 7bit

Russ,

This is OT relative to your question slightly, but. Be sure to take some good response time measurements (10046 level 8) before the rebuild so that you can make objective before/after comparisons. If you're right about your prediction that all this work won't provide much of a performance impact (and I suspect that you are exactly right), this is the only way you're going to be able to prove it.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic <http://www.hotsos.com/training/clinic> , Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium <http://www.hotsos.com/events/symposium> on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum <http://www.miracleas.dk> , Sep 20-22 Middlefart Denmark

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of Brooks, Russ
Sent: Tuesday, September 10, 2002 10:44 AM To: Multiple recipients of list ORACLE-L Subject: Reorganizing a database

Hi,

We are creating a 45GB database from a restore of a current system. My boss has requested that I reorganize the entire new database. I have my doubts that this will provide as much benefit as he seems to expect, but they are adamant that this is what they want. One of the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O. I'm thinking of using a full export as the base of this activity, splitting the export. This will be done on an HP L class with 2 500MHz processors and 3 GB of memory.

What is the best way of accomplishing this as quickly and painlessly as possible? I've looked through Metalink and various books without finding a good general procedure for changing the location and sizes of the datafiles during the import.

About how long should this take (ballpark)?

Thanks,

Russ

------=_NextPart_000_047A_01C258B8.F64B1E20 Content-Type: text/html;

charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUS-ASCII">

<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--

/* Font Definitions */
@font-face

	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}

/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal

	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p
	{margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Times New Roman";}
span.EmailStyle17
	{font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}

-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Russ,

This is OT relative to your = question
slightly, but… Be sure to take some good response time = measurements (10046
level 8) before the rebuild so that you can make objective before/after = comparisons.
If you’re right about your prediction that all this work = won’t
provide much of a performance impact (and I suspect that you are exactly = right),
this is the only way you’re going to be able to prove = it.

<div>

Cary = Millsap 
Hotsos Enterprises, Ltd. 
<a href=3D"http://www.hotsos.com">http://www.hotsos.com</a> 
 

Upcoming events: 
- <a href=3D"http://www.hotsos.com/training/clinic">Hotsos Clinic</a>, = Oct
1–3 San Francisco, Oct 15–17 Dallas, Dec 9–11 = Honolulu 
- <a href=3D"http://www.hotsos.com/events/symposium">2003 Hotsos = Symposium</a> on
Oracle® System Performance, Feb 9–12 Dallas - Next event: <a href=3D"http://www.miracleas.dk">Miracle Database = Forum</a>, Sep
20–22 Middlefart Denmark

</div>

-----Original Message----- 
From: root_at_fatcity.com
[mailto:root_at_fatcity.com] On Behalf = Of Brooks,
Russ 
Sent: Tuesday, September =
10, 2002
10:44 AM 
To: Multiple recipients =
of list
ORACLE-L 
Subject: Reorganizing a =
database

<div>

Hi,

</div>

<div>

  We are creating a 45GB database from a restore = of a
current system.  My boss has requested that I reorganize the entire = new
database.  I have my doubts that this will provide as much benefit = as he
seems to expect, but they are adamant that this is what they want.  = One of
the desired effects is to resize all the datafiles to uniform sizes and distribute some I/O.  I'm thinking of using a full export as the = base of
this activity, splitting the export.  This will be done on an HP L = class
with 2 500MHz processors and 3 GB of memory.

</div>

<div>

  What is the best way of accomplishing this as = quickly
and painlessly as possible?  I've looked through Metalink and = various
books without finding a good general procedure for changing the location = and
sizes of the datafiles during the import.

</div>

<div>

  About how long should this take = (ballpark)?

</div>

<div>

</div>

<div>

Thanks, Received on Tue Sep 10 2002 - 10:58:08 CDT