Hello, Our GIS workgroup is testing a new spatial database which utilises SDE 10.1. Our GIS workgroup has found issues with the performance of redisplay of geospatial data on the test database â?? thus far. We have defaults install. Our aim is for at least similar display performance with the new database. Unfortunately, we have found redisplay times for spatial datasets are up to 2-3 times slower under the new database setup when compared to our â??oldâ?? (SDE 9.3.1) setup.
Environment:
ArcGIS 10.1 (build 3035) on Microsoft Win XP v2002 SP3. 3.3Ghz with 2 G RAM.
Oracle 11r2 on Sun Solaris (Geodatabase enabled SDE schema v 10.1)
Oracle 11r2 on Sun Solaris with SDE 9.3.1 (Note: This â??oldâ?? environment forms the â??baselineâ?? for performance testing against the new system/environment).
The performance tests:
â?¢ A bookmarked extent sourced from a PC (specs above) with ArcMAP 10.1 installed.
â?¢ Polygon datatypes ~400,000 spatial records:
o ST_Geometry (10.1)
o Low Resolution ESRI binary (9.3.1)
Find below a copy of the two queries (using OEM) as received at the database:
Query on Oracle-SDE 10.1 database:
SELECT 1 SHAPE, VEGGROUP, TEST_TV2_ST2.OBJECTID, TEST_TV2_ST2.SHAPE.points,TEST_TV2_ST2.SHAPE.numpts,TEST_TV2_ST2.SHAPE.entity,TEST_TV2_ST2.SHAPE.minx,TEST_TV2_ST2.SHAPE.miny,TEST_TV2_ST2.SHAPE.maxx,TEST_TV2_ST2.SHAPE.maxy,TEST_TV2_ST2.rowid
FROM BASE.TEST_TV2_ST2 TEST_TV2_ST2
WHERE SDE.ST_EnvIntersects(TEST_TV2_ST2.SHAPE,:1,:2,:3,:4) = 1
Query on Oracle-SDE 9.3.1 database:
SELECT /*+ LEADING INDEX(S_ S945_IX1) INDEX(SHAPE F945_UK1) INDEX(TASVEG_VEGETATION_ONLY A945_IX1) */ SHAPE, VEGGROUP ,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid,SHAPE.numofpts,SHAPE.entity,SHAPE.points,SHAPE.rowid
FROM
(SELECT /*+ INDEX(SP_ S945_IX1) */ DISTINCT sp_fid, eminx, eminy, emaxx, emaxy
FROM SIPS_DBA.S945 SP_
WHERE ((SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3 AND SP_.gy <= :4 ) OR (SP_.gx >= :5 AND SP_.gx <= :6 AND SP_.gy >= :7 AND SP_.gy <= :8)) AND SP_.eminx <= :9 AND SP_.eminy <= :10 AND SP_.emaxx >= :11 AND SP_.emaxy >= :12) S_ , SIPS_DBA.TASVEG_VEGETATION_ONLY , SIPS_DBA.F945 SHAPE
WHERE S_.sp_fid = SHAPE.fid AND S_.sp_fid = SIPS_DBA.TASVEG_VEGETATION_ONLY.SHAPE
Findings:
Query on SDE10.1:
â?¢ takes 70 seconds to redisplay in ArcMAP 10.1.
â?¢ Analysing the Explain Plan of the 10.1 SDE query shows it has a very high cost.
â?¢ took 58 seconds to process on the Server.
â?¢ utilises the layer geometry to perform the sub-selection.
Query on SDE9.3.1:
â?¢ takes 30 seconds to redisplay in ArcMAP 10.1
â?¢ Analysing the Explain Plan of the 9.3.1 SDE query shows it has a low cost.
â?¢ takes 4 seconds to process on the Server.
â?¢ utilises Oracle Optimiser Hints.
â?¢ uses the layers Spatial index.
We have some questions and concerns:
1. Why does the query hit the database with different syntax - when the Client application is the same (ArcGIS 10.1), as is the (bookmarked) extent?
a. Why are Oracle optimiser hints not being used in SDE 10.1?
b. Why is the query in SDE 10.1 utilising feature geometry layer instead of the spatial index to perform the query? (Note spatial index is available and built)
Looking forward to your insights on this matter.
Regards, Simon
