DISPLAY_PROFILE is used to display the performance analysis data of the SQL execution plan, and you can view the diagnostic information of the operators for the specified trace_id.
Syntax
DBMS_XPROFILE.DISPLAY_PROFILE(
trace_id varchar2 default null,
tenant_id integer default 0,
format varchar2 default 'AGGREGATED',
level integer default 1,
svr_ip varchar2 default null,
svr_port integer default 0,
op_id integer default null
)
Parameters
| Parameter | Description |
|---|---|
| trace_id | Optional. Specifies the trace id. Displays the profile under the specified trace_id. If this parameter is omitted, set to null, or set to an empty string, the last trace id of the current session is used by default. |
| tenant_id | Optional. Specifies the tenant ID. If this parameter is omitted or set to 0, the current tenant ID is used by default. For user tenants, if a tenant ID other than the system tenant is specified, no result will be returned. |
| format | Optional. Specifies the display format. Valid values:
|
| level | Optional. Specifies the output level. Each metric has its own level, and metrics with a level higher than the specified level will not be displayed. Valid values:
|
| svr_ip | Optional. Filters to display only the profile for the specified IP address. The default value is null, which means all profiles are displayed. |
| svr_port | Optional. Filters to display only the profile for the specified port. The default value is 0, which means all profiles are displayed. |
| op_id | Optional. Filters to display only the profile for the operator with the specified ID in the plan. The default value is null, which means all profiles are displayed. |
Examples
Query the profile of another tenant in the sys tenant.
obclient> SELECT DBMS_XPROFILE.DISPLAY_PROFILE('Y7A8B64586C10-00063BAC30589818-0-0', tenant_id);Notice
Only the sys tenant can query the profile of other tenants, and you must specify
tenant_id.Query your own profile in a user tenant (MySQL-compatible mode).
obclient> SELECT DBMS_XPROFILE.DISPLAY_PROFILE('Y7A8B64586C10-00063BAC30589818-0-0');