Full-link Diagnostic Trace

Introduction

The full-link diagnostic mechanism of OceanBase Database builds a standard data model between transactions and SQL Trace to expand the features of SQL Trace. This improves the efficiency in locating OBServer full-link problems, and implements tracing and analysis during distributed execution and parallel execution.

O&M engineers can use related methods in the PL program package DBMS_MONITOR or client APIs to enable or disable Full-link Diagnostic Trace based on different identification information in applications, and specify the strategy for printing the trace records and related information to trace logs. Note that whether trace records are printed to trace logs depends on whether the trace records are sampled and recorded in memory based on the sampling frequency. In other words, whether trace records are printed to trace logs is probabilistic.

You can use the PL program packages DBMS_APPLICATION_INFO and DBMS_SESSION to set application identification information in applications. The DBMS_APPLICATION_INFO package is used for setting the module name, handling procedure name, and other client-related information. The DBMS_SESSION package is used for setting the session identifier. After the session identifier, module name, handling procedure name, and other client-related information are set, the OBServer will synchronize the settings to the schema information of the current session. You can query related information in the V$SESSION view.

Prerequisites

To fully explore the Full-link Diagnostic Trace capabilities, we recommend that you use the following versions:

• OBProxy: V4.0.0 or later

• OBServer: V4.0.0 or later

Enable and disable trace collection at different levels

OceanBase Database uses the PL program package DBMS_MONITOR or client APIs to control trace collection at different levels to meet the diagnostic requirements of different scenarios in the production environment.

The DBMS_MONITOR package is used to control trace collection on OBServers. Variables involved in the DBMS_MONITOR program package include:

• session_id: the session ID. You can obtain the session ID by using the SHOW PROCESSLIST command (only in MySQL mode) or querying the GV$OB_PROCESSLIST view.

• client_id: the client ID. You can obtain the client ID by querying the ID field in the GV$OB_PROCESSLIST view.

• module_name: The module name You can obtain the module name by querying the module field in the GV$OB_PROCESSLIST view.

• action_name: the action name. You can obtain the action name by querying the action field in the GV$OB_PROCESSLIST view.

• tenant_name: the tenant name. The value can be empty, which indicates the current tenant, or be set to the name of another 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. Value range: [0,1].

• record_policy: the log printing strategy, which is the strategy for outputting trace information to log files. Three strategies are supported:

  • ALL: All span and tag information is printed to the log file when each span ends.

  • ONLY_SLOW_QUERY: When the current request is a slow query, the span and tag information is printed to the log file.

  • SAMPLE_AND_SLOW_QUERY: When the current request is a slow query, the span and tag information is printed to the log file, and the span and tag information of other requests is printed to the log file with a certain probability.

Enable/Disable trace collection at the session level

You can call the OB_SESSION_TRACE_ENABLE procedure of the DBMS_MONITOR package to enable trace collection for the specified session based on the session ID. Syntax:

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

If the value of the session_id parameter is NULL, trace collection is disabled for the current session.

You can call the OB_SESSION_TRACE_DISABLE procedure of the DBMS_MONITOR package to disable trace collection for the specified session based on the session ID. Syntax:

DBMS_MONITOR.OB_SESSION_TRACE_DISABLE(session_id IN BINARY_INTEGER);

If the value of the session_id parameter is NULL, trace collection is disabled for the current session.

Examples

Enable Full-link Diagnostic Trace at the session level, set the sampling rate to 50%, and record the span and tag information of only slow queries. Then, disable Full-link Diagnostic Trace at the session level.

DBMS package:

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

DBMS_MONITOR.OB_SESSION_TRACE_DISABLE(session_id IN BINARY_INTEGER);

Perform the following steps on OBClient:

# Enable Full-link Diagnostic Trace at the session level, set the sampling rate to 50%, and record the span and tag information of only slow queries.
obclient [SYS]> CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(null,1,0.5,'ONLY_SLOW_QUERY');
Query OK, 0 rows affected
# Disable Full-link Diagnostic Trace at the session level.
obclient [SYS]> CALL DBMS_MONITOR.OB_SESSION_TRACE_DISABLE(null);
Query OK, 0 rows affected

Enable/Disable trace collection at the client ID level

Before you enable trace collection at the client ID level, you must first use the DBMS_SESSION package to set client_id. Syntax:

DBMS_SESSION.SET_IDENTIFIER(client_id VARCHAR2);

You can call the OB_CLIENT_ID_TRACE_ENABLE procedure of the DBMS_MONITOR package to enable trace collection based on the specified client ID. Syntax:

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

You can call the OB_CLIENT_ID_TRACE_DISABLE procedure of the DBMS_MONITOR package to disable trace collection based on the specified client ID. Syntax:

