WMS GetFeatureInfo shows very poor performance in Oracle

Description

I have a line layer in Oracle Locator 11gR2 with around 100,000 features (all 2-vertex lines, no line strings). Rendering a map showing an area within the layer, containing perhaps 500 features, is nice and fast - maybe two seconds. However, a getFeatureInfo operation with the same parameters (bbox, style, cql_filter etc.) is extremely slow - over two minutes. The issue was originally observed in a custom application but I can replicate it in the OpenLayers layer preview.

I enabled verbose logging and did some digging. The issue appears to come from the SQL query that GeoServer sends to Oracle. In GeoServer 2.2.5 getMap and getFeatureInfo, and GeoServer 2.6.1 getMap, the spatial part of the query looks like this:

SELECT ... FROM ... WHERE (SDO_FILTER(LINE, MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(<minx>,<miny>,<maxx>,<maxy&gt), 'mask=anyinteract querytype=WINDOW') = 'TRUE'

However, in GeoServer 2.6.1 getFeatureInfo the spatial part looks like this:

SELECT ... FROM ... WHERE (SDO_FILTER(LINE, MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(<minx>,<miny>,<maxx>,<maxy&gt), 'mask=anyinteract querytype=WINDOW') = 'TRUE' OR (SDO_FILTER(LINE, MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(<minx>,<miny>,<maxx>,<maxy&gt), 'mask=anyinteract querytype=WINDOW') = 'TRUE'

Presumably the severe slowdown comes from both clauses trying to do the same thing simultaneously. Perhaps if my line layer had fewer features I wouldn't notice that this was an issue.

Environment

None

Status

Assignee

Unassigned

Reporter

codehaus

Triage

None

Fix versions

None

Affects versions

2.6.1

Components

Priority

Low
Configure