Came into my mind that we timed the GDAL queries which were close to
SELECT META.SDO_LAYER_GTYPE FROM ALL_INDEXES INFO INNER JOIN MDSYS.ALL_SDO_INDEX_METADATA META ON INFO.INDEX_NAME = META.SDO_INDEX_NAME WHERE INFO.TABLE_NAME = :1 AND REPLACE(meta.sdo_column_name, ‘"’) = :2
and they took about 15 second on the first run. The same query sent again was fast, but for another table name again 15 seconds.
What GeoTools could do is to
select * from all_sdo_geometry_metadata;
select * from all indexes;
select * from all_sdo_index_metadata;
perhaps with limited list of attributes and join the tables on the client side. I bet that 70 minutes that Jacob now needs to wait would be closer to 70 seconds then.
-Jukka-
···
Lähettäjä: Rahkonen Jukka (MML) [mailto:jukka.rahkonen@…6847…]
Lähetetty: 9. joulukuuta 2015 19:56
Vastaanottaja: Jacob Overgaard Madsen; GeoServer Mailing List List
Aihe: Re: [Geoserver-users] More Geoserver 2.8 debugging - please help! Someone!
Hi,
The query that you copied is pretty simple and it is checking if the spatial index of spatial table is created with parameter “layer_gtype”. In Oracle that is the only place to check if table is having some fixed geometry types. If layer_gtype parameter is not set then geometries in the table can be of any kind.
Theoretically query is OK but unfortunately for some reason Oracle is dead slow with it. The problem is the same with other programs as well, for example GDAL. For us running plain “ogrinfo OCI:user/pw@…7368…” takes about 15 seconds per layer and the result is just list of layers with their geometry types.
This feels like something that should really be corrected on the Oracle side but it may not happen soon. However, there should be a documented workaround called “geometry_metadata_table”. Documentation is in [http://docs.geoserver.org/latest/en/user/data/database/oracle.html](http://docs.geoserver.org/latest/en/user/data/database/oracle.html). However, is the documentation correct? I have thought that the name of the table shoud be “geoserver_metadata”, not GEOMETRY_COLUMNS. Also version “*Geometry metadata table”* appears in the document.
-Jukka Rahkonenö
Jacob Overgaard Madsen wrote:
Hi list
Ok, let me break this down. I have an Oracle Spatial database, which currently is home to about 6000 spatial tables. I’m currently running a production 2.6.2 Geoserver, which I’m still unable to upgrade to the latest 2.8.1 version.
The spatial tables are created in sets, where a specific user is then given access to the set through WFS. Until now, I have chosen to create a separate data store each time for these sets for easier administration.
But now, due to my previous failed attempts to get the server to start under 2.8.x with my current data directory, I’ve decided to try changing strategy: in my new data directory, I now only have 1 single data store, ie. one single connection to the database. This data store is at this moment home to almost 6000 layers, I tediously copied over from the previous configuration.
Now, I can actually get the server to boot - but it takes forever! Take a look at this log: in my latest run, the server took 1 hour and 10 minutes to start!
INFO | jvm 1 | 2015/12/09 16:14:31 | 09 dec 16:14:31 INFO [gwc.config] - Initializing GeoServer specific GWC configuration from gwc-gs.xml
INFO | jvm 1 | 2015/12/09 17:24:17 | 09 dec 17:24:17 INFO [ows.OWSHandlerMapping] - Mapped URL path [/gwc/service/**] onto handler ‘dispatcher’
Between those 2 lines, I have tried analyzing what’s actually going on. In the waiting time, I’ve run the following script against the database to analyze deeper what’s going on:
SELECT sid
, serial#
, username
, ( SELECT max( sql_text ) FROM v$sql sq WHERE sq.sql_id = se.sql_id ) AS sql_text
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
, se.*
FROM v$session se
WHERE username = ‘(geoserver oracle user)’
– AND se.status = ‘ACTIVE’
– AND sid = 397
– AND plsql_entry_object_id IS NOT NULL
ORDER BY se.sid;
This query returns the queries currently being run against the database. An example of a query run in this period is here:
SELECT META.SDO_LAYER_GTYPE FROM ALL_INDEXES INFO INNER JOIN MDSYS.ALL_SDO_INDEX_METADATA META ON INFO.INDEX_NAME = META.SDO_INDEX_NAME WHERE INFO.TABLE_NAME = :1 AND REPLACE(meta.sdo_column_name, ‘"’) = :2
Can somebody please(!!) explain what’s going on here? Have anyone ever tested Geoserver against an Oracle 12c database? It seems to me, that it’s trying to validate all database tables connected to the Oracle layers. That’s fine by me - but not if it now makes the server take forever to start. Honestly, even though it’s a large amount of layers, it really shouldn’t take as much as 70 minutes to validate 6000 Oracle tables. It seems like you’re running some insanely heavy queries against the database at server startup.
Can this eventually be disabled by disabling the option “validate connections” on the data store? What exactly does this option do?
I really hope to hear from someone knowledgable about this.
Thanks in advance,
Jacob Madsen