|
|
|
Issue Type:
|
 Bug
|
|
Affects Versions:
|
2.1.4
|
|
Assignee:
|
Andrea Aime
|
|
Components:
|
Oracle
|
|
Created:
|
19/Sep/12 11:57 AM
|
|
Description:
|
When loading data using an “Oracle NG” or “Oracle NG (JNDI)” store, getting the data results in 16 calls to the database:
- SELECT WKTEXT FROM MDSYS.CS_SRS WHERE SRID = -1
- 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
- SELECT * FROM (SELECT geom FROM gis_objct WHERE layer_id = 40080) VTABLE WHERE 1 = 0
- SELECT WKTEXT FROM MDSYS.CS_SRS WHERE SRID = -1
- 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
- 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
- SELECT * FROM (SELECT geom FROM gis_objct WHERE layer_id = 40080) VTABLE WHERE 1 = 0
- 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)
So this results in 16 roundtrips just to get the data from one layer?
Some extra remarks:
- if there isn’t a “schema” specified in the store configuration, queries number 2 and 5 take ± 1 second, as they fetch the entire all_objects because ‘%’ is passed as bind variables. If “schema” is specified with any value, eg. ‘UNEXISTINGSCHEMA’, everything still works perfectly, but the query takes 0.001 seconds. So this means that the query must be completely useless… and could/should not be executed.
- The SRID info (queries 1 and 4) fetched could just be cached instead of retrieving it every time… even if null is returned (we use SRID -1 as we use SRID NULL in oracle).
- I know why the check of the validation-query in a connection is so popular… this way you don’t need to think about that in your application, but there is a much more efficient way to solve this. You just check in every catch block (or in a overriden version of the connection class
 ) if the error returned is one that signifies that the connection is stale… (eg. ORA-01090, ORA-01089,…) if so, just clear all connections from the connection pool and retry once… If it is OK then the database is back, if not… rethrow the error anyway bacause the DB is still unreachable…
This is what we do in our internal development (in C#) and it works like a charm without millions of “select 1 from dual” per day.
|
|
Environment:
|
Suse Linux Enterprise 11, Tomcat 6
|
|
Project:
|
GeoServer
|
|
Priority:
|
 Major
|
|
Reporter:
|
the_roggy
|
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your [JIRA administrators](https://jira.codehaus.org/secure/ContactAdministrators!default.jspa).
For more information on JIRA, see: [http://www.atlassian.com/software/jira](http://www.atlassian.com/software/jira)