We are storing data in Geodatabase on Oracle using Oracle Spatial option SDO_Geometry and consuming that using ArcGIS server 10.2.1
The performance is very slow (we are talking about between one to two million records)
When I loaded the data, I didn't pay attention to create indexes or configure indexes I just used FME to load data from 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 beginner in the performance issues, because I am mostly a developer. thanks for any help. Any help, best practices, guidance, will be appreciated.
The SDO Geometry versus ST_Geometry versus Compressed Binary versus whatever storage format for geometries discussion is something ESRI has been avoiding mostly in the past ten years or so...
As to best practices, performance of software / hardware systems etc., the System Design Strategies Preface gives guidance and lots of stuff to read, some others here on the forum may give more specific answers as regards the Oracle Spatial storage.
There is no such thing as an "SDE" index. All indexes in a database are database indexes. The SDE.ST_GEOMETRY type has an index designed by Esri, to be implemented as a library extension by Oracle, but it's still a database index.
The MDSYS.SDO_GEOMETRY type is of Oracle's design and implementation. Esri had no role in its creation.
There isn't much you can do to customize the SDO_GEOMETRY spatial index -- A different tablespace, and a geometry type specification, though if you have multiple independent disks, the former might be helpful, and if your data is points, then specifying
might produce a significant performance improvement.
Your post indicates 1-2m rows -- How many are being returned by each query? If it isn't a tiny subset, then that's really your problem -- no index performs well when returning a significant fraction of the table. The SDO_GEOMETRY storage format is also much larger than the compressed binary used in SDE.ST_GEOMETRY, so if an identical ST_GEOMETRY polygon layer is 1/3 the size of SDO_GEOMETRY, on a full table scan query, it wouldn't be surprising if the ST_GEOMETRY was 3 times faster.
One property that you do have control over is spatial fragmentation -- If you organize the features so that neighboring features are close to each other in the table (e.g. ORDER BY UTM_ZONE,LATITUDE on a global GeoNames dataset), then a spatial query over a small area (say, one 6x6 degree tile) will be optimized. Of course, this requires that you export and reimport the base table.
Another potential optimization is to break up massive polygons. When I split a 1:15m scale "world" polygons layer into 5x5 degree tiles for any country larger than 5 degrees in either X or Y, my "identity" operation performance moved from ~300ms/query to ~3ms/query.
All in all, it's probably unwise to ignore index creation options, but the best place to learn how to optimize Oracle's datatype is on an Oracle forum.