25.1.1.Oracle Database End-to-End Tracing

Oracle Database end-to-end application tracing simplifies diagnosingapplication code flow and performance problems in multi-tier or multi-userenvironments.

The connection attributesConnection.client_identifier,Connection.clientinfo,Connection.dbop,Connection.module, andConnection.action set metadata forend-to-end tracing. The values can be queried from data dictionary and dynamicperformance views to monitor applications, or you can use tracingutilities. Values may appear in logs and audit trails.

Also seeConnection Metadata and Application Contexts for information about setting Application Contexts.

TheConnection.client_identifier attribute is typically set to the name(or identifier) of the actual end user initiating a query. This allows thedatabase to distinguish, and trace, end users for applications that connectusing a common database username. It can also be used byOracle VirtualPrivate Database (VPD) policies to automatically limitdata access. Oracle Database’sDBMS_MONITOR package can take advantage of theclient identifer to enable statistics and tracing at an individual level.

TheConnection.module andConnection.action attributes can beset to user-chosen, descriptive values identifying your code architecture.

After attributes are set, the values are sent to the database when the nextround-trip to the database occurs, for example when thenext SQL statement is executed.

The attribute values will remain set in connections released back to aconnection pool. When the application re-acquires a connection from the pool,it should initialize the values to a desired state before using thatconnection.

The example below shows setting the action, module, and client identifierattributes on a connection object, and then querying a view to see the recordedvalues. The example both sets and queries the values, but typically monitoringis done externally to the application.

# Set the tracing metadataconnection.client_identifier="pythonuser"connection.action="Query Session tracing parameters"connection.module="End-to-end Demo"forrowincursor.execute("""        SELECT username, client_identifier, module, action        FROM V$SESSION        WHERE sid = SYS_CONTEXT('USERENV', 'SID')"""):print(row)

The output will be like:

('SYSTEM','pythonuser','End-to-end Demo','Query Session tracing parameters')

The values can also be manually set by callingDBMS_APPLICATION_INFO procedures orDBMS_SESSION.SET_IDENTIFIER. These incurround-trips to the database which reduces application scalability:

BEGINDBMS_SESSION.SET_IDENTIFIER('pythonuser');DBMS_APPLICATION_INFO.set_module('End-to-End Demo');DBMS_APPLICATION_INFO.set_action(action_name=>'Query Session tracing parameters');END;

TheConnection.dbop attribute can be used for Real-Time SQL Monitoring,seeMonitoring Database Operations. The value willbe shown in the DBOP_NAME column of theV$SQL_MONITORview:

connection.dbop="my op"forrowincursor.execute("""        SELECT dbop_name        FROM V$SQL_MONITOR        WHERE sid = SYS_CONTEXT('USERENV', 'SID')"""):print(row)

25.1.2.Debugging PL/SQL with the Java Debug Wire Protocol

The Java Debug Wire Protocol (JDWP) for debugging PL/SQL can be used withpython-oracledb.

Python-oracledb applications that call PL/SQL can step through that PL/SQL codeusing JDWP in a debugger. This allows Python and PL/SQL code to be debugged inthe same debugger environment. You can enable PL/SQL debugging inpython-oracledb as follows:

• In python-oracledb Thin or Thick modes, set theORA_DEBUG_JDWPenvironment variable tohost=hostname;port=portnum indicating where thePL/SQL debugger is running. Then run the application.

• In python-oracledb Thin mode, you can alternatively set the connectionparameterdebug_jdwp during connection. This variable defaults to thevalue of theORA_DEBUG_JDWP environment variable.

See the documentation onDBMS_DEBUG_JDWP, the videoPL/SQL debugging with Visual Studio andVisual Studio Code, and theblog postDebugging PL/SQL with Visual Studio Code (and more).

25.1.3.Low Level SQL Tracing

The Thick mode of python-oracledb is implemented using theODPI-C wrapper on top of the Oracle Clientlibraries. The ODPI-C tracing capability can be used to log executedpython-oracledb statements to the standard error stream. Before executingPython, set the environment variableDPI_DEBUG_LEVEL to 16 in your terminalwindow.

