Tracing the Oracle Health Insurance 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 gains a connection from the pool, application-related information 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 and many Oracle Database performance views and often report in trace files.

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

Table 1. Tracing the Oracle Health Insurance Database Activity
Use Case Module Action

OHI HTTP API Resources

URI path of the OHI Resource

The HTTP method.

ADF UI

Page Id

The HTTP method.

Task Processing

Task Types Reference Code

The task Subject Code. For example, for Claims this is the Claim Code.

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:

  • Query execution 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 from the previous section can be through either of the following methods:

  • Based on the combination of module and action. For example, by calling DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.

  • Based on the ClientId. For example, by calling DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE.

Database tracing usage notes:

  • Enable the database tracing for diagnostic only.

  • A database trace writes typically to multiple trace files. Use the trcsess tool to collect data into a single file.

  • All instances enable tracing and is persistent across restarts. Check enabled traces by executing the following code:

select * from DBA_ENABLED_TRACES

See the Oracle Database documentation for additional information.