Load Performance - RS6000 and Oracle

richardjackson · Jan 5, 2001

This is a multi-part message in MIME format.

------=_NextPart_000_0000_01C07769.D2953880
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I am trying to run loadall and the load times are about 6 hours per path
code.

RS/6000 model M80 with 4 processors and 6 GB memory

AIX ver 4.3

Oracle 8.1.7

The system is dedicated to this task.

We have done the following:

1. created separate raid 0 + 1 sets (striped plus mirroring) for the tables
and data. There is one logical volume for data and one for indexes. We are
trying to load all three path path codes into the same logical volume (into
the same mount point).

2. created a very large rollback segment, turned off all other rollback
segments

3. turned on asynchronous IO - about 300 tasks

The import file is stored on the RS6K file system . A couple disk drives
are pretty busy, CPU utilization is about 30 percent total - 3 CPUs are
slightly less loaded than the fourth CPU. There is essentially no page
faulting.

Any ideas or comments? Is 6 hours per path code satisfactory or should I
expect 1 hour per path code? I suspect that I am missing something silly
but I don't know what it is.

Richard Jackson

------=_NextPart_000_0000_01C07769.D2953880
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns

=3D=20
"urn:schemas-microsoft-com

ffice

ffice" xmlns:w =3D=20
"urn:schemas-microsoft-com

ffice:word"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3DWord.Document name=3DProgId>
<META content=3D"MSHTML 5.50.4611.1300" name=3DGENERATOR>
<META content=3D"Microsoft Word 9" name=3DOriginator><LINK=20
href=3D"cid:filelist[email protected]" rel=3DFile-List><!--[if gte =
mso 9]><xml>
<o:OfficeDocumentSettings>
<o

oNotRelyOnCSS/>
</o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:Zoom>0</w:Zoom>
<w

ocumentKind>DocumentEmail</w

ocumentKind>
<w:EnvelopeVis/>
</w:WordDocument>
</xml><![endif]-->
<STYLE>@font-face {
font-family: Tahoma;
}
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; =
mso-style-parent: ""; mso-pagination: widow-orphan; =
mso-fareast-font-family: "Times New Roman"; mso-bidi-font-family: "Times =
New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; =
mso-style-parent: ""; mso-pagination: widow-orphan; =
mso-fareast-font-family: "Times New Roman"; mso-bidi-font-family: "Times =
New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; =
mso-style-parent: ""; mso-pagination: widow-orphan; =
mso-fareast-font-family: "Times New Roman"; mso-bidi-font-family: "Times =
New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
A:visited {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
SPAN.MsoHyperlinkFollowed {
COLOR: blue; TEXT-DECORATION: underline; text-underline: single
}
P.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; =
mso-pagination: widow-orphan; mso-fareast-font-family: "Times New =
Roman"; mso-bidi-font-family: "Times New Roman"
}
LI.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; =
mso-pagination: widow-orphan; mso-fareast-font-family: "Times New =
Roman"; mso-bidi-font-family: "Times New Roman"
}
DIV.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; =
mso-pagination: widow-orphan; mso-fareast-font-family: "Times New =
Roman"; mso-bidi-font-family: "Times New Roman"
}
SPAN.EmailStyle17 {
COLOR: navy; mso-bidi-font-family: Arial; mso-style-type: =
personal-reply; mso-ansi-font-size: 10.0pt; mso-ascii-font-family: =
Arial; mso-hansi-font-family: Arial
}
DIV.Section1 {
page: Section1
}
OL {
MARGIN-BOTTOM: 0in
}
UL {
MARGIN-BOTTOM: 0in
}
</STYLE>
</HEAD>
<BODY lang=3DEN-US style=3D"tab-interval: .5in" vLink=3Dblue =
link=3Dblue>
<DIV><![if !supportEmptyParas]><![endif]><![if =
!supportEmptyParas]><![endif]><![if !supportEmptyParas]><![endif]><![if =
!supportEmptyParas]><![endif]><SPAN=20
style=3D"COLOR: black; mso-color-alt: windowtext"><o

>

I =
am=20
trying to runloadall =
and the=20
load times are about 6 hours per path code. 

RS/6000 model M80 with 4 processors and 6 =
GB=20
memory

AIX ver 4.3

Oracle 8.1.7

The system is dedicated to this =
task.

We have done the following:

1. created separate raid 0 + 1 sets =
(striped plus=20
mirroring) for the tables and data. There is one logical volume for data =
and one=20
for indexes. We are trying to load all three path path codes into the =
same=20
logical volume (into the same mount point). 

2. created a very large rollback segment, =
turned off=20
all other rollback segments

