Performance problem with Job (Flat file to Oracle)

michaelsarsnorum · Post by **michaelsarsnorum** » Thu Mar 16, 2006 3:31 am

I have a problem with some jobs that have been migrated from DS 5.2. Some of the jobs run faster than on the old version, but some are slowed fown by a factor of 5-10.

One job reads 13.000.000 records from a flat file, performs a hashed-lookup (preloaded to memory) and insert into a table in a oracle database (no indexes, array size = 100). When first migrated the job ran with 200 rows pr. second and by chaning the values mentioned above I have managed to increase this to 800 rows/sec. The old version had this job running at almost 2000 rows/sec.

When using top to monitor the load on the DS-server and the Oracle server they have 85% and 99% idle respectively. Does this mean that I have a problem with network congestion? Or is the DS-server radically misconfigured?

On the old server I had a case where a job that read from a flat file and inserted into a table on the Oracle database without any indexes took about 1.5 hours (7.000.000 records). By using SQL-loader (running on the DS-server) the same file was loaded into the DB in about 5 minutes...

My main qustion is how can I locate the bottleneck in scenarios such as the ones described above?

Any other perfomance tips are also apreciated.

m.

ArndW · Post by **ArndW** » Thu Mar 16, 2006 3:43 am

Make a copy of your load job and write to a sequential file (/dev/null) to see what speed you job is capable of without writing to Oracle. That way you will be 100% certain that the loading to Oracle is the culprit in your case. I am assuming that the Oracle database is on a remote system from your description.

What stage are you using to load into Oracle? With inserts you could use the bulk load stage which equates to the sql loader.

kumar_s · Post by **kumar_s** » Thu Mar 16, 2006 3:43 am

How about the Commit Interval for the current job?

michaelsarsnorum · Post by **michaelsarsnorum** » Thu Mar 16, 2006 3:45 am

Set to zero, I commit at the end of the transaction.

MaheshKumar Sugunaraj · Thu Mar 16, 2006 4:27 am

Hi,

If you plan to change your design/stage then If your just doing direct inserts, You could use ORACLE BULK LOADER stage, which will perfom much faster.

I had a similiar issue when I tried using the Native Oracle OCI plug in to insert data into the Oracle tables and later I changed to ORACLE BULK LOADER stage which was much faster.

With Regards
Mahesh

michaelsarsnorum · Post by **michaelsarsnorum** » Thu Mar 16, 2006 5:06 am

It seems this is a problem with network congestion/infrastructure.

I changed the job to write directly to a flat file instead of the database. This gave me 5600 rows/sec. When using the database the server's load was almost non-existent according to top.

I'll see what Bulkloader can do.

michaelsarsnorum · Post by **michaelsarsnorum** » Thu Mar 16, 2006 5:27 am

Bulkloader was not as easy to figure out as I thought....

On my DS-server (7.5.1A) I have two different Bulk loader stages.
One is called Oraociblk (Oracle 8.1 Bulk Loader). This one has loads of parameters to be set.

The other is ORABULK (Oracle 7 Load), this is the one that corresponds to the Developer's Help files. But it does not have any parameters that specify which database to load the data into or which user name it's to use.

The Oracle version used on ordinary OCI stages is Oracle 9.

chulett · Post by **chulett** » Thu Mar 16, 2006 7:44 am

So, do you have it figured out? OraOciBlk is the one you want, probably in Manual mode. We're using it with both 9i and 10g databases.

michaelsarsnorum · Post by **michaelsarsnorum** » Thu Mar 16, 2006 7:49 am

I can't say I have control of this yet. I developed a job with an Oraociblk target. This had about the same performance as the ordinary OCI stage. I also tried this with and IPC-stage to divide the reading from the flat file and the writing of the bulk stage into two different processes, no performance gained, although with the latest configuration I could see that there was some IO-wait reported by top on the DS-server.

The DB-server still ran with virtually no load. Right now I'm testing this with DS-performance reporting tool to see if I can find any evicence that the bottleneck is the network (as i suspect).

DSguru2B · Post by **DSguru2B** » Thu Mar 16, 2006 7:56 am

Try pinging the server and see what are the results. If the T.T is too much and there are many delays, then definetly its your network.

chulett · Post by **chulett** » Thu Mar 16, 2006 8:01 am

michaelsarsnorum wrote:I developed a job with an Oraociblk target. This had about the same performance as the ordinary OCI stage.

That must be because you are using the dreaded 'Automatic Mode'. Yuck. Switch it to Manual and use the stage to just generate the files you need. Then script a plain old sqlldr direct path bulk load. Wham. Done.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 16, 2006 8:22 am

Check that nothing has changed in the Oracle table too. If "they" have added another 20 constraints and 15 indexes, naturally any load process will be slower.

michaelsarsnorum · Post by **michaelsarsnorum** » Thu Mar 16, 2006 8:25 am

There are no indexes and no constraints at all on the target table. The table is generated from a template and indexes are created after the data load is completed.

kumar_s · Post by **kumar_s** » Thu Mar 16, 2006 8:47 am

So as suggested, what is your network traffic?
Have you tried to ping with your server, are you able to get the responses less than 10ms (which is most likely the accecptable value).

michaelsarsnorum · Post by **michaelsarsnorum** » Thu Mar 16, 2006 8:50 am

The pingtime from the DS-server to the oracle server is 0.13 ms.