The Show Trace feature allows you to interactively view the call chain and the time spent in each stage during the execution of the last SQL request. This feature is useful for performance analysis and optimization. You can easily identify performance bottlenecks using the Show Trace feature.
Version requirements
OceanBase Database: V4.2.0 and later
ODP: V4.2.0 and later
OCP: V4.0.3 and later
Considerations
OceanBase Database supports the Show Trace feature starting from V4.2.0, based on the OceanBase 2.0 protocol. Please note the following points about this feature.
When using compatible component versions, you can enable the Show Trace feature at the session level by using the feature switch. Once enabled, ODP and OceanBase Database will record detailed interaction information for every subsequent SQL executed. After the SQL execution is complete, you can simply execute the
show trace;command to view the diagnostic information generated by the last SQL.```shell obclient> alter proxyconfig set server_protocol = 'Auto'; obclient> alter proxyconfig set enable_single_leader_node_routing = False; ```Method 2: Directly set server_protocol to
OceanBase 2.0.obclient> alter proxyconfig set server_protocol = 'OceanBase 2.0';
Enable trace statistics in ODP.
Set enable_full_link_trace to
Trueto enable the full-link tracing feature for query execution tracking.obclient> alter proxyconfig set enable_full_link_trace = True;
Step 2: Enable the Show Trace feature and execute the target SQL
Log in to the OceanBase cluster as a user tenant and perform the following steps.
Enable Show Trace at the session level.
Set
ob_enable_show_tracetoONto enable the Show Trace feature.obclient> set ob_enable_show_trace= 'ON';Execute the target SQL. For example, execute the
show tables;command.obclient> show tables;The output is as follows:
+----------------------------+ | Tables_in_test | +----------------------------+ | t | | t1 | | t2 | +----------------------------+ 3 rows in set
Step 3: Use Show Trace
You can execute the show trace; command to obtain the result set echo or the show trace format='json'; command to obtain the result set in JSON format.
Execute the
show trace;command to obtain the result set echo.obclient> show trace;The output is as follows:
+-----------------------------------------------+----------------------------+------------+ | Operation | StartTime | ElapseTime | +-----------------------------------------------+----------------------------+------------+ | ob_proxy | 2026-01-08 15:17:24.555215 | 12.414 ms | | ├── ob_proxy_partition_location_lookup | 2026-01-08 15:17:24.555411 | 0.010 ms | | ├── ob_proxy_server_process_req | 2026-01-08 15:17:24.555813 | 10.150 ms | | └── com_query_process | 2026-01-08 15:17:24.555989 | 10.265 ms | | └── mpquery_single_stmt | 2026-01-08 15:17:24.556058 | 10.172 ms | | ├── sql_compile | 2026-01-08 15:17:24.556137 | 7.300 ms | | │ ├── pc_get_plan | 2026-01-08 15:17:24.556230 | 0.016 ms | | │ └── hard_parse | 2026-01-08 15:17:24.556370 | 7.014 ms | | │ ├── parse | 2026-01-08 15:17:24.556396 | 0.044 ms | | │ ├── resolve | 2026-01-08 15:17:24.556526 | 1.866 ms | | │ ├── rewrite | 2026-01-08 15:17:24.558557 | 2.020 ms | | │ ├── optimize | 2026-01-08 15:17:24.560613 | 1.928 ms | | │ └── code_generate | 2026-01-08 15:17:24.562580 | 0.428 ms | | └── sql_execute | 2026-01-08 15:17:24.563462 | 2.457 ms | | ├── open | 2026-01-08 15:17:24.563469 | 0.089 ms | | ├── response_result | 2026-01-08 15:17:24.563614 | 1.620 ms | | │ └── do_local_das_task | 2026-01-08 15:17:24.563716 | 1.025 ms | | └── close | 2026-01-08 15:17:24.565277 | 0.342 ms | | ├── close_das_task | 2026-01-08 15:17:24.565294 | 0.028 ms | | └── end_transaction | 2026-01-08 15:17:24.565551 | 0.036 ms | +-----------------------------------------------+----------------------------+------------+ 20 rows in setFrom the above echo, you can first scope the problem and analyze it by examining the time spent at the following key points, which can help you quickly identify the overall database execution time and provide further analysis and optimization.
ob_proxy is the starting point of ODP, recording the time from when ODP receives the SQL request to when it provides a complete response to the client.
ob_proxy_server_process_req indicates the time from when the SQL request is sent to when the first response is received from an OBServer node, equal to the OBServer node processing time plus the network return time.
com_query_process is the total point for OceanBase Database, recording the time from when an OBServer node receives the SQL request to when it forwards the response.
Execute the
show trace format='json';command to obtain the result set in JSON format.Executing the
show trace format = 'json';command will return the result in JSON format, which can be formatted for easier collection and analysis, as shown in the following figure.
You can repeatedly execute the show trace; or show trace format='json'; command to observe the real-time echo.
Step 4: Disable the Show Trace feature
After analysis is complete, execute the following command to disable Show Trace at the session level to avoid performance impact.
obclient> set ob_enable_show_trace= 'OFF';