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
