首先运行两个结构相同的语句，下面的实验通过SQL计划基线，将一个语句的执行计划通过另一个语句的执行计划来固定
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from DBA_sql_plan_baselines;
未选定行
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:27:31
h_stat where id=711
select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41
dh_stat where id=711

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4vaj9fgjysy9c, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 124 (100)| |
|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=711)
已选择19行。

SQL> select * from table(dbms_xplan.display_cursor('fm35jcmypb3qu','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fm35jcmypb3qu, child number 0
-------------------------------------
select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=711)
已选择20行。


SQL> DECLARE
2 k1 pls_integer;
3 begin
4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
5 sql_id=>'4vaj9fgjysy9c',
6 plan_hash_value=>1845196118
7 );
8 end;
9 /
PL/SQL 过程已成功完成。

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ACC
------------------------------ ------------------------------ ------------------------------------------------------- ---
SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
h_sta

刚生产sql plan baseline的时候，第一次查询，无法找到执行计划，直到第二次执行的时候，才能看到，如下
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID: 4vaj9fgjysy9c cannot be found
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41
dh_stat where id=711


SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54
h_stat where id=711
select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41
dh_stat where id=711


SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4vaj9fgjysy9c, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711

Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 124 (100)| |
|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=711)
Note
-----
- SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement
已选择23行。

将符合我们预期的执行计划的加载到第一次生成的sql baseline中！
SQL> DECLARE
2 k1 pls_integer;
3 begin
4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
5 sql_id=>'fm35jcmypb3qu',
6 plan_hash_value=>2780970545,sql_handle=>'SYS_SQL_11bcd50cd51504e9'
7 );
8 end;
9 /

PL/SQL 过程已成功完成。

可以看到，SYS_SQL_11bcd50cd51504e9下目前有两个plan_name
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ACC
------------------------------ ------------------------------ ------------------------------------------------------- ---
SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
h_sta

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
h_sta
删除第一个plan_name，即将我们不需要的执行计划版本去除掉！
SQL> DECLARE
2 k1 pls_integer;
3 begin
4 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');
5 end;
6 /

PL/SQL 过程已成功完成。



通过下面的一部分测试，我们可以看到，新的SQL计划基线已经正常生效，及时语句中包含full提示，执行计划也走索引定位数据
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4vaj9fgjysy9c, child number 1
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=711)
Note
-----
- SQL plan baseline SQL_PLAN_13g6p1maja1790cce5f0e used for this statement
已选择24行。


可以通过dba_sql_plan_baselines来显示可用的SQL计划基线的一般信息，也可以通过如下这种方式显示执行SQL计划基线的详细信息！
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL handle: SYS_SQL_11bcd50cd51504e9
SQL text: select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_13g6p1maja1790cce5f0e Plan id: 214851342
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=711)
已选择26行。

查看SQL计划基线中保存的hint提示集合
SQL> conn /as sysdba
已连接。
SQL> select
2 extractvalue(value(d), '/hint') as outline_hints
3 from
4 xmltable('/outline_data/hint'
5 passing (
6 select
7 xmltype(comp_data) as xmlval
8 from
9 sqlobj$data sod, sqlobj$ so
10 where so.signature = sod.signature
11 and so.plan_id = sod.plan_id
12 and comp_data is not null
13 and name like '&baseline_plan_name'
14 )
15 ) d;
输入 baseline_plan_name 的值: SQL_PLAN_13g6p1maja1790cce5f0e
原值 13: and name like '&baseline_plan_name'
新值 13: and name like 'SQL_PLAN_13g6p1maja1790cce5f0e'

OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DH_STAT"@"SEL$1" ("DH_STAT"."ID"))

已选择6行。

首先运行两个结构相同的语句，下面的实验通过SQL计划基线，将一个语句的执行计划通过另一个语句的执行计划来固定
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from DBA_sql_plan_baselines;
未选定行
SQL> alter system flush shared_pool;
系统已更改。
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:27:31
h_stat where id=711
select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41
dh_stat where id=711

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4vaj9fgjysy9c, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 124 (100)| |
|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=711)
已选择19行。

SQL> select * from table(dbms_xplan.display_cursor('fm35jcmypb3qu','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fm35jcmypb3qu, child number 0
-------------------------------------
select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=711)
已选择20行。


SQL> DECLARE
2 k1 pls_integer;
3 begin
4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
5 sql_id=>'4vaj9fgjysy9c',
6 plan_hash_value=>1845196118
7 );
8 end;
9 /
PL/SQL 过程已成功完成。

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ACC
------------------------------ ------------------------------ ------------------------------------------------------- ---
SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
h_sta

刚生产sql plan baseline的时候，第一次查询，无法找到执行计划，直到第二次执行的时候，才能看到，如下
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID: 4vaj9fgjysy9c cannot be found
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41
dh_stat where id=711


SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54
h_stat where id=711
select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41
dh_stat where id=711


SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4vaj9fgjysy9c, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711

Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 124 (100)| |
|* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=711)
Note
-----
- SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement
已选择23行。

将符合我们预期的执行计划的加载到第一次生成的sql baseline中！
SQL> DECLARE
2 k1 pls_integer;
3 begin
4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
5 sql_id=>'fm35jcmypb3qu',
6 plan_hash_value=>2780970545,sql_handle=>'SYS_SQL_11bcd50cd51504e9'
7 );
8 end;
9 /

PL/SQL 过程已成功完成。

可以看到，SYS_SQL_11bcd50cd51504e9下目前有两个plan_name
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME SQL_TEXT ACC
------------------------------ ------------------------------ ------------------------------------------------------- ---
SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
h_sta

SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
h_sta
删除第一个plan_name，即将我们不需要的执行计划版本去除掉！
SQL> DECLARE
2 k1 pls_integer;
3 begin
4 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');
5 end;
6 /

PL/SQL 过程已成功完成。



通过下面的一部分测试，我们可以看到，新的SQL计划基线已经正常生效，及时语句中包含full提示，执行计划也走索引定位数据
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID NAME TYPE
---------- ------------------------------ ---------------
711 I_STREAMS_PROCESS_PARAMS1 INDEX

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4vaj9fgjysy9c, child number 1
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=711)
Note
-----
- SQL plan baseline SQL_PLAN_13g6p1maja1790cce5f0e used for this statement
已选择24行。


可以通过dba_sql_plan_baselines来显示可用的SQL计划基线的一般信息，也可以通过如下这种方式显示执行SQL计划基线的详细信息！
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL handle: SYS_SQL_11bcd50cd51504e9
SQL text: select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_13g6p1maja1790cce5f0e Plan id: 214851342
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=711)
已选择26行。

查看SQL计划基线中保存的hint提示集合
SQL> conn /as sysdba
已连接。
SQL> select
2 extractvalue(value(d), '/hint') as outline_hints
3 from
4 xmltable('/outline_data/hint'
5 passing (
6 select
7 xmltype(comp_data) as xmlval
8 from
9 sqlobj$data sod, sqlobj$ so
10 where so.signature = sod.signature
11 and so.plan_id = sod.plan_id
12 and comp_data is not null
13 and name like '&baseline_plan_name'
14 )
15 ) d;
输入 baseline_plan_name 的值: SQL_PLAN_13g6p1maja1790cce5f0e
原值 13: and name like '&baseline_plan_name'
新值 13: and name like 'SQL_PLAN_13g6p1maja1790cce5f0e'

OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DH_STAT"@"SEL$1" ("DH_STAT"."ID"))

已选择6行。