Hi All,
We should rename this the “Oracle lack of appreciate thread”. I can concur that its something of a pain but unfortunately its our “Corporate solution” though I am pressing for PostGIS, but that’ll be a long fight.
Following Christian, I’ve done my own testing (hence my delayed reply - look at the numbers and you’ll see why 
).
https://docs.google.com/open?id=0B22cYd3gwE-6MkRfdlp6VFNOZjQ
My own EXTENT_OF speeds were much faster than yours Christian - in fact I found the exact opposite linear progression (gets faster for larger datasets). Even for ~20million features it only took ~60 seconds. I honestly don’t know why mine is so much faster.
Of course, my AGGR_MBR times were utterly dismal in comparison. You’ll see that features per second is fairly consistently slow with AGGR_MBR.
There’s also the issue of load; when using AGR_MBR, it uses a fair amount of CPU resources on the box (there’s nothing else running on that box).
I tried to investigate what the difference was between EXTENT_OF and SDO_AGGR_MBR but that doesn’t seem to be documented anywhere I can find.
I did however discover this page: http://support.cs.nott.ac.uk/help/docs/databases/oracle/standard/appdev.101/b10826/sdo_objtune.htm#i857307 - which states that EXTENT_OF is deprecated (and this is in Oracle 10.1):
“This function is deprecated, and will not be supported in future versions of Spatial. You are instead encouraged to use the SDO_AGGR_MBR function, documented in Chapter 14, to return the MBR of geometries. The SDO_TUNE.EXTENT_OF function is limited to two-dimensional geometries, whereas the SDO_AGGR_MBR function is not.”
Which given my statistics seems like a downgrade.
Ourselves we don’t really use any of the spatial functions of Oracle beyond SDO_FILTER (which is the intersect) - all of that analysis is done client side, so speed hasn’t been an issue.
Given all of this, would it be worth including a “select count(*) from Table_name” when a user clicks “calculate bounds” on Oracle and warning the user if the feature count is > 100,000 that they should figure their bounds out another way? At least if its using AGGR_MBR. Otherwise courtesy of not-thread-safe, GeoServer admin will be locked up for literally hours if there are millions of features.
Jonathan
On 8 January 2013 10:19, Paolo Crosato <paolo.crosato@anonymised.com> wrote:
Il 08/01/2013 07:18, cmaul ha scritto:
Jonathan,
I was never able to define large datasets in Oracle letting Geoserver find
the bounding box and thought it is a Geoserver problem but it is not.
As you said and Andrea confirmed in the background on the oracle level it
must be one of these commands:
SELECT SDO_TUNE.EXTENT_OF(‘TABLE_NAME’, ‘SPATIAL_COLUMN’) FROM DUAL;
or
SELECT SDO_AGGR_MBR(SPATIAL_COLUMN) FROM TABLE_NAME;
Running the SDO_TUNE.EXTENT_OF on a dataset with 3.3 million features I gave
up after 15 mins.
Trying the command with smaller datasets I get the following numbers.
secs feature numbers
1.6 935
1.9 3700
7.6 28000
30.25 105000
Sort of linear increase and atrocious performance. The ‘SDO_AGGR_MBR’ is
even worse. So, I get the BBox from ArcSDE and hack it in by hand which
infuriates me, because I think a decent spatial database should work on its
own resources.
Geoserver is 2.1.3 and all the tables have spatial indices.
Anything I might do wrong with the DB or any solutions for that?
Cheers
Christian
Hi,
I made a short comparative study about usage of Oracle Spatial for a
rather large dataset we are planning to use, about 100k features with .
I tested the both bounding box, aggregate and intersection function over
the whole dataset. It turned out performances on the db side were
unacceptable, even on a fairly good workstation (2 quad core xeon procs,
32G ram and SAS hds).
I had to skip Oracle in favour of PostGIS, the performances on my quad
core development pc are order of magnitudes better.
I can confirm it’s not a Geoserver issue, the same Geoserver that was
sluggish on Oracle Spatial became blazing fast when hooked to Postgres.
I don’t know if Oracle Spatial performances get better on big mainframes
or racs, if you can afford them.
Regards,
Paolo
–
Paolo Crosato
Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS
and more. Get SQL Server skills now (including 2012) with LearnDevNow -
200+ hours of step-by-step video tutorials by Microsoft MVPs and experts.
SALE $99.99 this month only - learn more at:
http://p.sf.net/sfu/learnmore_122512
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
This transmission is intended for the named addressee(s) only and may contain sensitive or protectively marked material up to RESTRICTED and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All email traffic sent to or from us, including without limitation all GCSX traffic, may be subject to recording and/or monitoring in accordance with relevant legislation.
