Show end-to-end tracing information

You can run the SHOW TRACE command to quickly locate an associated business system where errors occur. You can set the trace ID of an SQL request in a business database connection through a Java Database Connectivity (JDBC) API or SQL API. The trace ID is then recorded in the end-to-end tracing system of OceanBase Database for system performance analysis and troubleshooting. This command allows you to view the information about a trace based on its trace ID to better understand the system running status.

Check abnormal SQL requests in OCP

You can quickly find abnormal SQL requests in the OceanBase Cloud Platform (OCP) console, which facilitates system 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

In an interaction scenario of OceanBase Database, you can execute the SHOW TRACE statement to quickly locate faults.

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   |
   +-------------------------------------------+----------------------------+------------+