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.

closed as too broad by PolyGeo Aug 28 at 22:41

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.