Methods

O&M engineers can use related methods in the PL/SQL package DBMS_MONITOR to enable applications to determine whether to enable end-to-end diagnostics trace based on different identifiers, and the policy of recording the trace information in trace logs. The sampling frequency determines whether logs are sampled and recorded in the memory. Logs may also be recorded in the trace log file.

Enable and disable trace

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

Variables in the methods are described as follows:

• session_id: the session ID. You can run the show processlist command (MySQL mode) to query views such as GV$OB_PROCESSLIST for the session ID.

• client_id: the client ID. You can query the GV$OB_PROCESSLIST view for the client ID based on the client field.

• module_name: the module name. You can query the GV$OB_PROCESSLIST view for the module name based on the module field.

• action_name: the action name. You can query the GV$OB_PROCESSLIST view for the action name based on the action field.

• tenant_name: the tenant name. Valid values are NULL and actual tenant names. A non-NULL value indicates the current tenant.

• level: the log printing level. Three log printing levels are supported. Level1 indicates log printing at the module level and Level3 indicates log printing at the most fine-grained level.

• sample_pct: the sampling frequency. The value range is [0, 1].

• record_policy: the strategy of recording trace information in log files. Valid values:

  • ALL: The information about all spans and tags is recorded in log files. Specifically, the information is logged when each span ends.

  • ONLY_SLOW_QUERY: If the current request is a slow query, its span and tag information are recorded in a log file.

  • SAMPLE_AND_SLOW_QUERY: If the current request is a slow query, its span and tag information are recorded in a log file. The span and tag information of other requests may also be recorded in a 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: Execute the following SQL statement to record the time consumption information of the current session for only slow queries and set the sampling frequency to 50%:

  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 only for slow queries whose client_id is jdbc and set the sampling frequency to 50%.

  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 only for slow queries whose module name is backup and action name is insert, and set the sampling frequency to 50%. You can 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.

  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 recorded in the trace.log file. The following example shows the content of a trace.log file:

[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, see Trace query.