[Geoserver-users] Issue with internal connection pooling for Oracle datastore with no schema name?

GeoServer GeoServer User
1

We have the following GeoServer setup:

Datastore to an Oracle 12c Exadata instance
Internal connection pooling
No schema specified

We noticed a serious performance anomaly, where each layer was taking about 4 s to render, even when the data was very small.

When we switched to specifying an explicit schema in the Datastore config, the performance got significantly faster, and the time to render individual layers became more proportional to the query result size.

So the questions are:

  1. does omitting a schema name in the Oracle Datastore config cause connection pooling to be disabled or defeated?
  2. Will this issue be avoided when using a JNDI connection pool?

If the answer to # 1 is yes, this should probably be highlighted in the docs…

2

On Thu, Jul 30, 2015 at 7:56 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

We have the following GeoServer setup:

Datastore to an Oracle 12c Exadata instance
Internal connection pooling
No schema specified

What GEoServer version?

We noticed a serious performance anomaly, where each layer was taking
about 4 s to render, even when the data was very small.

When we switched to specifying an explicit schema in the Datastore config,
the performance got significantly faster, and the time to render individual
layers became more proportional to the query result size.

So the questions are:
1. does omitting a schema name in the Oracle Datastore config cause
connection pooling to be disabled or defeated?

While I cannot ensure I won't be hit by an asteroid in the next hour,
that's unlikely. So is the idea that not setting
up the catalog can break connection pooling.

It will likely slow down things for other reasons unrelated to connection
pooling instead.
Oracle is a database from hell, if you don't setup the catalog the jdbc
driver returns a huge number of tables (50k on some installations?) when
you get the
database metadata.
We had issues with caching metadata at the content data store level, with
the Oracle dialect not using prepared statements
for metadata (pull request still open, I'm unable to find the time to
review it, see here:
https://github.com/geotools/geotools/pull/905), and likely something else
that I don't remember.

I would suggest to try trunk, with the above pull request applied, and see
if it gets any better.

2. Will this issue be avoided when using a JNDI connection pool?

Not directly, but with JNDI you can setup a single connection pool and then
setup N datastores, one for each
of the catalogs you need to access.

Investigations and patches welcomed too, we have a large disconnect between
people using and complaining
about Oracle and people actually doing something about it. The common
wisdom is to just drop
Oracle in favor of PostGIS, when one can (yes, I'm well aware that's often
not an option).

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V 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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

3

Thanks, Andrea.

We’re using GeoServer 2.6.0

The performance issue occurs for map requests - so wouldn’t this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we’ve seen as well, but it only hurts the admin, not the users, so we’re less caring about that 8^).

But I guess anything might be happening in there. We’re attempting to get tracing on the DB side to see what’s going on, but so far haven’t found a friendly-enough DBA. 8^)

We’ll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we’ll be looking for a code fix - which we can likely get funded and contribute back.

Will communicate any findings we have, to help improve the support for Oracle.

(Postgres does tend to scare people at this particular client, unfortunately. We did float the idea of using a “local data cache” built on top of a special technology ideally suited for this purpose whose initials just happen to be PG… but so far no bites. However, if we can show a major improvement in map image request performance, we might still be able to make this fly…)

···

On Thu, Jul 30, 2015 at 1:42 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Thu, Jul 30, 2015 at 7:56 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

We have the following GeoServer setup:

Datastore to an Oracle 12c Exadata instance
Internal connection pooling
No schema specified

What GEoServer version?

We noticed a serious performance anomaly, where each layer was taking about 4 s to render, even when the data was very small.

When we switched to specifying an explicit schema in the Datastore config, the performance got significantly faster, and the time to render individual layers became more proportional to the query result size.

So the questions are:

  1. does omitting a schema name in the Oracle Datastore config cause connection pooling to be disabled or defeated?

While I cannot ensure I won’t be hit by an asteroid in the next hour, that’s unlikely. So is the idea that not setting
up the catalog can break connection pooling.

