Show end-to-end tracing information

The end-to-end tracing 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 tracing system of OceanBase Database, facilitating subsequent system performance analysis and troubleshooting. By using the Show Trace command, users can query detailed information related to the trace ID for a clearer understanding of the system's operational status.

View tracing information through OceanBase Cloud Platform (OCP)

You can quickly find specific requests through OCP to perform system performance analysis and troubleshooting.

Locating and analysis

OCP supports multiple search dimensions, such as duration, trace ID, or SQL ID, to help users quickly locate issues. In addition, OCP provides an intuitive way to display end-to-end tracing information, allowing users to view the execution details of requests at each stage from the client to the database.

Procedure

In OCP, you can query link information by following these steps. For more information, see Trace query.

1. Log in to OCP.
2. In the left navigation pane, choose System Management > Log Service.
3. Click the Link query tab to enter the Link query interface.
4. Set query conditions in the Link query interface to help locate the target trace.
5. Click Query. Hover the cursor over the timeline of a span node to view the span details in the popup.

View tracing information on the command line

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.

Locating and analysis

After you manually execute a statement on the command line, if you need to perform performance analysis or optimization on its execution process, you can use the Show Trace feature to view the execution call chain of the statement and the duration of each stage in the chain. This allows you to quickly find performance bottlenecks for analysis and optimization.

Procedure

The following example shows the execution of two distributed parallel tasks (px_task). To view related log information, see Query the log of a previous SQL request by using the trace feature.

1. Enable SQL trace.

   obclient [test]>SET ob_enable_show_trace = 1;
   

2. Manually 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. Use the SHOW TRACE statement to view the execution call chain of the statement and the duration of each stage in the chain.

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