Oracle 11g serious slow performance issues

[Bill McCracken]

Hi,

Some background:
I have recently been successful in moving our company (Sprint Nextel) into using EA as their primary arch tool.  We have just begun the initial deployment of 32 seats and targeted our backend using our production 11g Oracle db (using a grid).  During the pilot phase of our search for a tool, I used a production MySql server instance since it was readily available.

What we have found is that the performance using the Oracle schema (and patch file) from EA for the Oracle db is pretty much a show stopper for our deployment.  We need and want to use Oracle as our backend.  We have major investments to produce 5 9s production support levels.  However, the response time is almost a 10x factor slower than the MySql db.  For example, simply loading up the model (lazy load / 512mb config) on any client, is somewhere between 2 to 5 minutes.  Running a simple report for a perhaps 10 elements (requirements and a diagram) is about 5 minutes.

We've had our DBA's look for issues, see none on the DB side.

Key questions that we really need help with:
a) Is anyone else running an Oracle 11g instance successfully with EA 9.3?
b) If so, what tuning did you need to do to obtain acceptable response times
c) Could you share your experience with us.  Again we are in a roadblock scenario since the DB performance is so poor, it causes the use of the tool to be extremely unattractive.
d) No, we cannot run locally and then upload at later times.  We are expected to run in a federated model.  Single schema backend DB, multiple projects in the schema.

We've tried everything, and don't see anything visible on the ODBC traces or Oracle traces.  It's just slow.  I have even tried switching to other claimed 'faster' ODBC drivers.  No luck.

The irony here is that the MySql server works beautifully.  Performance is very snappy despite it being a small Production box.  I was expecting the reverse that MySql small prod box would be slow, and Oracle would blow it away.  We cannot deploy on MySql unfortunately.

Any help appreciated.  Thank you very much for your time to read this.

Bill McCracken
Sprint Nextel
[email protected]

[Geert Bellekens]

Bill,

One of my previous clients is running EA on Oracle (I think they're on 11G too, but I'm not sure). I've helped them with the transition from Rational Rose back in 2007, so they've been working in this configuration for about 5 years.
I think they have about ~50 users.
Just so you know that it is in fact possible.

Now to solve the problem, I can only tell you that EA does hundreds of small SQL queries per second, so the database should be tuned for for that (not for large heavy transactions).
Also make sure your network is fast, because all of those queries need to be sent to over the network to the DB server, and back.

And last but not least, don't hesitate to contact Sparx support. They are usually very helpful with issues like these. I'm sure they will be able to give you some pointers.

Good luck

Geert

[Bill McCracken]

Geert,

Thank you very much for your help.  I did find an ODBC tracer that I could see the EA SQL calls going through the Oracle odbc driver.  I think that the schema needs better work for Oracle sites and the repeated 'SELECT * from tablename' when the project is loaded objects are requested to work on are not helping performance at all.  These are extremely inefficient compared to requesting the real field data items only when the front end app actually needs the data.

Our DBAs indicate the requests are coming in and out so fast they can barely 'see them' process.  Our db servers are in different geographic cities for business resumption reasons.

To give an idea of the experience, launch the database, and loading our model will take approximately 2-4 minutes to just be able to click on an object in the project library.  Clicking on a library element and pulling up properties is 10-30 seconds.  Running any kind of RTF report (canned supplied by EA), is a click and go get some lunch ordeal.

Does anyone know if there is a debug option for this tool to turn on full tracing and dump it to a file?

I also noticed a strong inefficiency in the schema itself, with many key fields far down into the table, and many behind CLOB type fields.  The schema really needs to be normalized and I think performance across the entire tool when using a RDBMS would significantly go up.

We were hoping for some tuning advice on a 11g server, but it's starting to look like network/EA software latency potentially with the combination of a poor Oracle odbc driver is really hurting us.  Do you know anyone that has built a OCI Oracle extension for db access to make a straighter db call?

The only tuning that we could see out of explain plans and looking at other server stats was to increase the cursors count limits.  I'm also curious if the tool is somehow turning on db hints, which it should not be.

I'm fairly frustrated, as this is a wonderful product but we are using the top database in the market of Oracle, the EA product looks very negative and I'm afraid our full roll out efforts are going to cancelled when people see how slow the response it.

Our current start up main repository project stats are roughly 18k elements.  A small to mid size effort that will continue to grow by a factor of 10 fold over the next 12 months - if we ever get this db performance fixed.  We are right in the middle of our core team setup / launch process trying to move our entire Arch staff into this tool environment...emphasize trying.  We were ready to pull the trigger on the license orders until we ran into this major problem.

We are trying a last resort of using the WAN Optimizer, and want to get it on the same subnet/geo-location as the Oracle 11g server but I have little confidence that this is going to resolve much.

This product is going to be limited to smaller shop installs unless there is a strong focus on fixing Oracle based installations quickly.  It's frustrating.

Thanks as always for your kind responses.

[Geert Bellekens]

Hi Bill,

Although your remarks on the database design are probably justified, that is not something that is very likely to change in the near future.
I think the database design hasn't changed in about 10 years or so.

What is critical however is that the database is located on a local fast network. Remote databases don't really work well for EA.

And make sure you get support involved in this. They surely will be able to help.

Geert

[Sunshine]

When investigating any problem you need to take into account all the factors and figure out the root cause. It sounds like you've investigated the DB and found nothing so far so may be have a look at the network. May be apply a Kepner Tregoe problem solving technique to help you figure out what is the root cause. If you've debugged windows apps before you'll know there are a load of performance counts you can enable on the client machine. Try having a look at that and create a log of using the MySQL DB and compare it to similar activities using Oracle. If you find some evidence its the network latency/lag to the DB then consider re-architecting your deployment. For instance consider having a local DB which replicates to your offsite DB.

I've known a colleague in another company use databases on remote sites and the performance was awful. They moved to having a local DB and the performance increased dramatically. Think it was MS SQL they used.

Well hope that helps - good luck

[GMF_TimB]

We are encountering the same issues in a 10g environment.  Was there ever a resolution that someone would like to share?

Thanks, as always,
Tim B.
GM Financial

[OpenIT Solutions]

Hi,

One comment that i didn't see in the orginal thread - performance issues are typically down to network latency - the WAN Optimizer can help a little here. The biggest help we found - if you have users across the globle connecting via their desktop to a central Sparx server - is to use something like citrix / desktop on demand and co-locate your citrix server in the same locale / data center as your rdbms.

Regards,

Jon.

[Helmut Ortmann]

Hi,

in my experience you can work with Oracle. There are problems specific to Oracle which you don't encounter with other database. For example: Oracle don't allow SQL queries with more than 1,000 items in an In (***) statement. (I hope I remember the 1.000 correctly).

Beside this:
Consider locale repositories with version control. If you have a bad connection it might help you.

Besides all:
Some of our users convinced the IT to change from ORACLE to SQL Server. It was not easy.

Helmut

Helmut

[Nizam Mohamed]

Just re-emphasizing some previous suggestions.

• Try Using WAN Optimizer, it speeds up things on regular usage
• Consider using Version control / Repository combination to facilitate remote access [Was your MySQL setup a very similar one or was it a a pilot run in a local n/w?]