It will likely slow down things for other reasons unrelated to connection pooling instead.
Oracle is a database from hell, if you don’t setup the catalog the jdbc driver returns a huge number of tables (50k on some installations?) when you get the
database metadata.
We had issues with caching metadata at the content data store level, with the Oracle dialect not using prepared statements
for metadata (pull request still open, I’m unable to find the time to review it, see here:
https://github.com/geotools/geotools/pull/905), and likely something else that I don’t remember.

I would suggest to try trunk, with the above pull request applied, and see if it gets any better.

  1. Will this issue be avoided when using a JNDI connection pool?

Not directly, but with JNDI you can setup a single connection pool and then setup N datastores, one for each
of the catalogs you need to access.

Investigations and patches welcomed too, we have a large disconnect between people using and complaining
about Oracle and people actually doing something about it. The common wisdom is to just drop
Oracle in favor of PostGIS, when one can (yes, I’m well aware that’s often not an option).

4

An update on this issue.

As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema).

We’re now attempting to do Oracle logging to try and see what’s getting run that might slow a map request down. (We have only limited access to the box where the problem shows up). Results are not conclusive so far, but we think we are seeing several queries being run over as many as 5 different “Geoserver sessions” during a single map request. Cannot tell what these are yet, but seems likely they are metadata queries. This is odd, since we are not seeing this happen in another similar environment. One difference is that we are connecting via an Oracle Service rather than a SID in the slow environment. Would be odd if this was the cause, though.

···

On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

Thanks, Andrea.

We’re using GeoServer 2.6.0

The performance issue occurs for map requests - so wouldn’t this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we’ve seen as well, but it only hurts the admin, not the users, so we’re less caring about that 8^).

We’ll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we’ll be looking for a code fix - which we can likely get funded and contribute back.

5

I have a silly suggestion, when not using a schema is the data store getting back an amazingly large number of oracle tables … checking each one for a spatial index and so on?

I would expect that to take a bit longer on startup … but you are indicating that every GetMap request is consistently slow.

···

On 1 September 2015 at 12:46, Martin Davis <mtnclimb@anonymised.com> wrote:

An update on this issue.

As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema).

We’re now attempting to do Oracle logging to try and see what’s getting run that might slow a map request down. (We have only limited access to the box where the problem shows up). Results are not conclusive so far, but we think we are seeing several queries being run over as many as 5 different “Geoserver sessions” during a single map request. Cannot tell what these are yet, but seems likely they are metadata queries. This is odd, since we are not seeing this happen in another similar environment. One difference is that we are connecting via an Oracle Service rather than a SID in the slow environment. Would be odd if this was the cause, though.

Geoserver-users mailing list
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–
Jody Garnett

On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

Thanks, Andrea.

We’re using GeoServer 2.6.0

The performance issue occurs for map requests - so wouldn’t this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we’ve seen as well, but it only hurts the admin, not the users, so we’re less caring about that 8^).

We’ll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we’ll be looking for a code fix - which we can likely get funded and contribute back.

6

Yes, we are suspicious that the metadata queries are returning a lot of rows when no schema is specified. But can’t confirm this is happening, until we can get DB-level tracing enabled.

And as you say, why would this be happening on every GetMap ? And why happening in one environment and not in a similar different one?

···

On Tue, Sep 1, 2015 at 2:13 PM, Jody Garnett <jody.garnett@anonymised.com> wrote:

I have a silly suggestion, when not using a schema is the data store getting back an amazingly large number of oracle tables … checking each one for a spatial index and so on?

I would expect that to take a bit longer on startup … but you are indicating that every GetMap request is consistently slow.

–
Jody Garnett

On 1 September 2015 at 12:46, Martin Davis <mtnclimb@anonymised.com> wrote:

An update on this issue.

As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema).

We’re now attempting to do Oracle logging to try and see what’s getting run that might slow a map request down. (We have only limited access to the box where the problem shows up). Results are not conclusive so far, but we think we are seeing several queries being run over as many as 5 different “Geoserver sessions” during a single map request. Cannot tell what these are yet, but seems likely they are metadata queries. This is odd, since we are not seeing this happen in another similar environment. One difference is that we are connecting via an Oracle Service rather than a SID in the slow environment. Would be odd if this was the cause, though.

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

Thanks, Andrea.

We’re using GeoServer 2.6.0

The performance issue occurs for map requests - so wouldn’t this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we’ve seen as well, but it only hurts the admin, not the users, so we’re less caring about that 8^).

