DBMS_XPLAN information collection|V4.2.0|OceanBase Diagnostic Tool |docs|OceanBase Documentations

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: `opt_trace`: Use the DBMS_XPLAN.ENABLE_OPT_TRACE function to collect SQL performance diagnostic information. `display_cursor`: Use the DBMS_XPLAN.DISPLAY_CURSOR function to collect SQL performance diagnostic information. `all`: Use the DBMS_XPLAN.ENABLE_OPT_TRACE and DBMS_XPLAN.DISPLAY_CURSOR functions to collect performance diagnostic information for SQL.
--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`. Explanation The 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. Explanation For 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