Hello,
Thanks for looking into this.
When I did my tests last year "Oracle NG" and "Oracle JNDI" gave the same
results... but when I retested now on our current environment (geoserver 2.2.0
instead of 2.1.4) there are indeed significant differences between the two:
- Oracle JNDI (using an "SQL view" layer):
- 16 calls to the database per layer, and query "SELECT NULL AS
table_cat..." takes 0.1 second per execution
- 8 out of the 16 calls it is the validation query or the JNDI
connection pool
- Oracle NG (using an "SQL view" layer):
- 2 calls to the database per layer:
- the query getting the data
- +- the data query but with an extra "where 1=0" appended
- NO validation query, even though the "validation query" checkbox is
enabled in the data store definition. Or is the "where 1=0" query the
validation query?
So... it seems that the "Oracle NG" datastore is a lot better... I just wonder
if it really is validating its connection or not...
Thanks a lot for this feedback... it looks "Oracle NG" might save us. There are
some disadvantages with it, like the password that needs to be specified in
each datastore instead of in the tomcat configuration... but... these are minor
inconveniences compared to the performance problems...
I'll keep you guys posted...
Thanks!!!
Pieter
Van: Jonathan Moules [mailto:[email protected]]
Verzonden: dinsdag 29 oktober 2013 13:31
Aan: Pieter Roggemans
CC: Andrea Aime; [email protected]
Onderwerp: Re: [Geoserver-users] Oracle vector JNDI: bad performance due to 16
database calls per layer fetched
Hi Both,
I'm arriving a little late to this one.
I'm using "Oracle NG" for my connection (Geoserver 2.4.1) and don't appear to
be seeing this, although maybe I'm looking in the wrong places. Is this
something unique to the JNDI driver?
The debug level GeoServer logging is only showing one request made, and the
v$sql query only appears to be one as well (though I may well be mis-reading
both).
So maybe you could try a different Oracle connection type?
Jonathan
On 25 October 2013 14:18, Pieter Roggemans
<[email protected]<mailto:[email protected]>>
wrote:
OK.
This means that the best-case of migrating now to a newer version would be that
we would "only" loose one second per WMS request instead of 3 seconds per WMS
request... This is a significant improvement... if the behavior is the same in
our environment... but nonetheless it stays a very suboptimal situation.
Hopefully you find the time to have a look into the code to check if it is
possible to optimize these issues away...
Pieter
Van: [email protected]<mailto:[email protected]>
[mailto:[email protected]<mailto:[email protected]>] Namens Andrea Aime
Verzonden: vrijdag 25 oktober 2013 14:43
Aan: Pieter Roggemans
CC:
[email protected]<mailto:[email protected]>
Onderwerp: Re: [Geoserver-users] Oracle vector JNDI: bad performance due to 16
database calls per layer fetched
On Fri, Oct 25, 2013 at 2:40 PM, Pieter Roggemans
<[email protected]<mailto:[email protected]>>
wrote:
Ouch... the culprit shows up now in 2.3.4 as well... so upgrading won't solve
the problem... at best it will reduce the problem:
SELECT NULL AS table_cat,/n o.owner AS table_schem,/n o.object_name
AS table_name,/n o.object_type AS table_type,/n NULL AS remarks/n
FROM all_objects o/n WHERE o.owner LIKE :1 ESCAPE '/'/n AND o.object_name
LIKE :2 ESCAPE '/'/n AND o.object_type IN ('xxx', 'TABLE', 'VIEW',
'SYNONYM')/n ORDER BY table_type, table_schem, table_name/n
Is the above query only executed once per layer fetched in a WMS call? In our
case it is 3 times per layer...
I have dumped all the queries performed for one wms call over a sql view in my
previous mail.
So yes, it's happening once per call.
Can probably be optimized out with some caching, not 100% sure, some
investigation in the code would be needed
Cheers
Andrea
--
==
Our support, Your Success! Visit http://opensdi.geo-solutions.it 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
-------------------------------------------------------
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
[email protected]<mailto:[email protected]>
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.
------------------------------------------------------------------------------
Android is increasing in popularity, but the open development platform that
developers love is also attractive to malware creators. Download this white
paper to learn more about secure code signing practices that can help keep
Android apps secure.
http://pubads.g.doubleclick.net/gampad/clk?id=65839951&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
