Performance drops with Oracle 11gr2

Question

Hello,

We have an ArcGIS Server 10.1 on which we mounted several mapservices that only uses query layers to Oracle databases (so no SDE involved and all geometries are SDO). A client application was developed with ArcGIS WPF api 2.4 that consumes these services to display maps as well as running query tasks and identify tasks.

Recently, our database servers were migrated from Oracle 10g to 11gR2. Immediately after this migration we experienced a rather spectacular rise in response time from the ArcGIS Server. The identify task seems to be the most affected functionality with response time up to 10 times longer than they used to be before migration.

We strongly suspect the new Oracle version to be at the root of this issue. To try to ascertain that, we re-mounted our old database (Oracle 10g) and set up identical map services with it. With this version of Oracle (and all other things remaining unchanged), we get back the performances that we used to have while despite our best efforts we can't bring the mapservices using Oracle 11g "up to speed".

Of course, there's always the risk that the schemas, users, permissions and/or whatever were not copied correctly from the old databases to the new ones but after pestering our database administrators almost past their point of endurance with these consideration we are fairly confident the problem lies somewhere else.

So, has anyone experienced this kind of issue? Is it,as we suspect, related to the new version of Oracle? Most importantly, how can we fix this?

Thanks for your time!

JulienDe_Vos · Answer

Hello,

Little update on the situation. Things have evolved bizarrely and probably out of the scope of this particular thread but since it is consecutive to it, I'm just putting a word here in case someone wonders if and how the problem was solved (it was not).

I've put a word about data fragmentation to our DBAs, they seemed quite skeptic about it. An argument in their favor is that we've been monitoring the SQL generated by ArcGIS Server and tried to run it in a simple RDBMS client. To our surprise, we found no noticeable difference in response time between Oracle 10 and 11. It seem to us that the problem then must reside within ArcGIS Server. We ignore too much to make this test foolproof and this conclusion...er...conclusive but this is the assumption we based ourselves on. If you see a flaw in the logic, please share...

The second surprise was to discover that ArcGIS Server only makes use of SDO_FILTER in its queries, which appears to be a function that queries features intersecting the MBR (Minimum Bounding Rectangle, that is the envelope if I am not mistaken) of a given geometry instead of the geometry itself. I suppose this is so to make optimal use of the spatial index while the exact intersection is then computed by the server on the subset of features returned by the database. Maybe that processing is where the problem manifests itself although why it should differ from one version of Oracle to the other is unclear to me. Except maybe I've been told that in oracle 11g all geometries are now 3D, I'm not sure if it is an absolute truth but it certainly is the case for our databases and our old 10g database were 2D, could that be related?

This is were things get weird and wander off subject.

We kept trying things and finally found something that "seemed" to work. We went through our query layers and realized they were rather complex. As I said earlier, we don't own this schema so to aggregate the data as we needed it, we had SQL somersaulting through some really mean hoops.
What we did was to remove all the complexity of the queries underlying our query layers. We created a dedicated schema in the database where we created views that aggregated all the needed information. In our query layers we now only had the simplest possible SQL statements ("select * from view"). After republishing the map service with the modified query layers we observed that response time improved until it was back to the same range of values as when we were using Oracle 10g.

I then performed a live test with the application and realized with some...,correct that, huge dismay that if the query was fast indeed, the results were now faulty.
I realized that the mapservice was returning features outside the geometry I provided. Actually it returned all features that intersected the envelope of the geometry instead of the geometry itself. Sadly, I've encountered this problem before (see Identify/Query Tasks return features intersecting envelope instead of geometry), I was never able to solve it but it turned out the affected layers were not strictly essential to the application so I just stopped querying them. I don't have that option today. 
Now, in light of the tests we've conducted, it seems to me that ArcGIS Server is sending back the results of its SDO_FILTER SQL query "as is" without processing the exact intersection (which could account for the lower response time). Have you any idea why that would happen and how we could  force it to perform an exact intersection?

Moreover, I realized that if I put back a where clause in my query (by "a" where clause, I mean "any" where clause, "where 1=1" will do the trick), the intersection results would be correct but I'd lose the performance again.

In short, I'm running in circles here and the aberrant behavior I have observed leave me completely clueless as how to solve this.

Well, actually, I was able to obtain a correct intersection by using the geometry service but I'm not satisfied with it. This is how I proceeded.
-I perform an identify task which sends me back the features intersecting the envelope of the geometry I provide. So I receive a larger set of features than I should but I know that all the features really intersecting my geometry are there.
-I then run the "relation" operation of the geometry service with the geometries of the result features and the same geometry I sent to the Identify. In this method, the intersection is correct (I suppose because Oracle is not involved) and I am then able to sort the features that really intersect my geometry and those that don't.

The "relation" operation is pretty fast but the problem with this method is that I find myself performing multiple calls to the server where there should be only one (actually there's 3 subsequent calls as the relation operation only support one geometry type at the time so if like me you have point, polylines and polygons you have to make a call for each).

Once again, thanks for your time

MarcoBoeringa · Answer

I have no clue how the data was migrated. This was all handled by our db admin team. The database is not anormally big and as for the Oracle version, apart from 11gr2 I couldn't tell you. I'll pass those questions to the database administrators as well as the tip for data fragmentation.

If this all happened through DBA's not aware of GIS datasets and the potential spatial fragmentation, than there is a real possibility tables got mangled / sorted in an undesirable way.

As Vince wrote in one of the other linked threads, there is an easy way to get some idea of a potential problem:

"The hallmark of spatially fragmented data is when rendering (edit: viewing the data in ArcMap) produces a random-seeming draw order, vice filling from one side of the field of view (edit: dataframe in ArcMap) to another. There is no silver bullet to solve spatial fragmentation, but there are techniques available to moderate its influence."

JulienDe_Vos · Answer

Hello!

Thanks for your answers.

I have no clue how the data was migrated. This was all handled by our db admin team. The database is not anormally big and as for the Oracle version, apart from 11gr2 I couldn't tell you. I'll pass those questions to the database administrators as well as the tip for data fragmentation.

Good point about the hardware too. It was definitively not upgraded, but unfortunately, this is not a point that we can change, it'll have to do, I hope we can make do with what we have.

As for ST_GEOMETRY, please pardon the maybe naive question (I'm not an expert) but what exactly do you mean by "ST_GEOMETRY is available when ArcGIS Server is present". Does that mean that ArcGIS Server can somehow dynamically convert SDO_GEOMETRY to ST_GEOMETRY and that would be more efficient? Wouldn't that bring additional processing time?
Or do you mean that we could install ArcSDE and use it instead of pure native Oracle Spatial? That we cannot do, not being the owners of the databases we cannot make any change to the schema structure (certainly not get an SDE schema involved) nor the data types, so it has to remain Oracle Spatial and SDO_GEOMETRY only.

We keep investigating, if anything comes up, I will post what we found here. In the meantime, if anyone thinks of other avenues to explore, please let me know.