[opencms-dev] Monitoring Oracle 11g database access

[Jose Ignacio Yarza]

Hi again,

 

 

Has anyone tried the dbms_monitor package to monitor OpenCMS database access
over Oracle 11g?

 

Is this the correct way to get a connection from the OpenCMS pool?

 

Connection dbConnection = OpenCms.getSqlManager().getConnection("default")

 

And, did it work for you?

 

 

Thanks,

Jose

 

 

De: opencms-dev-bounces at opencms.org [mailto:opencms-dev-bounces at opencms.org]
En nombre de Jose Ignacio Yarza
Enviado el: miércoles, 31 de julio de 2013 13:05
Para: opencms-dev at opencms.org
Asunto: [opencms-dev] Monitoring Oracle 11g database access

 

Hi List,

 

Environment: OpenCMS 8.5, Oracle 11g

 

We want to monitor database access within a custom JSP that uses the OpenCMS
taglib/API. What we need is to generate an Oracle trace file with all the
SQL instructions that happen within that particular JSP, regardless if they
belong to different database connections within a pool.

 

The issue is that we don’t get any info in the trace file about the OpenCMS
queries. We are trying this:

 

<%

String CLIENT_ID = String.valueOf(new Date().getTime());

Connection dbConnection = OpenCms.getSqlManager().getConnection("default");


CallableStatement cstmt1 = dbConnection.prepareCall("{call
dbms_session.set_identifier(?)}");

cstmt1.setString(1, CLIENT_ID);

cstmt1.executeUpdate();

 

CallableStatement cstmt2 = dbConnection.prepareCall("{call
dbms_monitor.client_id_trace_enable(?)}");

cstmt2.setString(1, CLIENT_ID);               

cstmt2.executeUpdate();           

 

CmsJspActionElement cjae = new CmsJspActionElement(pageContext, request,
response);                                                  

CmsObject cms = cjae.getCmsObject();

CmsRequestContext crc = cms.getRequestContext();

 

(
) // some JSP code accesing contents

 

CallableStatement cstmt3 = dbConnection.prepareCall("{call
dbms_monitor.client_id_trace_disable(?)}");

cstmt3.setString(1, CLIENT_ID);

cstmt3.executeUpdate();

 

(
) closing stuff

%>

 

 

All we get in the Oracle trace file are the explicit procedure calls. I am
not very familiar with this Oracle technique. Anyone has tried this? Why are
we not getting any trace from the api internal queries? Might be because
they are happening in another connection or session or something else?

 

Any comment is welcome. Thanks.

 

 

Jose Ignacio Yarza

Open Sistemas

Tel: 649 157 537

 

logo_open

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20130812/a38d0f4c/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 7887 bytes
Desc: not available
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20130812/a38d0f4c/attachment.jpg>