This article applies to the scenario of independently deploying obdiag. Use the obdiag gather dbms_xplan command to collect SQL performance diagnostic information using the DBMS_XPLAN system package with one click.
If it is a cluster deployed by obd, you can directly collect information on the selected cluster through the obd side diagnostic command group.
Instructions for use
obdiag gather dbms_xplan [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. |
| --scope | No | string | all | Select the DBMS_XPLAN system package used to collect SQL performance diagnostic information. The configurable values are as follows:
|
| --user | Yes | string | Default is empty | The user name of the tenant where the SQL to be collected is located. |
| --password | Yes | string | Default is empty | The user password of the tenant where the SQL to be collected is located. |
| --env | No | string | Default is empty | The connection information of the business tenant, supports specifying the key=value format multiple times, for example: --env host=x.x.x.x --env port=2881 --env user=root@test. |
| --store_dir | No | string | Defaults to the current path where the command is executed | The local path where the results are stored. |
| -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
Method 1: Use without configuration file (out of the box)
obdiag gather dbms_xplan --user=<MYUSER> --password=<MYPASSWORD> --trace_id=<TRACE_ID> \
--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:
$ obdiag gather dbms_xplan --user xxxx --password *** --trace_id YF2A0BA2DA7E-0006350FB4EBBD8A-0-0 \
--config db_host=xx.xx.xx.xx \
--config db_port=xxxx \
--config tenant_sys.user=root@sys \
--config tenant_sys.password=***
gather_dbms_xplan start ...
execute dbms_xplan.enable_opt_trace start ...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
call dbms_xplan.enable_opt_trace();
call dbms_xplan.set_opt_trace_parameter(identifier=>'obdiag_MkaDL7', `level`=>3);
call dbms_xplan.disable_opt_trace();
execute dbms_xplan.enable_opt_trace end
Gather dbms_xplan.enable_opt_trace:
+----------------+-----------+---------+--------+----------------------------------------------------------------------------------------------+
| Node | Status | Size | Time | PackPath |
+================+===========+=========+========+==============================================================================================+
| xx.xxx.xxx.xx | Completed | 35.800K | 0 s | ./obdiag_gather_pack_20250514200335/xx_xxx_xxx_xxx_optimizer_trace_G6WfT1_obdiag_MkaDL7.trac |
+----------------+-----------+---------+--------+----------------------------------------------------------------------------------------------+
Gather dbms_xplan.display_cursor:
+-----------+--------------------------------------------------------------------------+--------+
| Status | Result Details | Time |
+===========+==========================================================================+========+
| Completed | ./obdiag_gather_pack_20250514200335/obdiag_dbms_xplan_display_cursor.txt | 0.17 s |
+-----------+--------------------------------------------------------------------------+--------+
Trace ID: 7685e982-30bb-11f0-bf59-00163e01c7ce
If you want to view detailed obdiag logs, please run: obdiag display-trace f5b1f66e-5552-11ef-8a9b-00163e01c7ce
Method 2: Use with configuration file
You need to ensure that the login information of the node to be collected has been configured in the obdiag configuration file config.yml. For related detailed configuration introduction, see obdiag configuration.
obdiag gather dbms_xplan --user=<MYUSER> --password=<MYPASSWORD> --trace_id=<TRACE_ID>
Interpretation of results
After the command is executed, a result folder will be generated in the specified directory. The folder shape is: ./obdiag_gather_pack_xxxxx.
$tree
.
├── xx_xx_xx_xx_optimizer_trace_G6WfT1_obdiag_MkaDL7.trac # Files from dbms_xplan.enable_opt_trace
└── obdiag_dbms_xplan_display_cursor.txt # Output from dbms_xplan.display_cursor