End-to-end tracing methods

O&M engineers can use relevant methods from the PL/SQL package DBMS_MONITOR to control whether end-to-end tracing is enabled for applications across different identification dimensions, as well as to manage how trace points and related information are recorded in the trace log. Log output is determined by the sampling frequency, which decides whether the data is sampled and stored in memory. There is a certain probability that this information will be written to the trace log.

Enable and disable trace

You can call trace-related methods in the DBMS_MONITOR package to enable trace at different levels.

The variables in the methods are defined as follows:

• session_id: the session ID. You can query it by using show processlist (MySQL-compatible mode), GV$OB_PROCESSLIST, and other views.

• client_id: the client ID. You can query it from the client field of GV$OB_PROCESSLIST.

• module_name: the module name. You can query it from the module field of GV$OB_PROCESSLIST.

• action_name: the action name. You can query it from the action field of GV$OB_PROCESSLIST.

• tenant_name: the tenant name. Options: empty value (non-null, indicates the current tenant) or tenant names.

• level: the granularity of log printing. Three levels are supported. Level 1 is coarse-grained at the module level, and Level 3 is the most fine-grained.

• sample_pct: the sampling frequency. Value range: [0, 1].

• record_policy: the log printing strategy, that is, the policy for trace information output to log files. The following three policies are supported:

  • ALL: All span and tag information is printed to log files, and is printed when each span ends.
  • ONLY_SLOW_QUERY: If the current request is a slow query, its span and tag information is printed to the log file.
  • SAMPLE_AND_SLOW_QUERY: If the current request is a slow query, its span and tag information is printed to the log file; for other requests, span and tag information has a certain probability of being printed to the log file.

For more information, see the definition of the DBMS_MONITOR package.

Session-level trace

Enable trace

DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(
  session_id   IN  BINARY_INTEGER DEFAULT NULL,
  level        IN  INT,
  sample_pct   IN  NUMBER,
  record_policy IN VARCHAR2
);

Example: Record the time consumption and other information of the current session, set the sampling frequency to 50%, and record only slow queries:

call dbms_monitor.ob_session_trace_enable(null, 1, 0.5, 'ONLY_SLOW_QUERY');

Disable trace

DBMS_MONITOR.OB_SESSION_TRACE_DISABLE(session_id   IN  BINARY_INTEGER);

Example: Disable trace for the current session.

call dbms_monitor.ob_session_trace_disable(null);

Client identifier-level trace

Enable trace

DBMS_MONITOR.OB_CLIENT_ID_TRACE_ENABLE(
  client_id    IN  VARCHAR2,
  level        IN  INT,
  sample_pct   IN  NUMBER,
  record_policy IN VARCHAR2
);

Example: Record the time consumption information for client_id jdbc, set the sampling frequency to 50%, and record only slow queries.

call dbms_monitor.ob_client_id_trace_enable('jdbc', 1, 0.5, 'ONLY_SLOW_QUERY');

Disable trace

DBMS_MONITOR.OB_CLIENT_ID_TRACE_DISABLE(client_id IN  VARCHAR2);

Example: Disable the trace whose client_id is jdbc.

call dbms_monitor.ob_client_id_trace_disable('jdbc');

Module- or action-level trace

Enable trace

DBMS_MONITOR.OB_MOD_ACT_TRACE_ENABLE(
  module_name     IN VARCHAR2 DEFAULT ANY_MODULE,
  action_name     IN VARCHAR2 DEFAULT ANY_ACTION,
  level        IN  INT,
  sample_pct   IN  NUMBER,
  record_policy IN VARCHAR2
);

Example: Record the time consumption information for module_name backup and action_name insert, set the sampling frequency to 50%, and record only slow queries. Set module_name and action_name by using the SET_MODULE method in the dbms_application_info package.

call dbms_application_info.set_module('backup', 'insert');
call dbms_monitor.ob_mod_act_trace_enable('backup', 'insert', 1, 0.5, 'ONLY_SLOW_QUERY');

Disable trace

DBMS_MONITOR.OB_MOD_ACT_TRACE_DISABLE(
  module_name     IN  VARCHAR2,
  action_name     IN  VARCHAR2 DEFAULT ALL_ACTIONS
);

Example: Disable the trace whose module_name is backup and action_name is insert.

call dbms_monitor.ob_mod_act_trace_disable('backup', 'insert');

Tenant-level trace

Enable trace

DBMS_MONITOR.OB_TENANT_TRACE_ENABLE(
  level        IN  INT,
  sample_pct   IN  NUMBER,
  record_policy IN VARCHAR2
);

Example: Record all time consumption information in the current tenant and print all.

call dbms_monitor.ob_tenant_trace_enable(1, 1, 'ALL');

Disable trace

DBMS_MONITOR.OB_TENANT_TRACE_DISABLE(tenant_name  IN VARCHAR2 DEFAULT NULL);

Example: Disable the trace whose tenant name is ORACLE.

call dbms_monitor.ob_tenant_trace_disable('ORACLE');

Example: Disable trace for the current tenant.

call dbms_monitor.ob_tenant_trace_disable();

Related log information is output to trace.log. Example content:

[20xx-0x-xx 00:07:46.027232] [1751][T1_TNT_L0][T1][YB42AC12050D-0005E42565DA44B2-0-0] {"trace_id":"0005e495-f920-5adc-7cd1-3730afa11dbd","name":"sql_execute","id":"0005e426-2c2b-0755-0000-000000000005","

start_ts":1658707664460666,"end_ts":1658707666027098,"parent_id":"0005e426-3355-05b6-0000-000000000002","is_follow":false}
[20xx-0x-xx 00:07:46.027234] [1751][T1_TNT_L0][T1][YB42AC12050D-0005E42565DA44B2-0-0] {"trace_id":"0005e495-f920-5adc-7cd1-3730afa11dbd","name":"pc_get_plan","id":"0005e426-5aac-d38c-0000-000000000004","start_ts":1658707664460552,"end_ts":1658707664460660,"parent_id":"0005e426-a22f-6cd4-0000-000000000003","is_follow":false}

For more information about end-to-end tracing, see Trace query.