E811SP1 Performance on Oracle 10.1.0.4

[samuel]

Hi,

We currently implemented a 3-tier E811SP1 8.95.P1 on Oracle 10.1.0.4 running on Solaris 9.

Performance is very slow compared to Microsoft SQL Server, especially in areas like performing Full Web Generation (takes more than 24 hours!), updating ESUs, and now, installing Update 1(which has been running for 18 hours now).

Performing Full Web Generation, with of without users generate the same amount of time... So I don't think it's much due to the traffic or server load.

For disk array, the disk is arranged in 0+1 array(only 1 array for all tablespaces). Yes, it's a bad design... but does it affect performance that badly? It's less than twice the speed of Microsoft SQL Server....

Is there any way to improve it?

Thanks

Rgds,
Samuel Liew

 

[Alex_Pastuhov]

It's typically slower than SQL, certainly for any BLOB operations, so this can be normal.

Disk performance does affect it, but probably not that much.

Tuning can usually improve BLOB operations only marginally - stats, reorgs, better disk, memory, etc. can give you maybe approx. a 20% boost, based on my experience.

I'd also be interested to hear from anyone who can get a better improvement in this scenario...

 

[cmanderson]

We're running 8.11 SP1 with Tools Release 8.96_C1. Oracle database version 10.2.0.2 running on an IBM p570 with AIX 5.3.

Production is setup with Tier 1 SAN disk (HP 1024) in RAID-5. DEV and QA are attached to Tier 2 disk in a similar configuration.

A full direct db connect web gen in this config takes about two and a half hours (regardless of which environment we gen for, dev, qa or prod) when run in parallel on a dedicated HP DL360 with dual 3.6GHz Xeon's. It can take an extra 45 minutes to an hour if the recommended table/data dictionary gen is run serially prior to the parallel gen of apps, reports, etc.

Something is surely wrong, likely in more than one or two areas, for it to take more than 24 hours in another configuration.

When I installed 8.11 SP1, we were running Oracle 10.1.0.5 and Tools Release 8.95_H1; performance wasn't any better or worse as far as web gen's, so you should not consider an upgrade in the hopes that you will solve all of your performance problems.

 

[Alex_Pastuhov]

The power you have on the generation client would certainly help quite a lot too ;-)

 

[samuel]

Hmm... my Web Gen is just running on a PC with P4 3.0GHz 1.0GB RAM. I always run it sequentially since it's on a single processor. But I've run sequential Web Gen on other implementations with SQL Server even on a lower spec Web Generator. The most it takes me is around 4-5 hours.

According to the Oracle DBA, there's nothing he can do to tune the DB anymore. Connection is on dedicated server & Stats are recalculated daily. Actually, the load we monitored is not high on the DB side. DB Server is on a SunFire V440 Dual Processor with 8GB RAM.

What could be the cause....?

 

[wainwrr]

24 hours is huge, we've seen an increase in time for our 8.11 rig compared to the Xe rig but nothing like that _ maybe 3 hours ging to 4. Things to look at :-
With your configuration are you getting contention for the disks between read/write of the tablespace and write or the archive logs? A quick scan of the instance log file would indicate whether or not your getting archive log waits.
It's worth checking you have all the indexes required (as opposed to the ones supplied by default). Your DBA may be able to see long running transactions that do increasingly long full table scans as the generation progresses. The Gen process as far as I know ends up trying to do an update of the generated objetc record for each object. You might get an improvement by truncating the tables before generating (although that has an adverse effect on the user experience!!)

 

[cmanderson]

More than enough - total overall CPU utilization never exeeds 25% across processors during a full gen...this is why I've been using this server for several other concurrent functions in the development architecture. Might as well not waste the hardware.

 

[cmanderson]

Here's an idea for you - install a temporary (fully licensed of course!) Oracle instance on your web gen machine (you may want to add some RAM before doing this, otherwise tune the generator to max at 256M and limit the SGA on the Oracle instance.) *** You could be skewing the results of the test by not putting this test Oracle instance on a more capable machine, but it does not have to be a "real server" if you have adequate hardware and it is tuned appropriately.

Put the F989998/F989999 tables in there on two disks in RAID-0, on individual disks or the same really fast disk if that is all you can muster. Create a datasource in JDE and point to the temporary db instance. Point your jdbj.ini to the local Oracle instance and create an OCM mapping for a gen user in JDE. Don't point your web servers to the new datasources, this is strictly for testing purposes.

The idea is to take the network out of the equation, remove any chance that there is db contention at the spindle level. You will also have some ammo to take back to the DBA. The DBA should be making hardware recommendations as well as tuning the software portion of the RDBMS...

 

[samuel]

That should be a good idea. At least we can do a direct comparison. Will update this thread once I get the test up. Thks!