On Linux, you might use:

exportDPI_DEBUG_LEVEL=16

On Windows, this could be done with:

setDPI_DEBUG_LEVEL=16

After setting the variable, run the Python Script, for example on Linux:

pythonend-to-endtracing.py2>log.txt

For an application that does a single query, the log file might contain atracing line consisting of the prefix ‘ODPI’, a thread identifier, a timestamp,and the SQL statement executed:

ODPI[23389068]2025-06-2512:07:55.405:ODPI-C5.5.1ODPI[23389068]2025-06-2512:07:55.405:debuggingmessagesinitializedatlevel16ODPI[23389068]2025-06-2512:08:01.363:SQLselectnamefromjobs

SeeODPI-C Debugging fordocumentation onDPI_DEBUG_LEVEL.

25.1.4.Using Connection Identifiers

A unique connection identifier (CONNECTION_ID) is generated for eachconnection to the Oracle Database. The connection identifier is shown in someOracle Network error messages and logs, which helps in better tracing anddiagnosing of connection failures. For example:

DPY-6005:cannotconnecttodatabase(CONNECTION_ID=m0PfUY6hYSmWPcgrHZCQIQ==)

Depending on the Oracle Database version in use, the information that is shownin logs varies.

You can define a prefix value which is added to the beginning of theCONNECTION_ID value. This prefix aids in identifying the connections from aspecific application.

SeeTroubleshooting Oracle Net Services for moreinformation on connection identifiers.

Python-oracledb Thin mode

In python-oracledb Thin mode, you can specify a prefix using theconnection_id_prefix parameter when creatingstandaloneconnections orpooledconnections,or alternatively set a prefix when callingoracledb.ConnectParams() ororacledb.PoolParams(). For example:

connection=oracledb.connect(user="hr",password=userpwd,dsn="localhost/orclpdb",connection_id_prefix="MYAPP")

If this connection to the database fails,MYAPP is added as a prefix to theCONNECTION_ID value shown in the error message, for example:

DPY-6005:cannotconnecttodatabase(CONNECTION_ID=MYAPPm0PfUY6hYSmWPcgrHZCQIQ==).

Python-oracledb Thick mode

In python-oracledb Thick mode, you can specify the connection identifier prefixin the connection string or connect descriptor. For example:

mydb=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=...)(ADDRESS=...))(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)(CONNECTION_ID_PREFIX=MYAPP)))

25.1.5.Tracing Bind Values

Several methods for tracing bind variable values can be used. When tracing bindvariable values, be careful not to leak information and create a securityproblem.

In Oracle Database, the viewV$SQL_BIND_CAPTUREcan capture bind information. Tracing with Oracle Database’sDBMS_MONITORpackage may also be useful.

You can additionallysubclass python-oracledb classes andlog any bind values.

OpenTelemetry can also be used, seeUsing python-oracledb with OpenTelemetry.

25.1.6.Database Views for Tracing python-oracledb

This section shows some of the Oracle Database views useful for tracing andmonitoring python-oracledb. Other views and columns not described here alsocontain useful information, such as theDatabase Resident Connection Pooling (DRCP) views discussed inMonitoring DRCP, and the views discussed inOracle Database End-to-End Tracing andTracing Bind Values.

25.2.1.Example of Using python-oracledb with OpenTelemetry

This example shows a python-oracledb application using OpenTelemetry’sConsoleSpanExporter exporter to display trace information to the console.

Installing OpenTelemetry Modules

For this example, install:

python-mpipinstallopentelemetry-sdkopentelemetry-apiopentelemetry-instrumentation-dbapi

Sample Application

This simple application performs two queries in a custom span. It also sets theservice name and system attributes to user-chosen values. It uses thecapture_parameters option to enable bind variable tracing.

The sample code is:

