Performance problem with Job (Flat file to Oracle)

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

Try loading the database from command prompt with the file which is been exported by datastage using Load command. And check the time difference.

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

In other words - Manual Mode.

ArndW · Post by **ArndW** » Thu Mar 16, 2006 10:46 am

If network traffic/collisions is the source, you should see bad performance when you FTP a file (manually) between the machines. Take a 10Mb file and see how long that takes to transfer; a .13 turnaround time is very fast and doesn't point to a network problem.

rleishman · Post by **rleishman** » Fri Mar 17, 2006 8:09 pm

chulett wrote:That must be because you are using the dreaded 'Automatic Mode'. Yuck.

There's a lot of Auto-mode-bashing goes on in this forum. What's that about? I like auto-mode.

[rant]
If your transformation is a bit on the pudgy side, then you can slip an IPC just before the OCI Load without slowing the transform, and load while you transform. With manual mode, you have to serialize the transform and the load.

Manual is only useful when your transformation can easily out-perform the load - not always the case if you have a few hashed files or routine calls. Even then, you still need to compare OCI Load, SQL*Loader across the network, and SQL*Loader locally on the Oracle server. The best choice will depend on the config and load of the network and the two machines.
[/rant]

michaelsarsnorum · Post by **michaelsarsnorum** » Mon Mar 20, 2006 2:15 am

I have tried all of the suggestions above:
- Running in manual mode and then doing the sqlldr part manually. This gave me a transfer rate of about 1000 rows/sec.
- Placing an IPC-stage between the Transformer and the Bulk-loader stage. This gave me a performance of about 600 rows/sec

I tried connecting the file directly to an IPC-stage and then to a local sequential file. This gave me a transfer rate of about 14000 rows/sec which I think is what the HD can deliver.

Since the file is about 200 bytes wide, a transfer rate of 600 rows/sec requires a network speed of about 0.96 MBit/s. The servers have Gigabit Ethernet cards and are both connected to the same 100 MBit switch.

Clearly there is a bottle neck here and my first guess would be the network, but some of my jobs have had increased their performance on this server compared to the old one.

Another problem is that I can't trace performance statistics on the DS-server. The log entry containing the statistics are apperar but all figures (except the row count) are zero.

kumar_s · Post by **kumar_s** » Mon Mar 20, 2006 5:02 am

How about turning on APT_RECORD_COUNT envirnmental variable (Not sure about Server edition)

michaelsarsnorum · Post by **michaelsarsnorum** » Mon Mar 20, 2006 5:05 am

What does this variable do?

kumar_s · Post by **kumar_s** » Mon Mar 20, 2006 5:07 am

Gives out the number of records consumed by each player for the run. (So I am not sure whether this variable is available in Server Edition)

chulett · Post by **chulett** » Mon Mar 20, 2006 7:46 am

Um... no. PX only.

"All figures (except the row count) are zero"? Are you saying that you are looking in the 'Finishing (...)' messages for the active stages in the job log and only see zeroes for the CPU usage and elapsed seconds?

Or have you turned on Tracing during the job run and those entries only show zeroes?

michaelsarsnorum · Post by **michaelsarsnorum** » Mon Mar 20, 2006 7:51 am

When I look at the logs I get the following entry for performance statistics:
TestfAvtalerFraFil_MindreFil_Bulk..CTransformerStage1: DSD.StageRun Performance statistics
Name Percent Count Minimum Average
TestfAvtalerFraFil_MindreFil_Bulk..FilAvtale.AvtalerInn 0 222265 0 0
TestfAvtalerFraFil_MindreFil_Bulk..Hash_KundeKey.SjekkKunde 0 222265 0 0
TestfAvtalerFraFil_MindreFil_Bulk..Oracle_OCI_Load_10.GodkjenteAvtaler 0 222265 0 0
Status.Update 0 66 0 0
SjekkKunde.Derivation 0 222265 0 0
StageVars.Derivation 0 222265 0 0
GodkjenteAvtaler.Derivation 0 222265 0 0

chulett · Post by **chulett** » Mon Mar 20, 2006 7:59 am

Odd... doesn't seem right. What's your O/S and exact version of DS? I'd report this to your support provider and see if it is a known issue or bug.

rleishman · Post by **rleishman** » Mon Mar 20, 2006 5:03 pm

So Auto mode (with IPC) writes at 600/s, and Manual at 1000/s. This tells me that the maximum throughput of the transformation is 1000/s, but that the maximum write-rate slows that down to 600/s.

Since 1000/s writes to a local flat file, that eliminates Oracle and the network. Your initial post mentioned a rate of 2000/s. No matter what you do to improve the network and Oracle, the transformation will continue feeding them data at no more than 1000/s. You need to look locally on the DS server for the problem.

Have you tried mucking about with the row-buffer size?

michaelsarsnorum · Post by **michaelsarsnorum** » Tue Mar 21, 2006 12:34 am

The problem arises when I try to send data to another server... As I stated above, when writing to a local file inestead get a transfer rate of 14000 rows/sec.

Writing the SQLLDR datafile when using bulk loader is about 6000 rows/sec. The transfer of these data with sqlldr gives med 1000 rows/sec.

This doesn't strike me as a problem with the performance on the DS-server.

ArndW · Post by **ArndW** » Tue Mar 21, 2006 2:09 am

What speed does a FTP from your DS server to the remote machine achieve?

rleishman · Post by **rleishman** » Tue Mar 21, 2006 3:42 pm

Apols. I read your earlier report of 14000/s as being a simple read/write from flat file to benchmark your disk - not as a throughput figure on the job in question.

Re the quoted figure of 1000/s using sqlldr: are you running sqlldr across the network? Or locally on the Oracle server after an FTP? It would be good to have a comparison.

Also as ArndW suggested, an FTP benchmark would be worthwhile.