Re: write performance - broad generalization
Date: Fri, 3 Apr 2009 12:22:14 -0700
Message-ID: <a9c093440904031222p5bd46516uaf240070d8bab292_at_mail.gmail.com>
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-lReceived on Fri Apr 03 2009 - 14:22:14 CDT