2

We are storing data in a Geodatabase on Oracle 11g using Oracle Spatial with SDO_Geometry, and then consuming that using ArcGIS server 10.2.1.

The performance is very slow, because we are talking about between one to two million records.

When I loaded the data, I didn't pay attention to creating or configuring indexes, as I just used FME to load the data from a file geodatabase.

What are the best practices when you use Geodatabase on Oracle with SDO geometry?

Which indexes are being used, the oracle ones, or sde ones?

I am very new to database performance issues because I am mostly a developer.

  • 2
    You mention "one to two million rows" -- How many are returned by your query? – Vince Mar 28 '14 at 11:55
  • 1
    What do you mean by "very slow" ? Is is about the time needed to repaint a map ? To zoom or pan ? How many layers do you have on your maps ? What exact version of Oracle 11g do you use ? On what platform ? – Albert Godfrind Mar 28 '14 at 12:06
  • when zoom in, and pan, it is taking more than a minute to render the layers. Oracle in on unix box, and arcgis server on windows 2008 R2 server machine. – Ghassan Karwchan Mar 28 '14 at 17:28
  • 1
    when you say "consuming that using ArcGIS Server 10.2.1" can you provide more information, please? How are you consuming the data - via a feature layer, dynamic layer, etc? Which API/client are you using with ArcGIS Server? Have you set scale thresholds for the layers? Is the performance any better using the same dataset served via a file geodatabase? (The reason for all the questions here and above is that there are multiple places where the performance can be impacted.) – Stephen Lead Sep 23 '14 at 1:59
0

Make sure you are hitting the spatial index. Query the Oracle execution plan and make sure a full table scan isn't being executed. Also, make sure the geometry is valid and the metadata is valid. These can both be done by using GeoRaptor in SQL developer.

0

I will submit what I did to enhance the performance. I followed the procedure describe in the book "Pro Oracle Spatial"

http://www.oracle.com/technetwork/database/options/spatial/pro-oracle-spatial11g-094119.html

in Chapter 14, on clustering the data. The idea is to cluster and physically restore your data depending on a criteria, or spatial indexing areas

that enhanced the performance a lot, by at least 500%

Not the answer you're looking for? Browse other questions tagged or ask your own question.