Sparx Systems Forum
Enterprise Architect => Bugs and Issues => Topic started by: MatthiasVDE on March 01, 2016, 12:11:44 am
-
Recently we started to use our EA12 with an Oracle repository. A major problem is that it is almost not workable because of the slowness of the system. Is this a specific problem for a connection with Oracle, or is it just a setting in EA?
-
Hi Mathias,
For some reason Oracle as a backend for EA seems to have some performance issues.
I'm not sure why, but I've heard it from several clients in the past.
I usually advice to move to SQL Server or MySQL.
While still on Oracle you should probably try to get it optimized to handle many very small queries. When EA is working is executes up to a few hundred tiny queries on the database. At that moment the bottleneck becomes the number of parallel queries the server can handle, and the network bandwidth to the server.
You will probably notice that in terms of processor, the server is doing hardly anything at all.
Geert
-
Can I do the optimization in EA?
-
Not really. The only option is the WAN optimizer (if that applies). Avoid large diagrams. Use the cheap Oracle option called MySQL...
q.
-
I'm told that a very significant optimization when using Oracle repositories is to connect using OLE DB instead of ODBC.
-
Can I do the optimization in EA?
No, I meant optimization on the Oracle side.
I'm not a DBA, and certainly not an Oracle DBA, but I'm sure there about a gazillion options to play with on the Oracle server.
Geert
-
I can confirm that over the last 8 years every time an organisation has used Oracle for Sparx EA we've had performance issues. You need a really good Oracle DBA to tinker with the DB to get Oracle to perform. The other performance bottle neck can be the network and its some times difficult to get the Network and Database folk to work together to find the problem. Best bet is to use MSSQL Server or MySQL if you can to see if that works better. If it does then you know it was probably the Oracle DB. On the other hand if you're stuck with Oracle get the DBA to optimise. If the DBA can't make it perform then may be it might be the network or maybe the DBA isn't a good as they claim.
Problem solving techniques like Kepner Tregoe can sometimes help find the cause working across multi disciplines like network and database folk.
-
In EA the SQL layer is not well written. Maybe they tried to "specify" some specialisation however they made wrong work.
They not use parametric sql, the parameters placeholders just replaced the value. Thats way Oracle must reparse every same sql hundreds, thousands (depends your size of model), and cannot use the statement cache.
I found a "bug" and slow execution. The problem is the classifier_id. In some table (eg. t_attribute, t_operation) the classifier_id is not number(10) but also varchar2(50 char). EA generate sql looks like "classifier_id = to_char(t_object.object_id)", thats way Oracle can't use index, because they need convert object_id and full table scan occurs.
I started a github project where gathering tips, what I found helpful to increase performance while Sparx fix EA with Oracle.
https://github.com/onlyonce/sparx_ea_oracle_tuning (https://github.com/onlyonce/sparx_ea_oracle_tuning)
-
Hi onlyonce
How much difference did the tuning scripts in the GitHub make?
I am using Oracle as the base repository across our local network and the latency is quite bad. It takes about 2 minutes to open the repository project and then another couple of minutes to open each diagram. If I run any queries in SQLPLUS on my machine, the response is very fast. If however I connect via Sparx using the OLE DB driver from the same machine, the response is very slow. Sparx performance was fine till we doubled the landscape diagrams we had. We currently have 260 Application Components, 370 Data flows and each application component/data flow has 25 tagged values. I don't know how this compares with other landscape repositories but to me it doesn't seem that big.
In GitHub you have identified the scripts below:
t_attribute_tuning.sql
t_connector_tuning.sql
t_diagramobjects_tuning.sql
t_object_tuning.sql
t_operation_tuning.sql
t_operationparams_tuning.sql
... and
trg_tuning_ea_afterlogon.sql
Do you suggest I try running all of them and see what the result is like or try one at a time?
If anyone else has any advice, please do share with me as I need to make this repository available to our Data Architects fairly soon.
Many thanks in advance.
-
Hi Knightrider,
If anyhow possible move to another database.
Everyone, (and I mean literally everyone) I've spoken to who had Oracle as backend had serious performance issues.
The best performers seem to be MySQL followed by SQL Server.
Geert
-
Many thanks for your reply Greet.
I have had the same response from many users and luckily we are at the beginning of setting up the repository so we can change the database.
Am I right in thinking that there are no other functional limitations to Sparx whether we use MySQL, Oracle or SQL Server other than the performance issue discussed in this thread?
-
Many thanks for your reply Greet.
I have had the same response from many users and luckily we are at the beginning of setting up the repository so we can change the database.
Am I right in thinking that there are no other functional limitations to Sparx whether we use MySQL, Oracle or SQL Server other than the performance issue discussed in this thread?
Yes, that is exactly right.
Geert
-
Just thought you'd like to know that we have moved from .eap file to Oracle 11g Database because Oracle was already on our estate and we had performance problems running over the WAN between regional offices (as Sparx predicted we would).
Consensus amongst our users is that performance is now much improved. Using EA12. Our next move is likely to be to V13 which suggests that it would be even quicker as it uses indexes. If we still get performance issues as we grow the user base and dataset size we are likely to use Citrix as its already on our estate.
-
EA13 doesn't use indexes any more than EA12.
The release of EA13 coincided with a release of updated schemas that included additional indexes that will work with any version of EA.
-
We came accross the same case. It was because of a bug in Oracle which is addressed in 11g and 12c.
The EA executes many queries that includes ALL_SYNONYMS view. Execution time of the queries are expected to complete in milliseconds but they last more than a second. This small differences makes EA to performance badly in Oracle.
The affected Oracle versions are: 11.2.0.1, 11.2.0.2, 11.2.0.3, 11.2.0.4, 12c release1
The problem was gone after applying the PSU.
Details are on MOS notes: 9371529.8 and 22113854.8
The Oracle software we use is version 12.1.0.2 and we applied the PSU 12.1.0.2.161018
It is very possible for any 11g,12c Oracle customers run into this bug.
Yusuf,