If OceanBase Diagnostic Tool (obdiag) is independently deployed, you can run the obdiag gather plan_monitor command to collect the execution details of an SQL statement with the specified trace ID in the specified OceanBase cluster. This way, you can analyze the operators if the SQL statement execution is slow.
If your OceanBase cluster is deployed by using OceanBase Deployer (obd), you can run obdiag commands on obd to collect diagnostic information of the cluster. For more information about the commands, see obdiag commands.
Syntax
Note
This command is supported only in OceanBase Database V3.0 and later.
obdiag gather plan_monitor [options]
The following table describes the options.
| Option | Required | Data type | Default value | Description |
|---|---|---|---|---|
| --trace_id | Yes | string | Empty | In OceanBase Database of a version earlier than V4.0.0, you can query the gv$sql_audit view for trace IDs. In OceanBase Database V4.0.0 and later, you can query the gv$ob_sql_audit view for trace IDs. |
| --store_dir | No | string | The current path where the command is executed | The local path where the results are stored. |
| --env | No | string | Empty | The connection string of the business tenant to which the SQL statement corresponding to the specified trace ID belongs. The connection string is mainly used to obtain the return result of the EXPLAIN statement.
NoteThe connection string cannot contain the following characters: |
| -c | No | string | ~/.obdiag/config.yml |
The path of the configuration file. |
| --inner_config | No | string | Empty | The configurations of obdiag. |
| --config | No | string | Empty | The configurations of the cluster diagnosed by obdiag, in the format of --config key1=value1 --config key2=value2.
NoteFor information about the parameters supported by this option, see Configure obdiag. |
Examples
Before you use obdiag, obtain the trace_id by using either of the following methods.
Query the GV$OB_SQL_AUDIT or GV$SQL_AUDIT (in OceanBase Database of a version earlier than V4.0) view
select query_sql,trace_id from oceanbase.GV$OB_SQL_AUDIT where query_sql like 'xxx%' order by REQUEST_TIME desc limit 5;Execute the
SELECT last_trace_id();command in the current sessionSELECT last_trace_id();
Method 1: Use the command out-of-the-box without a configuration file
obdiag gather plan_monitor --trace_id YB420BA2D99B-0005EBBFC45D5A00-0-0 --env "{db_connect='-hxx -Pxx -uxx -pxx -Dxx'}" \
--config db_host=xx.xx.xx.xx \
--config db_port=xxxx \
--config tenant_sys.user=root@sys \
--config tenant_sys.password=***
The output is as follows:
Gather Summary:
+-----------+--------+-----------------------------------------------------------------------------+
| Status | Time | PackPath |
+===========+========+=============================================================================+
| Completed | 6 s | obdiag_gather_pack_20230118002457 |
+-----------+--------+-----------------------------------------------------------------------------+
Method 2: Use the command with a configuration file
Note
Before you run this command, make sure that the connection information of the sys tenant in the target cluster has been configured in the config.yml configuration file of obdiag. For more information, see Configure obdiag.
obdiag gather plan_monitor --trace_id YB420BA2D99B-0005EBBFC45D5A00-0-0 --env "{db_connect='-hxx -Pxx -uxx -pxx -Dxx'}"
Results
For more information, see Interpret SQL Monitor Reports Collected by obdiag.