Re: Q: SQL*Loader Performance

From: Murray Kaiser <ae873_at_cfn.cs.dal.ca>
Date: Mon, 27 Mar 1995 20:43:27 GMT
Message-ID: <D649KG.CLD_at_cs.dal.ca>

Steve Butler (sbut-is_at_seatimes.com) wrote:

: On Mon, 20 Mar 1995, Murray Kaiser wrote:
: > With both databases up and running, I get the following statistics:
: > Elapsed time was : 00:29:44.19
: > CPU time was: 00:16:26.82
: >
: > With only one database up and running, my statistics are:
: > Elapsed time was: 00:41:00.58
: > CPU time was: 00:38:34.95
 

: 1. You were loading the SAME database each time?

1. YES

: 2. Physical files were at the EXACT same physical location on each drive?

1. YES

: 3. Was one load using DIRECT path and the other didn't?

1. Neither. Identical SQL*Load job for both runs and did not use DIRECT path.

: I've seen examples of file placement causing a doubling of run time. It
: was traced down to the rotational delay on reading data off the drive.
: For the fast run, everything was positioned such that the data was almost
: ready to flow past the read head when needed. For the slow run, it has
: just gone past the read head and the system had to wait for the
: rotational delay of the disk.

1. The files were at the same physical location - I simply reran the job with the same input file and tablespace.

: These are the types of things that a good pre-fetch caching system are
: supposed to fix. Perhaps one of the other users was managing to get the
: blocks your load needed into memory before your loader needed them.
: Thats a bit farfetched, but....

1. I think something like this must have happened because now I cannot duplicate the shorter run at all. I have rerun this several time over the last week with both databases up, with one database (target database) up, busy machine (other users), quiet machine (nobody else on the machine and no batch jobs running) and I cannot get the shorter run time or shorter CPU time again. Instead, all SQL*Loader runs are consistently around 36 to 38 CPU minutes and 38 to 41 minutes.

Thanks for some thoughts Steve.

Murray Kaiser                  | Usual gutless disclaimer..
Nova Scotia Power              | Opinions are mine and not necessarily

(Murray.Kaiser_at_NSPower.NS.CA) | shared by my employer Received on Mon Mar 27 1995 - 22:43:27 CEST