Geodatabase with Oracle Spatial performance

Question

Hi

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.

GhassanKarwchan · Answer

Thanks Vangelo
you added very valuable information.
This is exactly what I was looking for

VinceAngelo · Answer

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 PARAMETERS(�??sdo_indx_dims=2, layer_gtype=point�??)

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.

- V

MarcoBoeringa · Answer

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.