importoracledbfromopentelemetryimporttracefromopentelemetry.sdk.traceimportTracerProviderfromopentelemetry.sdk.trace.exportimport(BatchSpanProcessor,ConsoleSpanExporter,)fromopentelemetry.sdk.resourcesimportResourceuser="hr"password=userpwdhost="dbhost.example.com"service_name="orclpdb"resource=Resource(attributes={"service.name":service_name,# displayed as a resource attribute "service.name"})provider=TracerProvider(resource=resource)processor=BatchSpanProcessor(ConsoleSpanExporter())provider.add_span_processor(processor)trace.set_tracer_provider(provider)fromopentelemetry.instrumentation.dbapiimporttrace_integrationtrace_integration(oracledb,connect_method_name="connect",database_system="oracle",# displayed as attribute "db.system"capture_parameters=True,# displayed as attribute "db.statement.parameters"# WARNING: this shows bind variable values)connection=oracledb.connect(user=user,password=password,host=host,service_name=service_name)withconnection.cursor()ascursor:tracer=trace.get_tracer("HR-tracer-name")withtracer.start_as_current_span("HR-span-1")asspan:sql="select city from locations where location_id = :1"forr,incursor.execute(sql,[2200]):print(r)sql="select 'Hello World!' from dual"forr,incursor.execute(sql):print(r)

Sample Output

The sample output will be like:

SydneyHello World!{    "name": "select",    "context": {        "trace_id": "0xb24817cd2ea38ffa523c2ee2778508f7",        "span_id": "0xacfd82ed60e8976d",        "trace_state": "[]"    },    "kind": "SpanKind.CLIENT",    "parent_id": "0x19027598c301cfac",    "start_time": "2025-05-29T08:40:10.194645Z",    "end_time": "2025-05-29T08:40:10.209815Z",    "status": {        "status_code": "UNSET"    },    "attributes": {        "db.system": "oracle",        "db.name": "",        "db.statement": "select city from locations where location_id = :1",        "db.statement.parameters": "[2200]"    },    "events": [],    "links": [],    "resource": {        "attributes": {            "service.name": "orclpdb1"        },        "schema_url": ""    }}{    "name": "select",    "context": {        "trace_id": "0xb24817cd2ea38ffa523c2ee2778508f7",        "span_id": "0x376dff430f66b14f",        "trace_state": "[]"    },    "kind": "SpanKind.CLIENT",    "parent_id": "0x19027598c301cfac",    "start_time": "2025-05-29T08:40:10.210799Z",    "end_time": "2025-05-29T08:40:10.214694Z",    "status": {        "status_code": "UNSET"    },    "attributes": {        "db.system": "oracle",        "db.name": "",        "db.statement": "select 'Hello World!' from dual"    },    "events": [],    "links": [],    "resource": {        "attributes": {            "service.name": "orclpdb1"        },        "schema_url": ""    }}{    "name": "HR-span-1",    "context": {        "trace_id": "0xb24817cd2ea38ffa523c2ee2778508f7",        "span_id": "0x19027598c301cfac",        "trace_state": "[]"    },    "kind": "SpanKind.INTERNAL",    "parent_id": null,    "start_time": "2025-05-29T08:40:10.194536Z",    "end_time": "2025-05-29T08:40:10.214732Z",    "status": {        "status_code": "UNSET"    },    "attributes": {},    "events": [],    "links": [],    "resource": {        "attributes": {            "service.name": "orclpdb1"        },        "schema_url": ""    }}

The two query results precede OpenTelemetry’s tracing. The tracing then shows:

• The start and end time of each operation.

• Each “select” trace block’s association to the span “HR-span-1” via theirparent_id values, which match the span’sspan_id value.

• The bind variable value2200 in the attributedb.statement.parameters.Warning: it is a security risk to monitor bindvariable values this way. Keep thecapture_parameters option set toFalse.

• The system and service name as set in the application.

The Python OpenTelemetry modules allow further customization for tracing. Seetheir documentation for more information.

When a graphical provider is used instead of ConsoleSpanExporter, the databasequery relationships and timings are easier to analyze.