24. Tracing python-oracledb
24.1. Application Tracing
There are multiple approaches for application tracing and monitoring:
End-to-end database tracing attributes such as
Connection.action
andConnection.module
are supported in the python-oracledb Thin and Thick modes. Using these attributes is recommended since they aid application monitoring and troubleshooting.You can subclass python-oracledb classes and implement your own driver API call tracing and logging. Also, the standard Python tracing capabilities can be used.
The Java Debug Wire Protocol (JDWP) for debugging PL/SQL can be used. See Debugging PL/SQL with the Java Debug Wire Protocol.
Python-oracledb in Thick mode can dump a trace of SQL statements executed. See Low Level SQL Tracing.
The connection identifiers that appear in the traces and logs can be used to resolve connectivity errors. See Using Connection Identifiers.
24.1.1. Oracle Database End-to-End Tracing
Oracle Database end-to-end application tracing simplifies diagnosing application code flow and performance problems in multi-tier or multi-user environments.
The connection attributes Connection.client_identifier
,
Connection.clientinfo
, Connection.dbop
,
Connection.module
, and Connection.action
set metadata for
end-to-end tracing. The values can be queried from data dictionary and dynamic
performance views to monitor applications, or you can use tracing
utilities. Values may appear in logs and audit trails.
Also see Connection Metadata and Application Contexts for information about setting Application Contexts.
The Connection.client_identifier
attribute is typically set to the
name (or identifier) of the actual end user initiating a query. This allows
the database to distinguish, and trace, end users for applications that connect
to a common database username. It can also be used by Oracle Virtual Private
Database (VPD) policies to automatically limit
data access.
The Connection.module
and Connection.action
attributes can be
set to user-chosen, descriptive values identifying your code architecture.
After attributes are set, the values are sent to the database when the next round-trip to the database occurs, for example when the next SQL statement is executed.
The attribute values will remain set in connections released back to a connection pool. When the application re-acquires a connection from the pool, it should initialize the values to a desired state before using that connection.
The example below shows setting the action, module, and client identifier attributes on a connection object, and then querying a view to see the recorded values. The example both sets and queries the values, but typically monitoring is done externally to the application.
# Set the tracing metadata
connection.client_identifier = "pythonuser"
connection.action = "Query Session tracing parameters"
connection.module = "End-to-end Demo"
for row in cursor.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 calling DBMS_APPLICATION_INFO procedures or DBMS_SESSION.SET_IDENTIFIER. These incur round-trips to the database which reduces application scalability:
BEGIN
DBMS_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;
The Connection.dbop
attribute can be used for Real-Time SQL Monitoring,
see Monitoring Database Operations. The value will
be shown in the DBOP_NAME column of the V$SQL_MONITOR view:
connection.dbop = "my op"
for row in cursor.execute("""
SELECT dbop_name
FROM V$SQL_MONITOR
WHERE sid = SYS_CONTEXT('USERENV', 'SID')"""):
print(row)
24.1.2. Subclassing Connections
Subclassing enables applications to add “hooks” for connection and statement execution. This can be used to alter or log connection and execution parameters, and to extend python-oracledb functionality.
The example below demonstrates subclassing a connection to log SQL execution to a file. This example also shows how connection credentials can be embedded in the custom subclass, so application code does not need to supply them.
class Connection(oracledb.Connection):
log_file_name = "log.txt"
def __init__(self):
connect_string = "hr/hr_password@dbhost.example.com/orclpdb"
self._log("Connect to the database")
return super(Connection, self).__init__(connect_string)
def _log(self, message):
with open(self.log_file_name, "a") as f:
print(message, file=f)
def execute(self, sql, parameters):
self._log(sql)
cursor = self.cursor()
try:
return cursor.execute(sql, parameters)
except oracledb.Error as e:
error_obj, = e.args
self._log(error_obj.message)
raise
connection = Connection()
connection.execute("""
select department_name
from departments
where department_id = :id""", dict(id=270))
The messages logged in log.txt
are:
Connect to the database
select department_name
from departments
where department_id = :id
If an error occurs, perhaps due to a missing table, the log file would contain instead:
Connect to the database
select department_name
from departments
where department_id = :id
ORA-00942: table or view does not exist
In production applications, be careful not to log sensitive information.
See Subclassing.py for an example.
24.1.3. Debugging PL/SQL with the Java Debug Wire Protocol
The Java Debug Wire Protocol (JDWP) for debugging PL/SQL can be used with python-oracledb.
Python-oracledb applications that call PL/SQL can step through that PL/SQL code using JDWP in a debugger. This allows Python and PL/SQL code to be debugged in the same debugger environment. You can enable PL/SQL debugging in python-oracledb as follows:
In python-oracledb Thin or Thick modes, set the
ORA_DEBUG_JDWP
environment variable to host=hostname;port=portnum indicating where the PL/SQL debugger is running. Then run the application.In python-oracledb Thin mode, you can alternatively set the connection parameter
debug_jdwp
during connection. This variable defaults to the value of theORA_DEBUG_JDWP
environment variable.
See the documentation on DBMS_DEBUG_JDWP, the video PL/SQL debugging with Visual Studio and Visual Studio Code, and the blog post Debugging PL/SQL with Visual Studio Code (and more).
24.1.4. Low Level SQL Tracing
The Thick mode of python-oracledb is implemented using the
ODPI-C wrapper on top of the Oracle Client
libraries. The ODPI-C tracing capability can be used to log executed
python-oracledb statements to the standard error stream. Before executing
Python, set the environment variable DPI_DEBUG_LEVEL
to 16 in your terminal
window.
On Linux, you might use:
export DPI_DEBUG_LEVEL=16
On Windows, this could be done with:
set DPI_DEBUG_LEVEL=16
After setting the variable, run the Python Script, for example on Linux:
python end-to-endtracing.py 2> log.txt
For an application that does a single query, the log file might contain a tracing line consisting of the prefix ‘ODPI’, a thread identifier, a timestamp, and the SQL statement executed:
ODPI [26188] 2019-03-26 09:09:03.909: ODPI-C 3.1.1
ODPI [26188] 2019-03-26 09:09:03.909: debugging messages initialized at level 16
ODPI [26188] 2019-03-26 09:09:09.917: SQL SELECT * FROM jobss
Traceback (most recent call last):
File "end-to-endtracing.py", line 14, in <module>
cursor.execute("select * from jobss")
oracledb.DatabaseError: ORA-00942: table or view does not exist
See ODPI-C Debugging for
documentation on DPI_DEBUG_LEVEL
.
24.1.5. Using Connection Identifiers
A unique connection identifier (CONNECTION_ID
) is generated for each
connection to the Oracle Database. The connection identifier is shown in some
Oracle Network error messages and logs, which helps in better tracing and
diagnosing of connection failures. For example:
DPY-6005: cannot connect to database (CONNECTION_ID=m0PfUY6hYSmWPcgrHZCQIQ==)
You can define a prefix value which is added to the beginning of the
CONNECTION_ID
. This prefix aids in identifying the connections from a
specific application.
In python-oracledb Thin mode, you can specify a prefix in the
connection_id_prefix
parameter when creating
standalone connections
, or
pooled connections
. Also, you can specify
the connection identifier in oracledb.ConnectParams()
or
oracledb.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 the
CONNECTION_ID
as shown in the error message below:
DPY-6005: cannot connect to database (CONNECTION_ID=MYAPPm0PfUY6hYSmWPcgrHZCQIQ==).
In python-oracledb Thick mode, you can specify the connection identifier prefix in a connection string. For example:
mydb = (DESCRIPTION =
(ADDRESS_LIST= (ADDRESS=...) (ADDRESS=...))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.example.com)
(CONNECTION_ID_PREFIX=MYAPP)
)
)
Depending on the Oracle Database version in use, the information that is shown in logs varies.
See Troubleshooting Oracle Net Services for more information on connection identifiers.
24.2. Finding the python-oracledb Mode
The boolean attributes Connection.thin
and ConnectionPool.thin
can be used to show the current mode of a python-oracledb connection or pool,
respectively. The python-oracledb version can be shown with
oracledb.__version__
.
The information can also be seen in the Oracle Database data dictionary table V$SESSION_CONNECT_INFO:
with connection.cursor() as cursor:
sql = """SELECT UNIQUE client_driver
FROM V$SESSION_CONNECT_INFO
WHERE sid = SYS_CONTEXT('USERENV', 'SID')"""
for r, in cursor.execute(sql):
print(r)
In python-oracledb Thin mode, the output will be like:
python-oracledb thn : 3.0.0
In python-oracledb Thick mode, the output will be like:
python-oracledb thk : 3.0.0
Note that you may not see these values if you have set
oracledb.defaults.driver_name
or the
driver_name
parameter in oracledb.init_oracle_client()
.
24.3. Database Views
This section shows some sample column values for database views useful for tracing and monitoring python-oracledb. Other views also contain useful information, such as the Database Resident Connection Pooling (DRCP) views discussed in Monitoring DRCP.
24.3.1. V$SESSION_CONNECT_INFO
The following table lists sample default values for some
V$SESSION_CONNECT_INFO columns. You may not see
values with these formats if you have changed the defaults using the
Defaults object, set the equivalent connection or pool
creation parameters, or set the driver_name
parameter in
oracledb.init_oracle_client()
.
Column |
Thick value |
Thin value |
---|---|---|
CLIENT_OCI_LIBRARY |
The Oracle Client or Instant Client type, such as “Full Instant Client” |
“Unknown” |
CLIENT_VERSION |
The Oracle Client library version number |
“3.0.0.0.0” (the python-oracledb version number with an extra .0.0) |
CLIENT_DRIVER |
“python-oracledb thk : 3.0.0” |
“python-oracledb thn : 3.0.0” |
24.3.2. V$SESSION
The following table lists sample default values for columns with differences in
V$SESSION. You may not see values with
these formats if you have changed the defaults using the
Defaults object, set the equivalent connection or pool
creation parameters, or set the attribute Connection.module
as
shown in Oracle Database End-to-End Tracing.
Column |
Thick value |
Thin value |
---|---|---|
TERMINAL |
similar to ttys001 |
the string “unknown” |
PROGRAM |
similar to python@myuser-mac2 (TNS V1-V3) |
the contents of Python’s |
MODULE |
similar to python@myuser-mac2 (TNS V1-V3) |
the contents of Python’s |
24.4. Low Level Python-oracledb Driver Tracing
Low level tracing is mostly useful to maintainers of python-oracledb.
For python-oracledb Thin mode, packets can be traced by setting the environment variable PYO_DEBUG_PACKETS in your terminal window before running your application.
For example, on Linux, you might use:
export PYO_DEBUG_PACKETS=1
On Windows you might set the variable like:
set PYO_DEBUG_PACKETS=1
Alternatively, the variable can be set in the application:
import os os.environ["PYO_DEBUG_PACKETS"] = "1" import oracledb
The output goes to stdout. The information logged is roughly similar to an Oracle Net trace of level 16, see Oracle Net Services TRACE_LEVEL_CLIENT.
Python-oracledb Thick mode can be traced using:
DPI_DEBUG_LEVEL as documented in ODPI-C Debugging.
Oracle Call Interface (OCI) tracing as directed by Oracle Support.
Oracle Net services tracing as documented in Oracle Net Services Tracing Parameters.