------------恢复内容开始------------
1.查询当前SQL执行计划并固定
（1）查询指定SQL SQL_ID
SQL> select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text like '%fundinfo%';
SQL_ID SQL_TEXT VERSION_COUNT PARSE_CALLS
------------- -------------------------------------------------------------------------------- ------------- -----------
6v57a84f59hqk select count(planid) from fundinfo where planid < 99000014000800382760 1 1
adz60h2w2js6p select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text l 1 1
（2）查询SQL_ID对应执行计划
# 使用下面两种查询一种查询出对应执行计划即可
SQL> select * from table(dbms_xplan.display_cursor('6v57a84f59hqk'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6v57a84f59hqk, child number 0
-------------------------------------
select count(planid) from fundinfo where planid < 99000014000800382760
Plan hash value: 913026973
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| FUNDINFO |
---------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("PLANID")<99000014000800382760)
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- rule based optimizer used (consider using cbo)
23 rows selected
SQL> SELECT hash_value,sql_id,plan_hash_value,operation,options FROM v$sql_plan WHERE sql_id='6v57a84f59hqk';
HASH_VALUE SQL_ID PLAN_HASH_VALUE OPERATION OPTIONS
---------- ------------- --------------- ------------------------------ ------------------------------
475316946 6v57a84f59hqk 913026973 SELECT STATEMENT
475316946 6v57a84f59hqk 913026973 SORT AGGREGATE
475316946 6v57a84f59hqk 913026973 TABLE ACCESS FULL
（3）固定执行计划
SQL> var test number
SQL> begin
2 :test := dbms_spm.load_plans_from_cursor_cache(sql_id=>'6v57a84f59hqk', plan_hash_value=>913026973);
3 end;
4 /
PL/SQL procedure successfully completed
SQL> select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf36b969528 MANUAL-LOAD
2.使用HINT生成指定的执行计划
（1）使用/*+ INDEX(table_name index_name)+*/生成执行计划
HR@honor1 > select /*+ INDEX(FUNDINFO FUNDINFO_PLANID)+*/ count(planid) from fundinfo where planid < 99000014000800382760;
COUNT(PLANID)
----------------------------------------
337473
（2）查询生成的执行计划
SQL> select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text like '%fundinfo%';
SQL_ID SQL_TEXT VERSION_COUNT PARSE_CALLS
------------- -------------------------------------------------------------------------------- ------------- -----------
gw6gd2ggk0h9m select /*+ INDEX(FUNDINFO FUNDINFO_PLANID)+*/ count(planid) from fundinfo where 1 1
adz60h2w2js6p select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text l 1 3
SQL> SELECT hash_value,sql_id,plan_hash_value,operation,options FROM v$sql_plan WHERE sql_id='gw6gd2ggk0h9m';
HASH_VALUE SQL_ID PLAN_HASH_VALUE OPERATION OPTIONS
---------- ------------- --------------- ------------------------------ ------------------------------
3743433011 gw6gd2ggk0h9m 2614089935 SELECT STATEMENT
3743433011 gw6gd2ggk0h9m 2614089935 SORT AGGREGATE
3743433011 gw6gd2ggk0h9m 2614089935 INDEX FULL SCAN
3.使用dbms_spm.load_plans_from_cursor_cache函数固定执行计划
# 说明
begin
:test := dbms_spm.load_plans_from_cursor_cache(
sql_id => '加入HINT后的SQL_ID',
plan_hash_value => 加入HINT后生成的plan_hash_value,
sql_handle => '旧的执行计划固定后dba_sql_plan_baselines中sql_handle');
end;
/
# 执行
SQL> begin
2 :test := dbms_spm.load_plans_from_cursor_cache(
3 sql_id => 'gw6gd2ggk0h9m',
4 plan_hash_value => 2614089935,
5 sql_handle => 'SQL_458606bd5b57f1c3');
6 end;
7 /
PL/SQL procedure successfully completed
# 查询基线
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf3686e388b MANUAL-LOAD
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf36b969528 MANUAL-LOAD
4.删除最开始固定的旧基线，使新的执行计划的新基线生效
SQL> var test number;
SQL> begin
2 :test := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_458606bd5b57f1c3', plan_name=>'SQL_PLAN_4b1h6rpdpgwf36b969528');
3 end;
4 /
PL/SQL procedure successfully completed
# 查询基线
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf3686e388b MANUAL-LOAD
5.测试新基线
HR@honor1 > explain plan for select count(planid) from fundinfo where planid < 99000014000800382760;
Explained.
HR@honor1 > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2614089935
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 27 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
|* 2 | INDEX FULL SCAN| FUNDINFO_PLANID | 18023 | 387K| 27 (4)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("PLANID")<99000014000800382760)
Note
-----
- SQL plan baseline "SQL_PLAN_4b1h6rpdpgwf3686e388b" used for this statement
18 rows selected.
# 可以看到走了索引全扫描，新基线已经生效。
6.删除基线
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf37d78711b AUTO-CAPTURE
var test number;
begin
:test := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_458606bd5b57f1c3', plan_name=>'SQL_PLAN_4b1h6rpdpgwf37d78711b');
end;
/
PL/SQL procedure successfully completed.
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
no rows selected
参考文档：
https://www.cnblogs.com/ddzj01/p/11377049.html
------------恢复内容结束------------
------------恢复内容开始------------
1.查询当前SQL执行计划并固定
（1）查询指定SQL SQL_ID
SQL> select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text like '%fundinfo%';
SQL_ID SQL_TEXT VERSION_COUNT PARSE_CALLS
------------- -------------------------------------------------------------------------------- ------------- -----------
6v57a84f59hqk select count(planid) from fundinfo where planid < 99000014000800382760 1 1
adz60h2w2js6p select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text l 1 1
（2）查询SQL_ID对应执行计划
# 使用下面两种查询一种查询出对应执行计划即可
SQL> select * from table(dbms_xplan.display_cursor('6v57a84f59hqk'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6v57a84f59hqk, child number 0
-------------------------------------
select count(planid) from fundinfo where planid < 99000014000800382760
Plan hash value: 913026973
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| FUNDINFO |
---------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("PLANID")<99000014000800382760)
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- rule based optimizer used (consider using cbo)
23 rows selected
SQL> SELECT hash_value,sql_id,plan_hash_value,operation,options FROM v$sql_plan WHERE sql_id='6v57a84f59hqk';
HASH_VALUE SQL_ID PLAN_HASH_VALUE OPERATION OPTIONS
---------- ------------- --------------- ------------------------------ ------------------------------
475316946 6v57a84f59hqk 913026973 SELECT STATEMENT
475316946 6v57a84f59hqk 913026973 SORT AGGREGATE
475316946 6v57a84f59hqk 913026973 TABLE ACCESS FULL
（3）固定执行计划
SQL> var test number
SQL> begin
2 :test := dbms_spm.load_plans_from_cursor_cache(sql_id=>'6v57a84f59hqk', plan_hash_value=>913026973);
3 end;
4 /
PL/SQL procedure successfully completed
SQL> select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf36b969528 MANUAL-LOAD
2.使用HINT生成指定的执行计划
（1）使用/*+ INDEX(table_name index_name)+*/生成执行计划
HR@honor1 > select /*+ INDEX(FUNDINFO FUNDINFO_PLANID)+*/ count(planid) from fundinfo where planid < 99000014000800382760;
COUNT(PLANID)
----------------------------------------
337473
（2）查询生成的执行计划
SQL> select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text like '%fundinfo%';
SQL_ID SQL_TEXT VERSION_COUNT PARSE_CALLS
------------- -------------------------------------------------------------------------------- ------------- -----------
gw6gd2ggk0h9m select /*+ INDEX(FUNDINFO FUNDINFO_PLANID)+*/ count(planid) from fundinfo where 1 1
adz60h2w2js6p select sql_id,sql_text,version_count,parse_calls from v$sqlarea where sql_text l 1 3
SQL> SELECT hash_value,sql_id,plan_hash_value,operation,options FROM v$sql_plan WHERE sql_id='gw6gd2ggk0h9m';
HASH_VALUE SQL_ID PLAN_HASH_VALUE OPERATION OPTIONS
---------- ------------- --------------- ------------------------------ ------------------------------
3743433011 gw6gd2ggk0h9m 2614089935 SELECT STATEMENT
3743433011 gw6gd2ggk0h9m 2614089935 SORT AGGREGATE
3743433011 gw6gd2ggk0h9m 2614089935 INDEX FULL SCAN
3.使用dbms_spm.load_plans_from_cursor_cache函数固定执行计划
# 说明
begin
:test := dbms_spm.load_plans_from_cursor_cache(
sql_id => '加入HINT后的SQL_ID',
plan_hash_value => 加入HINT后生成的plan_hash_value,
sql_handle => '旧的执行计划固定后dba_sql_plan_baselines中sql_handle');
end;
/
# 执行
SQL> begin
2 :test := dbms_spm.load_plans_from_cursor_cache(
3 sql_id => 'gw6gd2ggk0h9m',
4 plan_hash_value => 2614089935,
5 sql_handle => 'SQL_458606bd5b57f1c3');
6 end;
7 /
PL/SQL procedure successfully completed
# 查询基线
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf3686e388b MANUAL-LOAD
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf36b969528 MANUAL-LOAD
4.删除最开始固定的旧基线，使新的执行计划的新基线生效
SQL> var test number;
SQL> begin
2 :test := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_458606bd5b57f1c3', plan_name=>'SQL_PLAN_4b1h6rpdpgwf36b969528');
3 end;
4 /
PL/SQL procedure successfully completed
# 查询基线
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf3686e388b MANUAL-LOAD
5.测试新基线
HR@honor1 > explain plan for select count(planid) from fundinfo where planid < 99000014000800382760;
Explained.
HR@honor1 > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2614089935
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 27 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
|* 2 | INDEX FULL SCAN| FUNDINFO_PLANID | 18023 | 387K| 27 (4)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("PLANID")<99000014000800382760)
Note
-----
- SQL plan baseline "SQL_PLAN_4b1h6rpdpgwf3686e388b" used for this statement
18 rows selected.
# 可以看到走了索引全扫描，新基线已经生效。
6.删除基线
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ORIGIN
------------------------------ ------------------------------ --------------
SQL_458606bd5b57f1c3 SQL_PLAN_4b1h6rpdpgwf37d78711b AUTO-CAPTURE
var test number;
begin
:test := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_458606bd5b57f1c3', plan_name=>'SQL_PLAN_4b1h6rpdpgwf37d78711b');
end;
/
PL/SQL procedure successfully completed.
SYS@honor1 > select sql_handle, plan_name, origin from dba_sql_plan_baselines;
no rows selected
参考文档：
https://www.cnblogs.com/ddzj01/p/11377049.html
------------恢复内容结束------------