Thanks for the suggestions, Jonathan.
- We did try cranking up the logging level, but I think the problematic
queries are not logged. We didn't see them, anyway
- The SQL history tip looks very useful...
- Did think about a wire sniffer, but we don't have that level of access.
And it seems like it might be hard to interpret the raw wire data...
In the end we were able to enable DB session tracing and that gave us what
we needed to identify the problem. For reference, here's what we did:
* Put this into the datastore Session Startup SQL:
BEGIN; DBMS_SESSION.SET_IDENTIFIER('GEOSERVER'); END;
This puts “GEOSERVER” in the user session variable CLIENT_IDENTIFIER.
* In the database, a DBA needs to do this to start tracing:
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'GEOSERVER');
* Run the GeoServer query.
* Stop tracing:
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'GEOSERVER');
On Wed, Sep 2, 2015 at 3:57 AM, Jonathan Moules <jonat...@lightpear.com>
wrote:
> Hi Martin,
> I'll leave the driver level suggestions to those more knowledgeable, but
> have you tried:
>
> - Setting the GeoServer /Geotools logging level to GeoTools developer
> logging? In my experience this seems to log most things, including some of
> the metadata queries
> - Ask Oracle what it has been up to recently:
> http://stackoverflow.com/questions/14830875/find-out-the-history-of-sql-queries
> - Failing that, maybe try using some packet sniffer intermediate to watch
> the requests as they go by; not sure if fiddler2 can handle this type of
> packet.
>
> Cheers,
> Jonathan
>
>
> On 01/09/2015 20:11, Martin Davis wrote:
>
> In order to diagnose a performance problem [1] we would like to capture
> ALL SQL statements emitted by GeoServer to Oracle. It appears that many of
> the metadata queries are not logged.
>
> Oracle JDBC drivers support logging internally [2]. Has anyone tried
> this, and is it possible to enable this by simply creating a new logging
> properties file in GeoServer? Or is it necessary to follow the more
> complicated configuration documented by Oracle?
>
>
> [1]
> http://osgeo-org.1560.x6.nabble.com/Issue-with-internal-connection-pooling-for-Oracle-datastore-with-no-schema-name-td5217844.html
>
> [2] http://docs.oracle.com/cd/B28359_01/java.111/b31224/diagnose.htm
>
>
> ------------------------------------------------------------------------------
>
>
>
> _______________________________________________
> Geoserver-users mailing
> listGeoserver-users@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>
>
------------------------------------------------------------------------------
Monitor Your Dynamic Infrastructure at Any Scale With Datadog!
Get real-time metrics from all of your servers, apps and tools
in one place.
SourceForge users - Click here to start your Free Trial of Datadog now!
http://pubads.g.doubleclick.net/gampad/clk?id=241902991&iu=/4140
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
