[Oracle-l] Lets load test

I have a benchmark script I would like you to try, will send you the
attachment in a seperate email. Be interested in seeing the numbers, it is
very easy to run. I will post the script on the web when I get a chance for
everyone.

-Ethan

Ethan,

Test results with 5 million / 10k commit of your scripts:

Wed Dec 14 12:38:02 EST 2005 Benchmark Results
v_rows=5000000 | v_parse=1000 | v_commit=10000

INSERT APPEND INTO BENCHMARK : 00:00:19.63
GATHER TABLES STATS : 00:00:01.90
CREATE TABLE LOGGING : 00:00:09.71
UPDATE COLUMN : 00:02:34.21
DELETE ALL FROM TABLE : 00:01:04.98
DROP TABLE : 00:00:17.55
CREATE TABLE NOLOGGING : 00:00:07.85
INSERT/COMMIT LOOP : 00:00:00.01
BULK UPDATE : 00:02:08.65
TRUNCATE TABLE : 00:00:00.18
ADD COLUMN NUMERIC DEFAULT : 00:02:20.82
ADD COLUMN CHARACTER DEFAULT : 00:03:16.27
DROP NUMERIC COLUMN : 00:01:38.53
DROP CHARACTER COLUMN : 00:01:42.84
HARD PARSE : 00:00:00.23

Starting with 50 m. I will work on gething Mark's test suite after that.

I show you are about 10 times faster than

OS = SUN SOLARIS 8 (64 bit)/Oracle 9.2.0.4 (64 bit)
CPU = 6 x 900 MHz
Mem = 12 GB

My SGA was about 1GB and I ran with 500,000 rows ~35MB table. The disk
is direct attached and I have not optimized the file layout. This is a
test box.

My laptop, Dell Latitude D600 1.4 Ghz faired a little better than the Sun box.

I am not using any type of parellelism in my tests so the benchmark is
only making use of 1 CPU in my tests.

Sun Box Results with 500,000 rows.

Wed Dec 14 13:21:43 CST 2005 Benchmark Results
v_rows=500000 | v_parse=1000 | v_commit=10000

INSERT APPEND INTO BENCHMARK : 00:00:31.97
GATHER TABLES STATS : 00:00:14.69
CREATE TABLE LOGGING : 00:00:01.98
UPDATE COLUMN : 00:01:10.21
DELETE ALL FROM TABLE : 00:01:42.20
DROP TABLE : 00:00:05.54
CREATE TABLE NOLOGGING : 00:00:01.97
INSERT/COMMIT LOOP : 00:00:04.36
BULK UPDATE : 00:01:40.70
TRUNCATE TABLE : 00:00:01.46
ADD COLUMN NUMERIC DEFAULT : 00:01:19.84
ADD COLUMN CHARACTER DEFAULT : 00:03:17.52
DROP NUMERIC COLUMN : 00:02:16.66
DROP CHARACTER COLUMN : 00:02:02.07
HARD PARSE : 00:00:04.76

I have several databases, versions 8.1.7 through 9.2.0.4, and NLS of UTF8,
US7ASCII, and WE8ISO8859P1.

Currently our OEM server (v9.2.0.7 and UTF8), which is separate from our
database servers, exports a few of our databases and defaults to UTF8. With
the non-UTF8 databases, it has to convert the data.

In order to ensure that the export does not contain any corrupted data, I would
like to specify the NLS to be used prior to export. Is this possible with OEM 9i?

SAN, DAS or NAS ?

From: oracle-l-bounce_at_freelists.org
On Behalf Of Ethan Post

Sent: Wednesday, December 14, 2005 8:13 AM
To: kutrovsky.oracle_at_gmail.com
Cc: oracle-l
Subject: Re: Lets load test

I have a benchmark script I would like you to try, will send you

the attachment in a seperate email. Be interested in seeing the numbers,
it is very easy to run. I will post the script on the web when I get a
chance for everyone.


-Ethan



On 12/14/05, Christo Kutrovsky
wrote:

I have a relativelly fast machine for testing.


It's a 4 cpu XEON 3.6 ghz with 1 mb of cache
16 gb of ram

and a lot of fast storage.

It's running RedHat linux 4 64 bit with 64 bit Oracle

10g and ASM.


Throw your ideas if you would like to see something
tested, it's

sitting idle right now.


I just finished setting it up, so i am running some very
raw tests.


For example, I created a 3.68 gb table with ~40m rows
from

dba_objects. Exact size is:


3'959'422'976 bytes
38'653'952 rows

