Tracing OHI database activity

An Oracle Health Insurance application’s middle tier processes connect to an Oracle database through a JDBC connection pool. Starting with the 3.18.2.0.x major version of Oracle Health Insurance applications, every time an application process acquires a connection from the pool, application related information is set for the following attributes:

  • Module: name of the currently executing module

  • Action: name of the currently executing action

  • ClientId: client identifier

These values appear in the V$SESSION database view as well as in many Oracle Database performance views and can be reported in trace files.

For various use cases in Oracle Health Insurance applications the attributes are set as follows:

Use Case Module Action

OHI SOAP Services

Name of the SOAP Service

SOAP service method

OHI HTTP API Resources

URI path of the OHI Resource

HTTP method

ADF UI

Page Id

HTTP method

Task Processing

Task Types Reference Code

Task 'Subject Code', e.g. for Oracle Health Insurance Claims this is the code of a Claim

In all cases, the ClientId is set to the (primary key) Id of the Oracle Health Insurance User that executes the request.

Setting these values makes it possible to trace, for example:

  • queries executed on behalf of a specific user

  • task processing for a specific claim

  • query execution for specific web service requests

Enable Database Tracing

Database tracing based on the attributes that were introduced in the previous paragraph is usually done via either of the following methods:

  • Based on the combination of Module and Action, e.g. by calling DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.

  • Based on the ClientId, e.g. by calling DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE.

Database tracing usage notes:

  • Database tracing should be enabled for diagnostic purposes only.

  • A database trace is typically written to multiple trace files. The trcsess tool can be used to collect data into a single file.

  • Tracing is enabled for all instances and persistent across restarts. Check enabled traces by executing

select * from DBA_ENABLED_TRACES

For additional information see the Oracle Database documentation.