DBMS_MONITOR.OB_CLIENT_ID_TRACE_DISABLE(client_id IN VARCHAR2);

Examples

Enable trace collection at the client ID level, record information such as time consumption in traces with the client ID jdbc, set the sampling rate to 50%, and record the span and tag information of only slow queries. Then, disable trace collection at the client ID level.

DBMS package:

1. Set a client ID.

2. Enable or disable trace collection based on this client ID.

DBMS_SESSION.SET_IDENTIFIER(client_id VARCHAR2);

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

DBMS_MONITOR.OB_CLIENT_ID_TRACE_DISABLE(client_id IN VARCHAR2);

Perform the following steps on OBClient:

# Set a client ID.
obclient [SYS]> CALL DBMS_SESSION.SET_IDENTIFIER('obclient');
Query OK, 0 rows affected
# Enable trace collection based on the specified client ID, record information such as time consumption in traces with the client ID `obclient`, set the sampling rate to 50%, and record the span and tag information of only slow queries.
obclient [SYS]> CALL DBMS_MONITOR.OB_CLIENT_ID_TRACE_ENABLE('obclient',1,0.5,'ONLY_SLOW_QUERY');
Query OK, 0 rows affected
# Disable trace collection based on the specified client ID.
obclient [SYS]> CALL DBMS_MONITOR.OB_CLIENT_ID_TRACE_DISABLE('obclient');
Query OK, 0 rows affected

Enable/Disable trace collection at the module or action level

Before you enable trace collection at the module or action level, you must first use subprocedures in the DBMS_APPLICATION_INFO package to set the module name, handling procedure name, and other client-related information. Subprocedures:

• The SET_MODULE procedure is used to set the name of an application module.

  DBMS_APPLICATION_INFO.SET_MODULE (module_name IN VARCHAR2, action_name IN VARCHAR2);
  

• The SET_ACTION procedure is used to set the name of a handling procedure in the application module.

  DBMS_APPLICATION_INFO.SET_ACTION (action_name IN VARCHAR2);
  

• The SET_CLIENT_INFO procedure is used to set other client-related information.

  DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info IN VARCHAR2);
  

You can call the OB_MOD_ACT_TRACE_ENABLE procedure in the DBMS_MONITOR package to enable trace collection at the module or action level based on the specified module name or action name. Syntax:

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);

You can call the OB_MOD_ACT_TRACE_DISABLE procedure in the DBMS_MONITOR package to disable trace collection at the module or action level based on the specified module name or action name. Syntax:

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

Examples

Record information such as time consumption in traces with the module name backup and the action name insert, set the sampling rate to 50%, and record the span and tag information of only slow queries. Then, disable trace collection for the module named backup or the action named insert.

DBMS package:

1. Before you enable trace collection at the module or action level, you must first use a subprocedure in the DBMS_APPLICATION_INFO package to set the module name.

2. Then, enable or disable trace collection based on the specified module or action.

DBMS_APPLICATION_INFO.SET_MODULE (module_name IN VARCHAR2, action_name IN VARCHAR2);

DBMS_APPLICATION_INFO.SET_ACTION (action_name IN VARCHAR2);

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);

Perform the following steps on OBClient:

# Set the module name.
obclient [SYS]> CALL DBMS_APPLICATION_INFO.SET_MODULE('backup','insert');
Query OK, 0 rows affected
# Enable trace collection, set the sampling rate to 50%, and record the span and tag information of only slow queries.
obclient [SYS]> CALL DBMS_MONITOR.OB_MOD_ACT_TRACE_ENABLE('backup','insert',1,0.5,'ONLY_SLOW_QUERY');
Query OK, 0 rows affected
# Disable trace collection.
obclient [SYS]> CALL DBMS_MONITOR.OB_MOD_ACT_TRACE_DISABLE('backup','insert');
Query OK, 0 rows affected

Enable/Disable trace collection at the tenant level

You can call the OB_TENANT_TRACE_ENABLE procedure in the DBMS_MONITOR package to enable trace collection for the current tenant. Syntax:

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

Examples

Record information such as time consumption in the current tenant. Record and print the span and tag information of all queries in logs.

DBMS package:

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

Perform the following steps on OBClient:

# Enable trace collection and record information such as time consumption in the current tenant. Record and print the span and tag information of all queries in logs.
obclient [SYS]> CALL DBMS_MONITOR.OB_TENANT_TRACE_ENABLE(1, 1, 'ALL');
Query OK, 0 rows affected
# Disable trace collection.
obclient [SYS]> CALL DBMS_MONITOR.OB_TENANT_TRACE_DISABLE();
Query OK, 0 rows affected