3. turned on asynchronous IO - about 300=20
tasks

The import file is stored on the =
RS6K file=20
system. A couple disk =
drives are=20
pretty busy, CPU utilization is about 30 percent total - 3 CPUs are =
slightly=20
less loaded than the fourth CPU. There is essentially no page=20
faulting. 

Any ideas or comments?<SPAN=20
class=3D325583904-06012001> Is 6 hours per path code =
satisfactory or=20
should I expect1 hourper path code? I suspect that I =
am=20
missing something silly but I don't know what it is.=20


Richard=20
Jackson</o

></DIV></BODY></HTML>

------=_NextPart_000_0000_01C07769.D2953880--

altquark · Jan 6, 2001

I know I replied to this privately - but thought the list should see as well !

6 hours per pathcode is actually about average for an Oracle Loadall for OneWorld Xe (2million+ F98741 records). Obviously, it depends on the system configuration and how the import is configured - I know of DBA's who have changed the scripts to achieve 20% faster performance, but since its usually a once-off - I usually try to plan the loadall on Oracle over a weekend.

On MS Sql Server - the loadall can run in as little as 20 minutes per pathcode - and on the AS400, about 1 hour per pathcode. It seems as if the import utility used on the SQL Server is just more efficient for some reason - probably the scripts don't validate half as much as Oracle.

The time that Loadall takes to complete between DBMS systems are not an indicator of OneWorld Database Performance. However comparison of loadall between the SAME RDBMS is an indicator of how fast the machine is in my opinion. For example, the first SQL Server (MS 6.5) setup I put together was a DEC Alpha 4200 - the loadall back in 1997 took 4 hours per pathcode (on approximately 1million lines in the F98741). The latest loadall I ran on MS Sql Server (7.0) was on a Dell Quad Xeon 550Mhz machine with Fiber-connected 10,000RPM drives. Twice the amount of data in the loadall took 20 minutes to install - a factor of 24x faster in a matter of 4 years !!!

Jon Steel
Chief Technologist

ERP Sourcing
http://www.erpsourcing.com
[email protected]

richardjackson · Jan 6, 2001

Jon:

When we ran a single import on this system, the F98741 (containing a total
of about 2.17 million rows) was loading at the rate of 9,500 records per
minute.

We are currently loading PY and DV in parallel. The PY load is adding
records to the F98741 at the rate of 4,400 rows per minute. Same rate for
the DV load. The combined rate is slightly less than the single rate so
running the jobs in parallel makes better use of CPU capacity (%idle is
running from the mid 60s to the low 70s) but it seems to be choking on a
single disk spindle. I have been informed that this disk arm contains the
redo logs. Since there can be only one active redo log and writes to it are
synchronous, is there anything that we can do to eliminate this choke point?
For example, can we turn off redo logs?

We thought about changing the scripts to import one copy of the central
objects then to copy the tables using another technique such as "create
table as select * from user.f98741" or "insert into where (select * from
user.f98741)". We find that at the 8.1.7 Oracle code level, these choices
don't work when the tables contain a column of type long raw. Most tables
in central objects do contain long raw types.

We are exploring the idea of transportable table spaces.

Comments? Suggestions?

Richard Jackson
(speaking for myself)

Larry_Jones · Jan 8, 2001

Richard,

I didn't see what OW version you're installing . . . XE?
Would like to know.

Our last experience was B733.1. Box is an HP D390, Dual Processor, 1GB memory at that time. Also at that time was Oracle 8.0.5.

We saw times of 3 hours per path code then. I recall that we upped the import buffer space in the load scripts for the central objects. Also at that time the LoadAll script ran serially (imported one Path code at a time). Your email seems to imply that you are running all paths simulataneously? Is this the case for XE?

Regards,

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6

richardjackson · Jan 9, 2001

We are loading XE.

We did some buffer pool tuning. The Loadall script still runs serially but
we ran the pieces separately on Saturday. Since throughput was dependent on
the redo logs and they were stored on a single disk, there was no throughput
improvement when running two jobs at once. At the B733.1 level, the F98741
file had a lot fewer records than it does at the XE level. At the XE level,
that table contains 2.18 million rows.

Richard Jackson
(speaking for myself)

ramiro · Jan 10, 2001

We did the import a long time ago, but I can remember that one of the things our DBA did was to change to commit=N in the load script.

Alejandro Welsh
JDE Technology Services
Shell C.A.P.S.A.
[email protected]

Load Performance - RS6000 and Oracle

richardjackson

Active Member

altquark

Legendary Poster

richardjackson

Active Member

Larry_Jones

Legendary Poster

richardjackson

Active Member

ramiro

Member

Similar threads