Re: write performance - broad generalization

I agree with Greg, you need to test multiple concurrent write to get maximum performance. Multiple controllers and multiple paths to the disk are only used when you have several IOs to be written at the same instant.

Note that this is a pure sequential write performance test. It is very different then what DBWR will write.

If you want to perform the same read test, you can use rman's "backup as copy validate" and setup multiple default channels.

Things to be carefully about:

- direct_io
- async_io
- shared metavolumes on the SAN and activity on other machines.

The times you get should be very consistent. Especially single thread. If it changes, then follow up on some of the above points.

-- 
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/

On Fri, Apr 3, 2009 at 3:22 PM, Greg Rahn <greg_at_structureddata.org> wrote:


> Creating the data files for a tablespace is a single threaded

> operation, meaning it creates one file, then the next if there are

> multiple files listed in the statement, so this in itself is probably

> a very poor measure of the max write performance for Oracle.  It is

> probably representative of how much a *single* process can drive, but

> probably far from what the system is capable of.  If you really want

> to max out the throughput using TS creation, you need to create a TS

> with 1 data file, then in multiple, concurrent sqlplus sessions do a

> "add datafile".  This will result in multiple processes each writing

> out a data file.  In addition, multiple sqlldr sessions directly

> loading is also a poor I/O write test.  In most system, the bottleneck

> will easily be the CPU well before the I/O writes are the bottleneck.

> If you want to drive max I/O, you need a lower level tool like dd,

> Iometer or Oracle Orion.

>

> The characteristics of the file system as well as the storage are very

> important when it comes to comparing writes.  Predominately one needs

> to understand if the writes are buffered by cache or file system

> virtual memory.  This will impact the time to write the file.  Indeed

> you *will* have to go into specifics of the file system as well as the

> underlying storage in order to fully understand the differences.

>

> On Fri, Apr 3, 2009 at 5:18 AM, Michael McMullen <ganstadba_at_hotmail.com> wrote:

>> Whenever I get a new server or even compare write performance between

>> servers, I just create a 10GB tablespace (not temp). I time it and I think

>> it gives me a kind of max write performance (for oracle), no other work is

>> going on, just the one tablespace being created.  Real quick & dirty test.

>> Yesterday I got a brand new dev box and it took ~7 minutes for a 5GB

>> datafile. A 10GB datafile on another box that I recently set up < 1 minute.

>> Top of my head I think my new dev box is a dog. I don’t need to go into

>> specifics on the file system they gave me or the underlying storage. Just

>> wondering if I’m way off base. I’ll have to go through my docs. I think I’ve

>> done test with multiple sqlloader sessions doing direct loading to see if my

>> throughput matches my simple test but I’m not sure.

>

>

> --

> Regards,

> Greg Rahn

> http://structureddata.org

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>




-- 
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
http://www.freelists.org/webpage/oracle-l