为什么需要稳定执行计划
由于各种原因（如统计信息不准确，CBO成本计算公式缺陷）导致CBO产生效率不高、甚至错误的执行计划。
某个SQL原先跑的好好的，为什么突然慢的让人无法接受，这种效率衰减往往是因为目标SQL执行计划的改变。
CBO产生了错误的执行计划，我们要如何纠正
1. 重新收集统计信息，但有时不能解决问题
2. 修改目标SQL(比如在目标SQL中加入Hint)文本，第三方软件，不能修改源码
3. 使用SQL Profile(10g以上)或SPM(SQL Plan Management)(11g以上)
SQL Profile 有两种类型
一种是Automatic类型，另一种是Manual类型。
Automatic 类型的SQL Profile 的本质就是针对目标SQL的一些额外的调整信息，这些额外的调整信息需要与原目标SQL的相关统计信息等内容一起作用才能得到新的执行计划，即原始SQL的统计信息等内容一旦发生变化，即使原有 Automatic 类型的SQL Profile并没有改变，该SQL的执行计划也可能会发生变化。
从这个意义上讲，Automatic类型的SQL Profile并不能完全起到稳定目标SQL的执行计划的作用，虽然它确实可以用来调整执行计划。
创建测试环境
create table t1 (n number);
declare
begin
for i in 1 .. 10000
loop
insert into t1 values(i);
end loop;
commit;
end;
/
select count(*) from t1;
create index idx_t1 on t1(n);
exec dbms_stats.gather_table_stats(ownname=>'ZYLONG',tabname=>'T1',method_opt=>'for all columns size 1',CASCADE=>true);使用强制不使用索引IDX_T1的Hint，模拟执行计划错误
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 1kg76709mx29d, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]使用 SQL Tuning Advisor 尝试对这条SQL生成Automatic类型的SQL Profile。
先针对上述SQL创建一个名为 my_sql_tuning_task_2 的自动调整任务：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select /*+ no_index(t1 idx_t1) */ * from t1 where n=1';
my_task_name := dbms_sqltune.create_tuning_task (
sql_text => my_sqltext,
user_name => 'ZYLONG',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task_2',
description => 'Task to tune a query on table t1'
);
END;
/
/*
删除任务
BEGIN
dbms_sqltune.drop_tuning_task( task_name => 'my_sql_tuning_task_2');
END;
/
*/接着执行上述自动调整任务：
BEGIN
dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_2');
END;
/查看上述调整任务的结果：
set long 9000
set longchunksize 1000
set linesize 800
select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : ZYLONG
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 11/08/2019 16:04:32
Completed at : 11/08/2019 16:04:32
-------------------------------------------------------------------------------
Schema Name: ZYLONG
SQL ID : 4bh6sn1zvpgq7
SQL Text : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
为该语句找到了可能更好的执行计划。
Recommendation (estimated benefit: 90.91%)
------------------------------------------
- Consider accepting the recommended SQL profile.
考虑接受推荐的SQL配置文件。
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', task_owner => 'ZYLONG', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
已对 SQL profile 进行测试，方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000129 .000013 89.92 %
CPU Time (s): .0001 0 100 %
User I/O Time (s): 0 0
Buffer Gets: 22 2 90.9 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
2- Using SQL Profile
--------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
-------------------------------------------------------------------------------从上面的调整结果可以看到，Oracle已经为目标SQL找到了更好的执行计划，并且已经创建了针对该SQL的 Automatic 类型的SQL Profile。
如果我们使用execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'ZYLONG', replace => TRUE);接受了这个SQL Profile，则目标SQL的响应时间将会有89.92%的改善，逻辑读将会有90.9%的改善，并且目标SQL的执行计划将会由原先对T1表的全表扫描变为对索引IDX_T1的索引范围扫描。
执行调整结果，接受Oracle的SQL Profile
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'ZYLONG', replace => TRUE);测试查询，使用了SQL Profile，走INDEX RANGE SCAN
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 1kg76709mx29d, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
Note
-----
- SQL profile SYS_SQLPROF_016e4a1ae6a30000 used for this statementdbms_sqltune.accept_sql_profile 的输入参数 FORCE_MATCH 的默认值为 FALSE, 表示SQL文本完全匹配的情况下才会应用SQL Profile，只要目标SQL文本发生一点改动，原有的SQL Profile将失去作用。
将上述SQL的where条件由 n=1 改为 n=2 ，走TABLE ACCESS FULL，SQL Profile失去作用
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID c4j6hxkqudj1s, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
如果想使上述SQL Profile依然对改动后的SQL生效，我们可以在执行 dbms_sqltune.accept_sql_profile 时指定输入参数 FORCE_MATCH 的值为TRUE。
FORCE_MATCH 的值为TRUE的含义，是指即使目标SQL的where条件中具体的输入值发生了改变（不如由 n=1 改为 n=2），原SQL Profile依然有效，这就相当于将目标SQL的where条件中具体的输入值用绑定变量替换了。
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'ZYLONG', replace => TRUE, force_match => true);删除SQL Profile
execute dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016e4a284cff0001');测试
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID c4j6hxkqudj1s, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
Note
-----
- SQL profile SYS_SQLPROF_016e4a284cff0001 used for this statement
select /*+ no_index(t1 idx_t1) */ * from t1 where n=3;
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 0zz8t0qnm15hj, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=3
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
Note
-----
- SQL profile SYS_SQLPROF_016e4a284cff0001 used for this statement其他参考资料：
Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)
https://blog.csdn.net/u010692693/article/details/102983477
How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (Doc ID 1253696.1)
1712
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
