Hi,
I've just tried out with a 2.3.4 I was working with, I only see 4 queries
for each of my WMS requests, a line layer that I've renamed compared to the
raw table name to have a nicer looking name:
1) SELECT GEOMETRY as GEOMETRY FROM GEOSERVER.GRAFO_EXP WHERE
SDO_FILTER(GEOMETRY, :1, 'mask=anyinteract querytype=WINDOW') = 'TRUE'
2) select 1 from dual
3) SELECT info from "GEOSERVER".MDRT_6757$ where rowid = :rid
4) select count(*) from mdsys.geodetic_srids where srid = :srid
As you know 1 and 2 cannot be avoided (well 2) can be removed by disabling
connection validation)
3 is weird, must be generated by the jdbc driver but I have no idea why and
what it is about
4) is used to determine if the srid is geodetic, there is a cache for this
info, but it's not doing its job, this one can definitely be eliminated
Not sure why you're getting all the others. Might be the JDBC driver again
(have you tried using a different one, maybe more recent?),
or something that has been fixed in the meantime.
Btw, I'm not using a JNDI connection, that might be a factor, however the
GeoServer code does not even know where the connection pool
is coming from, so the difference could be caused, potentially, by the
connection pool itself.
There is a number of things that could be the cause... needs more
investigation.
Cheers
Andrea
On Fri, Oct 25, 2013 at 10:27 AM, Pieter Roggemans <
[email protected]> wrote:
> Hello everyone,
>
> We noticed while having a look at database activity from geoserver to
> Oracle there seem to be quite some inefficiencies...
>
> After some looking around we found out that for every vector layer
> (configured using the oracle JNDI datastore) fetched from oracle there are
> 16 calls to the database... and since our average WMS requests needs 10
> vector layers, this is 160 calls per WMS request.
>
> This is an example of the database calls for a layer:
>
> 1) SELECT WKTEXT FROM MDSYS.CS_SRS WHERE SRID = -1
> 2) SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS
> table_name, o.object_type AS table_type, NULL AS remarks FROM all_objects o
> WHERE o.owner LIKE :1 ESCAPE '/' AND o.object_name LIKE :2 ESCAPE
> '/' AND o.object_type IN ('xxx', 'TABLE', 'VIEW')
> ORDER BY table_type, table_schem, table_name
> 3) SELECT * FROM (SELECT geom FROM gis_objct WHERE layer_id = 40080)
> VTABLE WHERE 1 = 0
> 4) SELECT WKTEXT FROM MDSYS.CS_SRS WHERE SRID = -1
> 5) SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS
> table_name, o.object_type AS table_type, NULL AS remarks FROM all_objects o
> WHERE o.owner LIKE :1 ESCAPE '/' AND o.object_name LIKE :2 ESCAPE
> '/' AND o.object_type IN ('xxx', 'TABLE', 'VIEW')
> ORDER BY table_type, table_schem, table_name
> 6) SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS
> table_name, o.object_type AS table_type, NULL AS remarks FROM all_objects o
> WHERE o.owner LIKE :1 ESCAPE '/' AND o.object_name LIKE :2 ESCAPE
> '/' AND o.object_type IN ('xxx', 'TABLE', 'VIEW')
> ORDER BY table_type, table_schem, table_name
> 7) SELECT * FROM (SELECT geom FROM gis_objct WHERE layer_id = 40080)
> VTABLE WHERE 1 = 0
> 8) SELECT GEOM AS GEOM FROM (SELECT geom FROM gis_objct WHERE layer_id =
> 40080) VTABLE WHERE SDO_FILTER(GEOM, :1 , 'mask=anyinteract
> querytype=WINDOW') = 'TRUE'
>
> And before executing each and every query above, the connection pool will
> check if the connection is still valid by executing the following query:
> select sysdate from dual (or select 1 from dual)
>
> In the overview above you see that 2), 5) and 6) is the same query being
> executed 3 times per layer... so 30 times in our average case. If there is
> a "SCHEMA" defined (any) in the JNDI data store, this query takes 0.1
> second per execution in our config... if not... +- 1 second per execution.
> This means that best-case this query accounts for 0.1 * 30 calls = 3
> seconds added time for each WMS request... which means 66% of the time
> because our average WMS request takes 4.5 seconds.
>
> It doesn't matter if it is the first WMS request for a layer/data
> store/... or the 10th... all queries are always executed, no caching
> behaviour, regardless of the setting of "Feature type cache size" in global
> settings.
>
> Our configuration is as following:
> - Geoserver 2.2.0 (Linux, Oracle/Sun Java 1.6, Tomcat)
> - Oracle 11gR2
>
> Question:
> Is anyone else seeing this behavior... or is there something specific
> about our setup that causes these issues?
>
> PS: It is pretty easy for anyone with select permissions on the (system)
> table v$sql to see the activity of a certain user using the following query:
> SELECT LAST_ACTIVE_TIME, last_load_time, module,
> parsing_schema_name, elapsed_time total_elapsed_time_micros, executions
> number_executions,
> DECODE(executions,0,0,round((elapsed_time/executions)/1000000,3))
> avg_elapsed_time_s, sql_id, sql_fulltext FROM v$sql
> WHERE 1=1
> and parsing_schema_name IN (:username)
> -- and last_active_time > to_date('24/01/2011 16:13:00',
> 'DD/MM/YYYY HH24:MI:SS')
> -- and UPPER(sql_text) like UPPER('SELECT NULL AS
> table_cat,%')
> -- and sql_id = 'c749bc43qqfz3'
> ORDER BY LAST_ACTIVE_TIME DESC;
>
> All this information is also contained in following geoserver bug report:
>
> https://jira.codehaus.org/browse/GEOS-5317?focusedCommentId=334486&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-334486
>
> Regards,
> Pieter
>
>
> ------------------------------------------------------------------------------
> 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]
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
--
==
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
fax: +39 0584 1660272
mob: +39 339 8844549
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]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
