I have an ArcGIS Map Server Map Service connecting to an Oracle 11G instance via Direct Connect. There is no need for ArcSDE since our application doesn't need versioning, replication, or any other of the doo-dads that ArcSDE provides. All we want to do is plot some dots on a map.
The Oracle table(s) have a SDO Geometry column containing only point geometry types. I have implemented a spatial index, validated geometries, optimized tolerance...etc. However, the rendering performance of a layer pointing at SDO Geometry column is awful - 15-20 seconds to render 10k points with very simple symbology. I have actually found that using an event layer using raw x,y values is faster than using SDO geometry columns.
I will admit that I am more knowledgeable of MS SQL and PostGIS - but I've been pretty thorough in making sure I am doing good by Oracle. I am hoping someone with more experience using Oracle SDO Geometry in their map services can provide more guidance - are there maybe a few gotcha's I'm missing.
UPDATE (Per Vince's Request):
Spatial Index SQL:
CREATE INDEX ON "SGSDWADM"."SERVICELOCATION"("GEOMETRY") INDEXTYPE IS MDSYS.SPATIAL_INDEX