We’ll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we’ll be looking for a code fix - which we can likely get funded and contribute back.

7

So are you in position to profile the code? We recently enabled the database online tests again, but they test conformance, not performance.

···

On 1 September 2015 at 14:38, Martin Davis <mtnclimb@anonymised.com> wrote:

Yes, we are suspicious that the metadata queries are returning a lot of rows when no schema is specified. But can’t confirm this is happening, until we can get DB-level tracing enabled.

And as you say, why would this be happening on every GetMap ? And why happening in one environment and not in a similar different one?

–
Jody Garnett

On Tue, Sep 1, 2015 at 2:13 PM, Jody Garnett <jody.garnett@anonymised.com> wrote:

I have a silly suggestion, when not using a schema is the data store getting back an amazingly large number of oracle tables … checking each one for a spatial index and so on?

I would expect that to take a bit longer on startup … but you are indicating that every GetMap request is consistently slow.

–
Jody Garnett

On 1 September 2015 at 12:46, Martin Davis <mtnclimb@anonymised.com> wrote:

An update on this issue.

As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema).

We’re now attempting to do Oracle logging to try and see what’s getting run that might slow a map request down. (We have only limited access to the box where the problem shows up). Results are not conclusive so far, but we think we are seeing several queries being run over as many as 5 different “Geoserver sessions” during a single map request. Cannot tell what these are yet, but seems likely they are metadata queries. This is odd, since we are not seeing this happen in another similar environment. One difference is that we are connecting via an Oracle Service rather than a SID in the slow environment. Would be odd if this was the cause, though.

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis <mtnclimb@anonymised.com4…> wrote:

Thanks, Andrea.

We’re using GeoServer 2.6.0

The performance issue occurs for map requests - so wouldn’t this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we’ve seen as well, but it only hurts the admin, not the users, so we’re less caring about that 8^).

We’ll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we’ll be looking for a code fix - which we can likely get funded and contribute back.

8

We’re working on profiling from the DB side. It’s difficult to get a debug GeoServer setup against the environment showing the problem.

···

On Tue, Sep 1, 2015 at 2:47 PM, Jody Garnett <jody.garnett@…84…> wrote:

So are you in position to profile the code? We recently enabled the database online tests again, but they test conformance, not performance.

–
Jody Garnett

On 1 September 2015 at 14:38, Martin Davis <mtnclimb@anonymised.com> wrote:

Yes, we are suspicious that the metadata queries are returning a lot of rows when no schema is specified. But can’t confirm this is happening, until we can get DB-level tracing enabled.

And as you say, why would this be happening on every GetMap ? And why happening in one environment and not in a similar different one?

On Tue, Sep 1, 2015 at 2:13 PM, Jody Garnett <jody.garnett@anonymised.com.84…> wrote:

I have a silly suggestion, when not using a schema is the data store getting back an amazingly large number of oracle tables … checking each one for a spatial index and so on?

I would expect that to take a bit longer on startup … but you are indicating that every GetMap request is consistently slow.

–
Jody Garnett

On 1 September 2015 at 12:46, Martin Davis <mtnclimb@anonymised.com> wrote:

An update on this issue.

As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema).

We’re now attempting to do Oracle logging to try and see what’s getting run that might slow a map request down. (We have only limited access to the box where the problem shows up). Results are not conclusive so far, but we think we are seeing several queries being run over as many as 5 different “Geoserver sessions” during a single map request. Cannot tell what these are yet, but seems likely they are metadata queries. This is odd, since we are not seeing this happen in another similar environment. One difference is that we are connecting via an Oracle Service rather than a SID in the slow environment. Would be odd if this was the cause, though.

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis <mtnclimb@anonymised.com4…> wrote:

Thanks, Andrea.

We’re using GeoServer 2.6.0

The performance issue occurs for map requests - so wouldn’t this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we’ve seen as well, but it only hurts the admin, not the users, so we’re less caring about that 8^).

We’ll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we’ll be looking for a code fix - which we can likely get funded and contribute back.

9

We’ve run some DB logging on the map requests.

One of the causes of the slowdown for no schema is this query:

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’, ‘SYNONYM’)
ORDER BY table_type, table_schem, table_name

If no owner is specified this returns 38K rows, as opposed to about 240 when owner is given.

