The DISPLAY_SQL_PLAN_BASELINE function is used to view the baseline plans in the SQL Plan Management (SPM) feature.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
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, which can be obtained from SPM-related 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 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 session is connected. |
| tenant_id | The tenant ID to which the plan belongs. The default value is the tenant ID to which the 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 * FROM TABLE(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 |
+------------------------------------------------------------------------------------------------------------------+