obdiag gather plan_monitor|V3.2.0|OceanBase Diagnostic Tool |docs|OceanBase Documentations

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. Note The 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`. Note For 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 session
```
SELECT 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.