R98403 Performance

Hi List,

I'm currently running R98403 to copy Central Objects from PY811 to PD811. It is running very slowly whereby it takes around 25 seconds to insert 500 rows of data. I have always run this on Microsoft SQL Server and it is much much faster than this.

I'm currently on E811SP1 8.95.A1, Oracle 10G and Sun Solaris Enterprise Server. Database is already on NOARCHIVELOG mode. It is also running on a strong Enterprise server which is a V440 UltraSparcIII1 with 8GB RAM. Deployment Server(running the report) is on a 2GHz AMD Opteron with 2GB RAM. Nothing is running at the background.

Anything I could do to speed things up?

Thnks

Rgds,
Samuel Liew

What's most important is to determine what is the cause of the slow performance. While you do the copy operation, which is taking more CPU time the client, server, or database?

Investigate the machine that takes the most CPU during this copy. If your database is essentially sleeping, then the problem is somewhere else.

Consider these causes:

Always check to see that the jdedebug.log is off (i.e. Output=NONE in the jde.ini)

You could try to drop indexes before the copy and build indexes after the copy.

Good luck,

Chris

Where are you running the R98403?

When the R98403 is run, the machine where it is running essentially copies the record from the database server, down to that machine then copies it back to the dB server to the other database. This makes the whole process very dependant on the network between the R98403 machine and the database.

I use a real server (2 CPU x 2G) as an admin/egen machine. This machine sits in the data center and runs W2K3 and I use RDP to connect to it to run the fat client and egens. It has a 2GB pipe between it and the database server and is very fast. My copy rates on an R98403 before I was able to get this machine built were around 100 records/second between databases on seperate database servers (DV->PD). This was a typical desktop (1 CPU/1G) on a 100MB Full connection. The copy rates using a strong machine with the 2GB etherchannel connection are now in the 500-600 records/second range.

FWIW, using DTS nets a 2500 records/second copy rate.

Look very, very carefully at the network between the dB server and the machine performing the R98403, particularly making sure the duplexing matches.


Oh yeah, once you get the records copied run ANALYZE TABLE owner.tablename COMPUTE STATISTICS in SQLPlus.


[ QUOTE ]
Hi List,

I'm currently running R98403 to copy Central Objects from PY811 to PD811. It is running very slowly whereby it takes around 25 seconds to insert 500 rows of data. I have always run this on Microsoft SQL Server and it is much much faster than this.

I'm currently on E811SP1 8.95.A1, Oracle 10G and Sun Solaris Enterprise Server. Database is already on NOARCHIVELOG mode. It is also running on a strong Enterprise server which is a V440 UltraSparcIII1 with 8GB RAM. Deployment Server(running the report) is on a 2GHz AMD Opteron with 2GB RAM. Nothing is running at the background.

Anything I could do to speed things up?

Thnks

Rgds,
Samuel Liew

[/ QUOTE ]

Not sure how much it will speed things up, but you could also split the R98403 load among several machines. Machine 1 could run your biggest table, Machine 2 could run the 2 biggest, etc.

I'm running the R98403 from the Deployment Server which has a single 2GHz CPU and 2GM RAM. CPU and RAM usage is very minimal from checkiing the performance counters both in the DB server and also Deployment Server. Actually nothing is running on both servers apart from the Central Objects copy.

Network interface is only 100Mbps but it shouldn't account for such a slow performance. A check on the perfmon on Deployment Server does not indicate any congestion on Network Queue. And jdedebug.log is not turned on as well.

I'm not really familiar with Oracle DB. Is there any tuning which needs to be performed to enable faster Bulk Inserts?

Thanks!

Rgds,
Sam

What is the network duplexing set to on both the Deployment and database server?



[ QUOTE ]
I'm running the R98403 from the Deployment Server which has a single 2GHz CPU and 2GM RAM. CPU and RAM usage is very minimal from checkiing the performance counters both in the DB server and also Deployment Server. Actually nothing is running on both servers apart from the Central Objects copy.

Network interface is only 100Mbps but it shouldn't account for such a slow performance. A check on the perfmon on Deployment Server does not indicate any congestion on Network Queue. And jdedebug.log is not turned on as well.

