Show end-to-end tracing information

The end-to-end trace display feature helps users easily manage and analyze business systems. This feature allows users to set a trace ID associated with a call request when connecting to the business database through JDBC or SQL interfaces. This trace ID is then recorded in the end-to-end trace system of OceanBase Database, facilitating subsequent systemic performance analysis and troubleshooting for the user. By using the Show Trace command, users can retrieve detailed information related to the trace ID, providing a clearer understanding of the system’s operational status.

Check abnormal SQL requests in OCP

You can quickly find specific requests through OceanBase Cloud Platform (OCP), enabling systemic performance analysis and troubleshooting.

Fault locating and analysis

OCP allows you to search by multiple dimensions, such as duration, trace ID, or SQL ID, so that you can quickly locate the abnormal phase. In addition, OCP shows the end-to-end tracing information in a visualized way. You can view the execution information about an SQL request in all components in the entire process, from the client to the database, to better understand the system running status.

Procedure

In OCP, you can perform the following steps to show a trace. For more information, see Trace query.

1. Log on to the OceanBase Cloud Platform (OCP) console.
2. In the left-side navigation pane, choose System Management > Log Service.
3. Click the Link query tab.
4. On the Link query tab, specify search conditions as needed to retrieve the desired traces.
5. Click Query. Move the pointer over the timeline of a span to view details of the span in the tooltip.

View a trace on CLI

You can also use the Show Trace feature in the command-line interface of OceanBase Database. This feature allows users to quickly obtain detailed call chain and timing information after executing SQL statements. In this way, you can easily identify performance bottlenecks and conduct further analysis and optimization.

Fault locating and analysis

After you manually execute a statement on a CLI, if you want to analyze or optimize the execution process, you can execute the SHOW TRACE statement to show the execution trace of the statement and the duration of each phase in the trace. This allows you to quickly locate performance bottlenecks for analysis and optimization.

Procedure

The following example shows the execution process of two distributed parallel tasks (px_task). If you want to view trace logs, see Query the log of a previous SQL request by using the SQL Trace feature.

1. Enable SQL Trace.

   obclient [test]>SET ob_enable_show_trace = 1;
   

2. Execute the SQL statement to be queried.

   obclient [test]>select/*+parallel(2)*/ count(*) from t1;
   +----------+
   | count(*) |
   +----------+
   |        5 |
   +----------+
   1 row in set (0.005 sec)
   

3. Execute the SHOW TRACE statement to view the execution trace of the statement executed in the previous step and the duration of each phase in the trace.

   obclient [test]>show trace;
   +-------------------------------------------+----------------------------+------------+
   | Operation                                 | StartTime                  | ElapseTime |
   +-------------------------------------------+----------------------------+------------+
   | obclient                                  | 2023-06-25 17:51:30.143537 | 4.667 ms   |
   | └─ ob_proxy                               | 2023-06-25 17:51:30.143716 | 4.301 ms   |
   |    └─ com_query_process                   | 2023-06-25 17:51:30.145119 | 2.527 ms   |
   |       └─ mpquery_single_stmt              | 2023-06-25 17:51:30.145123 | 2.513 ms   |
   |          ├─ sql_compile                   | 2023-06-25 17:51:30.145133 | 0.107 ms   |
   |          │  └─ pc_get_plan                | 2023-06-25 17:51:30.145135 | 0.061 ms   |
   |          └─ sql_execute                   | 2023-06-25 17:51:30.145252 | 2.350 ms   |
   |             ├─ open                       | 2023-06-25 17:51:30.145252 | 0.073 ms   |
   |             ├─ response_result            | 2023-06-25 17:51:30.145339 | 2.186 ms   |
   |             │  ├─ px_schedule             | 2023-06-25 17:51:30.145342 | 1.245 ms   |
   |             │  │  ├─ px_task              | 2023-06-25 17:51:30.146391 | 1.113 ms   |
   |             │  │  │  ├─ get_das_id        | 2023-06-25 17:51:30.146979 | 0.001 ms   |
   |             │  │  │  ├─ do_local_das_task | 2023-06-25 17:51:30.147012 | 0.050 ms   |
   |             │  │  │  └─ close_das_task    | 2023-06-25 17:51:30.147237 | 0.014 ms   |
   |             │  │  └─ px_task              | 2023-06-25 17:51:30.146399 | 0.868 ms   |
   |             │  │     ├─ get_das_id        | 2023-06-25 17:51:30.147002 | 0.001 ms   |
   |             │  │     ├─ do_local_das_task | 2023-06-25 17:51:30.147036 | 0.041 ms   |
   |             │  │     └─ close_das_task    | 2023-06-25 17:51:30.147183 | 0.011 ms   |
   |             │  └─ px_schedule             | 2023-06-25 17:51:30.147437 | 0.001 ms   |
   |             └─ close                      | 2023-06-25 17:51:30.147536 | 0.049 ms   |
   |                └─ end_transaction         | 2023-06-25 17:51:30.147571 | 0.002 ms   |
   +-------------------------------------------+----------------------------+------------+