[Benchmarking] Performing SQL explain over Oracle DB

[Andrea Aime]

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
2))
2 = S1740
3 = S1400
4 = S1200
5 = S1100
6 = S1200
7 = S1100

Now, some of the parameters is redundant, but I don't see
why Oracle can't efficiently execute this query.

I would like to try and run an "explain" over it but I did not
manage to connect sqlplus to the db. Tried with

/u01/app/oracle/product/11.1.0/db_1/bin/sqlplus bench at orcl

but I get back:

ORA-12154: TNS:could not resolve the connect identifier specified

Suggestions?

Cheers
Andrea