I'm not really familiar with Oracle DB. Is there any tuning which needs to be performed to enable faster Bulk Inserts?

Thanks!

Rgds,
Sam

[/ QUOTE ]

Hi Samuel,

Chris is right:
[ QUOTE ]
You could try to drop indexes before the copy and build indexes after the copy.

[/ QUOTE ]

Dropping indecies before bulk record loading, copying and after building indicies has a big performance advantages on all platforms on all databases.

The reason is trivial:
Without this, the database has to maintenance the indicies per records one by one - which can require many processing time and hard disk operation.

Building indices after bulk load will be much more faster, because it is optimized starting from scratch.

Regards,

Zolán

Thanks for the replies. The switch is on 100Mbps Full Duplex mode. On the R98403, I have set it the option to Re-create tables. Hence, it will create the table, insert data before creating the indexes. So, the index is only created after data is inserted.

I'm currently still running R98403 for Central Objects, which has already crawled for 2 days... phew! But this is the 1st time I'm running it on Oracle. I was expecting it to be faster compared to Microsoft SQL Server. I do think that it has to do with the tuning of this DB. The I/O is showing very high activity in the EM. Is there any guide on Oracle tuning for JD Edwards?

I just ran R98403 for table F98306 parallel with F98741. It took 3 hours just to run F98306! But what surprises me is the size of the index, which is 1.7GB for F98306_0 and 1.5GB for F98306_2. Maybe that's the reason it took so long.

Hi Samuel,

Can you monitor, that how many memory used by Oracle while copy is running and how many is free, unused?

Though I am not a tech, but I have heard, that there are a multi-step tunning to force Microsoft operating system enable to use more meory for Oracle.

Regards,

Zoltán

Check the configuration for the redo logs and the rollback segments but I can't believe any but the most ill-installed database would perform like this. I have to ask: if you are working for a customer, do they have an Oracle dBA? Do they understand how much work keeping an Oracle database running is?

For rollback segments, create a large rollback segment (~1G or so) for the tablespace you are working with. Take the other rollback segments offline for that tablespace. For the redo logs make sure you have archive logging turned off. I don't recall how to do it but to disable logging of index creation and population use NOLOGGING. I don't even know if this will work with the R98403.

One last time, please humor me and report the duplex setting on 1) the Deployment Server 2) the switch port it goes into 3) the switch port that connects out to the switch the switch the database server is on if not on the same switch 4) the switch port the database server is on 5) the database server. In short, check the duplexing on each input and output port of each component in the path between the R98403 machine and the database server. I realize that I am fixating on the network stuff but the only things I have seen to make R98403 and package builds behave this atrociously are network duplexing and anti-virus.

Have you tried running the R98403 on a workstation? You need to start trying to eliminate possibilities. I would not let the R98403 run long ff you see that the copy rate stinks. This is just indicative of something seriously wrong and even if you end up finishing the R98403 the trouble will always be there. I would start the copy, check the rate and if it is not what it should be, kill the process and try some fix....lather, rinse, repeat.






[ QUOTE ]
Thanks for the replies. The switch is on 100Mbps Full Duplex mode. On the R98403, I have set it the option to Re-create tables. Hence, it will create the table, insert data before creating the indexes. So, the index is only created after data is inserted.

I'm currently still running R98403 for Central Objects, which has already crawled for 2 days... phew! But this is the 1st time I'm running it on Oracle. I was expecting it to be faster compared to Microsoft SQL Server. I do think that it has to do with the tuning of this DB. The I/O is showing very high activity in the EM. Is there any guide on Oracle tuning for JD Edwards?

I just ran R98403 for table F98306 parallel with F98741. It took 3 hours just to run F98306! But what surprises me is the size of the index, which is 1.7GB for F98306_0 and 1.5GB for F98306_2. Maybe that's the reason it took so long.

[/ QUOTE ]

There doesn't seem to be a clear cut solution for this problem, but I'm running into exactly the same thing trying to copy from UDB to Oracle, the copy is rediculously slow, whereas it's much faster when copying from UDB to UDB, which clearly makes me think it's an Oracle issue or that there's a translation process taking place within the R98403 that's slowing it down. Any other ideas or suggestions?

Thanks,

Phil.