This article is applicable to the scenario of independent deployment of obdiag. Use the obdiag gather plan_monitor command to collect the execution details of the SQL with the specified trace_id of the OceanBase cluster to which it belongs in one click, making it easier to analyze the reasons for slow SQL execution at the operator level.
If it is a cluster deployed by obd, you can directly collect information on the selected cluster through the obd side diagnostic command group. For detailed commands, see Diagnostic commands.
Instructions for use
Description
This function only supports OceanBase database V3.0 and above.
obdiag gather plan_monitor [options]
The options are explained below:
Option name |
Is it required |
Data type |
Default value |
Description |
|---|---|---|---|---|
| --trace_id | Yes | string | Default is empty | OceanBase database V4.0.0 and below can view trace_id from gv$sql_audit, and OceanBase database V4.0.0 and above can view trace_id from gv$ob_sql_audit. |
| --store_dir | No | string | Defaults to the current path where the command is executed | The local path where the results are stored. |
| --env | No | string | Default is empty | The connection information of the business tenant where the SQL involved in the trace_id to be analyzed is located, mainly used to obtain the explain SQL report. Supports specifying the key=value format multiple times, for example: --env host=x.x.x.x --env port=2881 --env user=root@test --env password=******** --env database=test.
NoteThe configured connection information does not currently support the |
| -c | No | string | ~/.obdiag/config.yml |
Configuration file path. |
| --inner_config | No | string | Default is empty | obdiag's own configuration. |
| --config | No | string | Default is empty | Configuration of the cluster to be diagnosed by obdiag, fixed style: --config key1=value1 --config key2=value2.
ExplanationThe parameters that support configuration through this option can be found in obdiag configuration. |
| --config_password | No | string | Default is empty | obdiag When using an encrypted configuration file, you need to pass in the corresponding password through this option.
ExplanationFor details, see Configuration file encryption. |
Usage example
Description
The method to obtain trace_id before use is as follows.
Obtain it from gv$ob_sql_audit (gv$sql_audit when the OceanBase database version is less than V4.0), or obtain it through SELECT last_trace_id();, choose one of the following two methods.
-- Method 1: obtain from sql_audit
select query_sql,trace_id from oceanbase.GV$OB_SQL_AUDIT where query_sql like 'xxx%' order by REQUEST_TIME desc limit 5;
-- Method 2: run SELECT last_trace_id(); in the current session
SELECT last_trace_id();
Method 1: Use without configuration file (out of the box)
obdiag gather plan_monitor --trace_id YB420BA2D99B-0005EBBFC45D5A00-0-0 \
--env host=xx.xx.xx.xx --env port=2881 --env user=root@test --env password=*** --env database=test \
--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 with configuration file
Description
You need to ensure that the cluster sys connection information that needs to be collected has been configured in the obdiag configuration file config.yml. For related detailed configuration introduction, see obdiag configuration.
obdiag gather plan_monitor --trace_id YB420BA2D99B-0005EBBFC45D5A00-0-0 \
--env host=xx.xx.xx.xx --env port=2881 --env user=root@test --env password=*** --env database=test
Interpretation of results
How to interpret the SQL Monitor Report collected by obdiag
Description
Since obdiag V4.1.0, when the OceanBase database is V4.0.0 or later, the report will include a statistical information histogram (Histogram), including table-level and partition-level histogram information, to facilitate analysis of execution plans and statistical information. Histogram information is displayed in the Statistics Histogram (Histogram) section of the report.
Since obdiag V4.2.0, the parsing table has been deduplicated to avoid repeated processing when the same table is referenced multiple times, improving the efficiency of schema reporting and statistical information collection; adding verification when the specified database does not exist, avoiding invalid connections, and making error prompts clearer.