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
