The DISPLAY_SQL_PLAN_BASELINE function is used to view the baseline plans in the SQL Plan Management (SPM) system.
Syntax
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
sql_handle VARCHAR2 DEFAULT NULL,
plan_name VARCHAR2 DEFAULT NULL,
format VARCHAR2 DEFAULT 'TYPICAL',
svr_ip VARCHAR2 DEFAULT null, -- default value: server connected by client
svr_port INTEGER DEFAULT 0, -- default value: server connected by client
tenant_id INTEGER DEFAULT 0 -- default value: current tenant)
RETURN DBMS_XPLAN_TYPE_TABLE;
Parameters
Parameter |
Description |
|---|---|
| sql_handle | The SQL handle of the baseline plan. You can obtain the SQL handle from the SPM views. |
| plan_name | The name of the baseline plan. |
| format | The plan format. Valid values:
|
| svr_ip | The IP address of the node where the plan is located. The default value is the IP address of the node to which the current session is connected. |
| svr_port | The port number of the node where the plan is located. The default value is the port number of the node to which the current session is connected. |
| tenant_id | The tenant ID to which the plan belongs. The default value is the tenant ID to which the current session is connected. |
Examples
/* Enable SPM for the current session. */
SET SESSION optimizer_use_sql_plan_baselines = 'ON';
SET SESSION optimizer_capture_sql_plan_baselines = 'ON';
/* Create a baseline plan. */
CREATE TABLE t1(c1 INT);
SELECT * FROM t1 WHERE c1 > 1;
/* Query the ID of the baseline plan. */
obclient> SELECT sql_handle,plan_name FROM oceanbase.DBA_SQL_PLAN_BASELINES;
+----------------------------------+----------------------+
| SQL_HANDLE | PLAN_NAME |
+----------------------------------+----------------------+
| 49D6048C041AA8C7E70E2D114250AB99 | 11986391583431335905 |
+----------------------------------+----------------------+
/* View the baseline plan */
obclient> SELECT DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('49D6048C041AA8C7E70E2D114250AB99','11986391583431335905','all');
+------------------------------------------------------------------------------------------------------------------+
| COLUMN_VALUE |
+------------------------------------------------------------------------------------------------------------------+
| ================================================================================================ |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| |
| ------------------------------------------------------------------------------------------------ |
| |0 |TABLE FULL SCAN|T1 |1 |2 |0 |0 |0 |0 | |
| ================================================================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1]), filter([T1.C1 > :0]), rowset=256 |
| access([T1.C1]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "SYS"."T1"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T1: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:0 |
| avaiable_index_name:[T1] |
| stats version:0 |
| Plan Type: |
| LOCAL |
| Parameters: |
| :0 => 1 |
| SQL text: |
| ------------------------------------- |
| select * from t1 where c1 > 1 |
| Outline data: |
| ------------------------------------- |
| /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SYS"."T1"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.0.0.0')END_OUTLINE_DATA*/ |
| SQL handle: |
| ------------------------------------- |
| 49D6048C041AA8C7E70E2D114250AB99 |
| Plan name: |
| ------------------------------------- |
| 11986391583431335905 |
| Baseline info: |
| ------------------------------------- |
| Origin: AUTO-CAPTURE Accepted: YES Fixed: NO Enabled: YES |
| Plan rows: From plan cahe |
+------------------------------------------------------------------------------------------------------------------+