I’m not totally sure what this is doing, but I suspect checking for Primary Key info?

And why is this being run on each MapRequest ?

Funnily, we have 2.6.3 running in a different setup, and it doesn’t seem to be issuing any of these queries - just the data query. I can’t see any config difference that would cause this.

Any ideas?

···

On Tue, Sep 1, 2015 at 12:46 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

An update on this issue.

As Andrea predicted, using a JNDI connection made no difference to the performance issue (no schema still substantially slower than using a schema).

On Fri, Jul 31, 2015 at 10:02 AM, Martin Davis <mtnclimb@anonymised.com…> wrote:

Thanks, Andrea.

We’re using GeoServer 2.6.0

The performance issue occurs for map requests - so wouldn’t this be something different to the issue with slow metadata loading (The metadata retrieval is an issue we’ve seen as well, but it only hurts the admin, not the users, so we’re less caring about that 8^).

We’ll probably try using a JNDI pool and see whether that helps at all. If so, we may just use that approach. If not, we’ll be looking for a code fix - which we can likely get funded and contribute back.

10

On Tue, Sep 1, 2015 at 11:13 PM, Jody Garnett <jody.garnett@anonymised.com>
wrote:

I have a silly suggestion, when not using a schema is the data store
getting back an amazingly large number of oracle tables .. checking each
one for a spatial index and so on?

GeoServer allows to publish non spatial tables, which is used/useful to
publish non spatial information via WFS, checking for spatial indexes
would result in a regression.
What we could have though, is a new datastore parameter that makes the
store only return spatial tables

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V 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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

11

On Wed, Sep 2, 2015 at 1:21 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

We've run some DB logging on the map requests.

One of the causes of the slowdown for no schema is this query:

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', 'SYNONYM')
  ORDER BY table_type, table_schem, table_name

If no owner is specified this returns 38K rows, as opposed to about 240
when owner is given.

I'm not totally sure what this is doing, but I suspect checking for
Primary Key info?

And why is this being run on each MapRequest ?

Funnily, we have 2.6.3 running in a different setup, and it doesn't seem
to be issuing any of these queries - just the data query. I can't see any
config difference that would cause this.

Maybe not so funny, I remember of a major performance regression in JDBC
stores in 2.6.0,
indeed it was very slow against spatial databases, see the 2.6.1 release
notes:
http://blog.geoserver.org/2014/11/18/geoserver-2-6-1-released/

Discussion here:
http://osgeo-org.1560.x6.nabble.com/Severe-slowdown-in-ContentDataStore-in-12-x-and-trunk-td5167030.html

To avoid this kind of issue, besides more review, which never hurts but
requires more staff, we'd need
continuous performance testing and comparison against production sized
databases, something
I've been proposing on and off for several years, but that never got the
resourcing it needs to make it happen.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V 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

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

12

Thanks, Andrea, that sounds like the cause. We’ll upgrade and see if the problem goes away.

···

On Tue, Sep 1, 2015 at 11:25 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Wed, Sep 2, 2015 at 1:21 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

We’ve run some DB logging on the map requests.

One of the causes of the slowdown for no schema is this query:

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’, ‘SYNONYM’)
ORDER BY table_type, table_schem, table_name

If no owner is specified this returns 38K rows, as opposed to about 240 when owner is given.

I’m not totally sure what this is doing, but I suspect checking for Primary Key info?

And why is this being run on each MapRequest ?

Funnily, we have 2.6.3 running in a different setup, and it doesn’t seem to be issuing any of these queries - just the data query. I can’t see any config difference that would cause this.

Maybe not so funny, I remember of a major performance regression in JDBC stores in 2.6.0,
indeed it was very slow against spatial databases, see the 2.6.1 release notes:
http://blog.geoserver.org/2014/11/18/geoserver-2-6-1-released/

Discussion here:
http://osgeo-org.1560.x6.nabble.com/Severe-slowdown-in-ContentDataStore-in-12-x-and-trunk-td5167030.html

To avoid this kind of issue, besides more review, which never hurts but requires more staff, we’d need
continuous performance testing and comparison against production sized databases, something
I’ve been proposing on and off for several years, but that never got the resourcing it needs to make it happen.