[Benchmarking] Performing SQL explain over Oracle DB

[Andrea Aime]

Andrea Aime ha scritto:
> Hi,
> during my Oracle and SDE tests this morning I've noticed
> that Oracle uses an inordinate amount of CPU and it's
> by a long shot the bottleneck (while GeoServer is barely using
> CPU on goliath).
> 
> In both cases GeoServer is running a query that involves both
> the spatial extent and the classifications, something like:
> 
> geom in bbox and (mtfcc = 'S1740' or mtfcc = 'S1200' or ...)
> 
> The situation deteriorates to a point where rendering the
> maps starts failing.
> 
> In particular a sample Oracle query looks like the following prepared
> statement:
> 
> SELECT MTFCC,SHAPE as SHAPE FROM BENCH.EDGES_MERGE WHERE (SDO_FILTER
> (SHAPE, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'  AND (MTFCC = ? 
> OR MTFCC = ? OR MTFCC = ? OR MTFCC = ? OR MTFCC = ? OR MTFCC = ?))
> 
> with the parameters bound to:
> 
> 1 = MDSYS.SDO_GEOMETRY(2003,4269,NULL,MDSYS.SDO_EL
> EM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-100.09730169892472,30.552995277124182,-100.02969830107527,30.552995277 
> 
> 124182,-100.02969830107527,30.60880972287582,-100.09730169892472,30.60880972287582,-100.09730169892472,30.55299527712418 

Ah, don't be thrown off by this, the actual geometry is sent
as an optimized rectangle, the above is just debugging
information (the path that dumps the geometries to a human readable
form cannot dump the geometry into the optimized rectangle
format).

Cheers
Andrea


-- 
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.