DISPLAY_PROFILE is used to display the performance analysis data of an SQL execution plan, and you can view the diagnostic information of operators for a 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. If you omit this parameter, set it to null, or set it to an empty string, the profile of the last trace id in the current session is displayed. |
| tenant_id | Optional. Specifies the tenant ID. If you omit this parameter or set it to 0, the current tenant ID is used. For a user tenant, if you specify a tenant ID other than the system tenant, no result is returned. |
| format | Optional. Specifies the display format. Valid values:
|
| level | Optional. Specifies the output level. Each metric has its own level. Metrics with a level higher than the specified level are not displayed. Valid values:
|
| svr_ip | Optional. Specifies the IP address of the server. The default value is null, which indicates that all profiles are displayed. |
| svr_port | Optional. Specifies the port of the server. The default value is 0, which indicates that all profiles are displayed. |
| op_id | Optional. Specifies the operator ID. The default value is null, which indicates that all profiles are displayed. |
Examples
Query the profile of another tenant in the sys tenant.
obclient> SELECT DBMS_XPROFILE.DISPLAY_PROFILE('Y7A8B64586C10-00063BAC2E689814-0-0', tenant_id);Notice
Only the sys tenant can query the profile of another tenant. You must specify
tenant_id.Query the profile of the current tenant in the Oracle-compatible mode.
obclient> SELECT * FROM TABLE(DBMS_XPROFILE.DISPLAY_PROFILE('Y7A8B64586C10-00063BAC2E689814-0-0'));