It's gething full scanned for 5.05 cpuseconds. (482008
logical reads,
no physical).

If anyone wants to see something let me know. There's
500 gb of
storage available.

IBM FastT 900 (new name DS 4500)
SAN Fibrechannel, 2 gbit (tested max 200 Mb/sec)

2 arrays in mirror with 36 gb drives for REDO (not multiplexed)
1 array 8x 73 gb (15k rpm) in raid 5 256Kb stripe

Controller has 1 gb cache, 2 controllers.

All ASM (except archived redo log destinations)

Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group

Christo,

What version of Oracle are you running (10.2?).
do you have the spatial option installed?
If so I'd be interested having you run a script that tests out spatial index build performance.
Also, I'd be interested in some details on your storage subsystem.

thanks,
--Peter

I've posted the storage details in responce to Kevin.

I dont have spatial, i could install it if you provide me with a nice test case.

�

OK then, sure. Test fault injections. Fire
up some heavily modify intensive workload (e.g.,
conventional path loads concurrently, several concurrent
CTAS statements, etc) and drive the I/O up as far as
possible then reach back to the HBA and pull the gbic.
After Oracle melts down, reinstate the path to the
SAN. Make sure ASM and Oracle can come up. Do this
a few times.

Seems' like we're going to try this. I'll let you know how it went :)

�

if you want to see some comprehensive perf
studies of Linux and IBM DS4x00 storage see
these papers:

http://www.polyserve.com/ibm/index.php

the VMDB paper might be of interest

Here's my results with ORABM:

Created with:

orabmload.10g.linux Warehouses 20

Followed by the rest of the loading procedures.

Then running:

/app/oracle/working/orabm/bin/linux> ./orabm.10g.linux 1,2,3,4 200000
---begin sess=1 txn=200000 ORACLE_SID=test64 Thu Dec 15 12:55:04 2005
test64 txn(all)=200000 xn(sam)=159999 t(sam)=170 tps=941 sl=80026(50%)
on=48069(30%) oi=31904(19.9%) end=151205-12:58:37
---end - Thu Dec 15 12:58:37 2005
---begin sess=2 txn=200000 ORACLE_SID=test64 Thu Dec 15 12:58:37 2005

test64 txn(all)=200000 xn(sam)=159999 t(sam)=184 tps=870
sl=80247(50.2%) on=47811(29.9%) oi=31941(20%) end=151205-13:02:27
test64 txn(all)=200000 xn(sam)=159999 t(sam)=185 tps=865
sl=80227(50.1%) on=47981(30%) oi=31791(19.9%) end=151205-13:02:28

---end - Thu Dec 15 13:02:28 2005
---begin sess=3 txn=200000 ORACLE_SID=test64 Thu Dec 15 13:02:28 2005

test64 txn(all)=200000 xn(sam)=159999 t(sam)=197 tps=812
sl=79775(49.9%) on=48028(30%) oi=32196(20.1%) end=151205-13:06:33
test64 txn(all)=200000 xn(sam)=159999 t(sam)=197 tps=812
sl=80176(50.1%) on=48000(30%) oi=31823(19.9%) end=151205-13:06:34
test64 txn(all)=200000 xn(sam)=159999 t(sam)=197 tps=812
sl=80316(50.2%) on=47801(29.9%) oi=31882(19.9%) end=151205-13:06:35

---end - Thu Dec 15 13:06:35 2005
---begin sess=4 txn=200000 ORACLE_SID=test64 Thu Dec 15 13:06:35 2005

test64 txn(all)=200000 xn(sam)=159999 t(sam)=209 tps=766
sl=80171(50.1%) on=47908(29.9%) oi=31920(20%) end=151205-13:10:56
test64 txn(all)=200000 xn(sam)=159999 t(sam)=210 tps=762
sl=79416(49.6%) on=48453(30.3%) oi=32130(20.1%) end=151205-13:10:57
test64 txn(all)=200000 xn(sam)=159999 t(sam)=211 tps=758
sl=79817(49.9%) on=48072(30%) oi=32110(20.1%) end=151205-13:10:58
test64 txn(all)=200000 xn(sam)=159999 t(sam)=212 tps=755
sl=80433(50.3%) on=47922(30%) oi=31644(19.8%) end=151205-13:10:59

---end - Thu Dec 15 13:10:59 2005

Again, this is:

Oracle 10gR2
4x XEON 3.6 1 mb cache (8 virtual CPUs)
16 G RAM

12 Gb SGA
64 bit Linux RedHat 4 - 64 bit Oracle