Hi All,
I did some digging into our problem and the way geoserver handles the specific
queries we are making, but before I go into that I wanted to give as much
detail on the problem as I can.
We have a geoserver instance rendering borehole feature data from an Oracle
(Standard Edition) into wms map tiles. This server has been running for a few
years without any problems. Recently we noticed Oracle refusing connections
both via geoserver and via direct connections. After some investigation we
noticed that some queries were taking a very long time (5-10 minutes) to
return, while others were sub 1 second. This would eventually result in either
geoserver running out of available database connections and refusing any more
or if we increased the number geoserver was allowed to make eventually the
database refused connections. So, we looking into which specific queries were
causing the problem.
We found that queries that touch or cross the antemeridian were causing the
problem. So, we examined the SQL generated by geotools for these queries and
found that when querying over the antemeridian geotools makes 2 BBox filters,
one either side of the antemeridian and joins them with an SQL 'or'. This
seems reasonable enough to me and as far as I can tell should work. However,
Oracle standard can't optimise this query and performs a full table scan.
Enterprise edition Oracle uses a strategy it calls "Binary plan conversions" to
complete this query in milliseconds but it's not available to Standard Edition.
Also, if the query is rewritten as 2 select statements joined by a union all
it completes in milliseconds.
I have discussed this with Oracle at length but they are adamant that this is
the way Standard Edition should behave and they are unwilling to do anything
about it.
Given that Oracle were not going to help I did some tracing in
geotools/geoserver to find out what the code was doing and I found the
advanced_projection_handling section in geotools
(http://www.geo-solutions.it/blog/developers-corner-advanced-raster-projection-geoserver/).
I'm not entirely sure why this is needed or what it's actually doing but if
enabled geotools generates these split bbox queries which cause us trouble.
Unfortunately, this is where my understanding of what's going on ends, so if
anyone has any pointers to further information or a possible
solution/workaround to this problem I would really appreciate it.
Cheers
Peter Warren
From: Rahkonen Jukka (MML) [mailto:[email protected]]
Sent: Tuesday, 17 March 2015 7:53 PM
To: Geoserver-devel
Cc: Tey, Victor (Mineral Resources, Kensington); Warren, Peter (Mineral
Resources, North Ryde); Jiang, Lingbo (Digital, Marsfield)
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox
range checking before database query.
Hi,
If it should save some time when testing, these queries are fast for me and
they return the same result.
- select sdo_tune.extent_of('geotable','geometry') from dual;
- select sdo_root_mbr from all_sdo_index_metadata where
sdo_index_name='GEOTABLE_SP_IDX';
I guess that the tune.extent is doing about this:
select sdo_root_mbr from all_sdo_index_metadata where sdo_index_name=
(select INDEX_NAME from all_sdo_index_info where table_name='GEOTABLE' and
column_name='GEOMETRY');
I believe that this query is always safe so that if the BBOX does not intersect
with root_mbr there can't be any geometries in the Oracle table to find. It may
be that root_mbr is not shrinked if features are deleted and it may be too
large sometimes but that is easy to test. However, the bounding box that is
given for the layer in Geoserver is not updated automatically either.
If this is too slow to me performed with every query, perhaps Geotools can be
made to cache the root_mbr?
-Jukka Rahkonen-
Rahkonen Jukka wrote:
Hi,
The bounds in the table metadata do not have a direct connection with index
bounds. They are inserted by hand or automatically by ogr2ogr or other
utilities. DIMINFO puts the maximum extents for the index, though, and it is
impossible to insert new features which fall outside these bounds. Therefore we
use always in DIMINFO such bounds which cover whole Finland and a little bit
extra so that is an unusable metadata for reducing spatial extents of queries
in our case. We put more realistic bounds into layer metadata with Geoserver
but that would not be accessible for Geotools.
Jody has written 5 years ago that the correct place to look is sdo_root_mbr of
the rtree index
http://lists.refractions.net/pipermail/udig-users/2010-August/001049.html
However, in this 7 years old thread
https://community.oracle.com/thread/626377?start=0&tstart=0 it is said that
accessing the bounds directly from Oracle should be very fast:
"For fastest speed, if you are getting the mbr of an entire layer, use
sdo_tune.extent_of, which does use the top level MBR values stored in the index
to very quickly give you the extent of the layer. For projected data this has
been the case for some time. For geodetic data the reading of the index
metadata might be new for 11g."
Is the Oracle bug that started this discussion just in that sdo_tune.extent_of
does not work with some Oracle versions?
-Jukka Rahkonen-
________________________________
Jody Garnett wrote:
> I think the index is constructed with the bounds from the table metadata.
I expect the data store can cache anything it wants. Check the bounds
implementation to see if is based on the index or metadata configuration.
On Mon, Mar 16, 2015 at 5:44 PM
<[email protected]<mailto:[email protected]>> wrote:
Jody,
Thanks for your input.
Do you know how we could get index bounds at this level?
Lingbo
From: Jody Garnett
[mailto:[email protected]<mailto:[email protected]>]
Sent: Tuesday, 17 March 2015 8:49 AM
To: Jiang, Lingbo (Digital, Marsfield)
Cc: Andrea Aime; Tey, Victor (Mineral Resources, Kensington);
[email protected]<mailto:[email protected]>
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox
range checking before database query.
Hey Lingbo, I am a geotools lead (like Andrea) and I would be happy to help.
The reason to catch it there (other than it being the right thing to do) is
that it will fix more than just the WMS GetMap case. WMS GetFeatureInfo and WFS
GetFeatures would also be affected after all.
The GeoTools project is built with maven (much like GeoServer), the classes you
are looking for are in JDBCDataStore:
* https://github.com/geotools/geotools/tree/master/modules/library/jdbc
In particular there is an
OracleDialect<https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java>
and
OracleFilterToSQL<https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleFilterToSQL.java>
where we construct all the oracle specific SQL queries:
*
https://github.com/geotools/geotools/tree/master/modules/plugin/jdbc/jdbc-oracle
The code of interest in OracleFilterToSQL goes like this:
if(filter instanceof Beyond || filter instanceof DWithin)
doSDODistance(filter, e1, e2, extraData);
else if(filter instanceof BBOX && looseBBOXEnabled) {
doSDOFilter(filter, e1, e2, extraData);
} else
doSDORelate(filter, e1, e2, swapped, extraData);
So you will want to look carefully at the doSDORelate and doSDOFilter methods.
I notice there is already a method here that does something like what you want,
perhaps you just have a bug to fix in the following code.
Expression clipToWorld(BinarySpatialOperator filter, Expression e) {
if (e instanceof Literal) {
Geometry eval = e.evaluate(filter, Geometry.class);
// Oracle cannot deal with filters using geometries that span
beyond the whole world
// in case the
if (dialect != null && isCurrentGeometryGeodetic() &&
!WORLD.contains(eval.getEnvelopeInternal())) {
Geometry result = eval.intersection(JTS.toGeometry(WORLD));
if (result != null && !result.isEmpty()) {
if(result instanceof GeometryCollection) {
result = distillSameTypeGeometries((GeometryCollection)
result, eval);
}
FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
e = ff.literal( result );
}
}
}
return e;
}
You may need to change this code to clip against your index bounds rather than
"WORLD".
I agree with you that we could do it on oracle datastore level in geotools
providing other DB has no need at all. It could fix our oracle problem only
without affecting others . with your expertise, could you help to pinpoint
where we may have good access with dataset BBOX and query BBOX together in
Geotools .
Lingbo
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Andrea Aime
Sent: Thursday, 12 March 2015 8:12 AM
To: Tey, Victor (Mineral Resources, Kensington)
Cc: Geoserver-devel; Jiang, Lingbo (Digital, Marsfield)
Subject: Re: [Geoserver-devel] Propose to add an option for latLongBoundingBox
range checking before database query.
On Wed, Mar 11, 2015 at 6:11 AM,
<[email protected]<mailto:[email protected]>> wrote:
Hi Andrea,
It seem like there might be some confusion with the different oracle issues
floating around. The oracle version(https://jira.codehaus.org/browse/GEOT-4912
) issue has nothing to do with the patch Lingbo is suggesting on implementing.
However to clear up the confusion, I have advised Lingbo to create appropriate
Jira task and submit a proposal instead so that the issue can be discussed
accordingly around the proposal instead.
Yes, indeed that ticket has nothing to do with it (actually I don't understand
why you're bringing it up? Maybe you wanted to link a different ticket?)
But earlier in this thread you said (copying and pasting):
---------
What one of our developer have found with oracle enterprise edition is that the
database actually optimize the query when a query is made outside the bounds
of the dataset. However when we ran the same query on a oracle standard
edition, the query crashed the database. We have logged a ticket with Oracle as
part of our contract with them and its currently under investigation.
---------
So, this statement makes me (and Jody) think your bbox problem problem is
Oracle version specific.
In other words, can you show us how this new configuration would provide a
_significant_ benefit with a non
broken database query optimizer? (yes, a theoretical general benefit is
evident, you don't even access the data,
it has to be faster, but if we get only 0.1% speedup on the overall WMS/WFS
request it's not worth the effort and the maintenance of that code).
Cheers
Andrea
--
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.
==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313<tel:%2B39%200584%20962313>
fax: +39 0584 1660272<tel:%2B39%200584%201660272>
mob: +39 339 8844549<tel:%2B39%20%C2%A0339%208844549>
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i
file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo
è consentito esclusivamente al destinatario del messaggio, per le finalità
indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne
il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di
procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro
sistema. Conservare il messaggio stesso, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse,
costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely for the
attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act (Legislative
Decree June, 30 2003, no.196 - Italy's New Data Protection Code).Any use not in
accord with its purpose, any disclosure, reproduction, copying, distribution,
or either dissemination, either whole or partial, is strictly forbidden except
previous formal approval of the named addressee(s). If you are not the intended
recipient, please contact immediately the sender by telephone, fax or e-mail
and delete the information in this message that has been received in error. The
sender does not give any warranty or accept liability as the content, accuracy
or completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.
-------------------------------------------------------
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Geoserver-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
