Automatic的SQL Profile来稳定执行计划

我们都希望Oracle数据库的执行的SQL，CBO都能够产生正确的执行计划，但是事实上由于各种原因（例如SQL所对应的对应的统计信息不准确，或者CBO内部一些计算公式的缺陷等），导致了CBO会产生效率不高的，甚至是错误的执行计划。特别是CBO对目标SQL所产生的初始执行计划是正确的，后来由于各种原因（比如统计信息的变更），导致了CBO重新产生了一个错误的执行计划，这种执行计划的改变往往会导致目标SQL执行时间呈一个数量级的递增，而且通常会给我们造成一个困惑，一条SQL原本可以正常的运行，但是为什么会突然变得很慢？其实这种SQL执行效率突然的衰减往往是因为目标SQL执行计划的改变。这时候我们可以使用SQL_Profile或者SPM来解决执行计划变更的问题，用他们来调整稳定目标的SQL执行计划。下面进行一个Automatic的SQL Profile来稳定执行计划的实验。1.创建一个测试表并插入数据，并创建相对应的索引
SQL> create table t1（n number）; Table created. SQL> declare
2 begin
3 for i in 1 .. 10000
4 loop
5 insert into t1 values（i）;
6 commit;
7 end loop;
8 end;
9 / PL/SQL procedure successfully completed. SQL> select count（*） from t1; COUNT（*）
----------
10000 SQL> create index idx_t1 on t1（n）; Index created. 2.对表T1收集统计信息SQL> exec dbms_stats.gather_table_stats（ownname =>"SYS",tabname =>"T1",method_opt =>"for all columns size 1",CASCADE =>true）; PL/SQL procedure successfully completed. 3.使用hint强制不使用索引，来模拟那些执行计划错误的SQL，并查看执行计划。
SQL> select /*+ no_index（t1 idx_t1） */ * from t1 where n=1; N
----------
1 SQL> 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 | | | | 7 （100）| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 （0）| 00:00:01 | PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------- Query Block Name / Object Alias （identified by operation id）:
------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data
------------- /*+ PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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）: PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------- 1 - filter（"N"=1） Column Projection Information （identified by operation id）:
----------------------------------------------------------- 1 - "N"[NUMBER,22] 42 rows selected.
从上面的内容我们不难发现，这条sql语句所走的是全表扫描，但是这显然是个错误的执行计划，正确的执行计划，我们应该是走索引。我们现在使用SQL Tuning Advisor来尝试对这条SQL进行通过产生Automatic类型的SQL Profile 4. 创建一个名为my_sql_tuning_task2的自动调整任务
SQL> declare
2 my_task_name varchar2（30）;
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext :="select /*+ no_index（t1 idx_t1） */ * from t1 where n=1";
6 my_task_name := dbms_sqltune.create_tuning_task（
7 sql_text => my_sqltext,
8 user_name => "SYS",
9 scope => "COMPREHENSIVE",
10 time_limit => 60,
11 task_name => "my_sql_tuning_task_2",
12 description =>"TASK to tune a query on table t1"）;
13 END;
14 /

PL/SQL procedure successfully completed.
然后执行上述自动调整任务SQL> begin
2 dbms_sqltune.execute_tuning_task（ task_name => "my_sql_tuning_task_2"）;
3 end;
4 / PL/SQL procedure successfully completed.
然后我们就可以使用DBMS_SQLTUNE.REPORT_TUNING_TASK来查看上述自动调整任务的调整结果：
SQL> set long 9000
SQL> set longchunksize 1000
SQL> set linesize 800
SQL> select dbms_sqltune.report_tuning_task（ "my_sql_tuning_task_2"） from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
-------------------------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit（seconds）: 60
Completion Status : COMPLETED
Started at : 04/13/2016 23:08:28
Completed at : 04/13/2016 23:08:28 DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
---------------------------------------------------------------------------------------------------
Schema Name: SYS
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）
-------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
----------------------------------------------------------------------------- Recommendation （estimated benefit: 90.91%）
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile（task_name =>
"my_sql_tuning_task_2", task_owner => "SYS", replace => TRUE）; Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"） DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
------------------------------------------------------------------------------------------------
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. DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
------------------------------------------------------------------------------------- -------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
------------------------------------------------------------------------------- 1- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013 --------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 （0）| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 （0）| 00:00:01 |
-------------------------------------------------------------------------- Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - filter（"N"=1） 2- Using SQL Profile DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
-------------------------------------------------------------------------------------------------
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）: DBMS_SQLTUNE.REPORT_TUNING_TASK（"MY_SQL_TUNING_TASK_2"）
------------------------------------------------------------------------------------------------ 1 - access（"N"=1） -------------------------------------------------------------------------------
从上面的调整结果，我们可以看到，他已经为我们目标SQL找到了更好的执行计划，并且也完成了针对该SQL的Automatic类型的SQL Profile，如果我们使用 execute dbms_sqltune.accept_sql_profile（task_name => "my_sql_tuning_task_2", task_owner => "SYS", replace => TRUE）;
相应时间将会有89.9%的改善，逻辑读将会有 90.9 %的改善，并且接受后将会有全表扫描改变为IDX_T1的索引范围扫描。然后我们按照oracle提示接受这个SQL profile，并重新查看执行计划
SQL> execute dbms_sqltune.accept_sql_profile（task_name => "my_sql_tuning_task_2", task_owner => "SYS", replace => TRUE）; PL/SQL procedure successfully completed. SQL> select /*+ no_index（t1 idx_t1） */ * from t1 where n=1; N
----------
1 SQL> 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 | PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------- Query Block Name / Object Alias （identified by operation id）:
------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data
------------- /*+ PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
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）: PLAN_TABLE_OUTPUT
------------------------------------------------ 1 - access（"N"=1） Column Projection Information （identified by operation id）:
----------------------------------------------------------- 1 - "N"[NUMBER,22] Note
----- PLAN_TABLE_OUTPUT
-------------------------------------------------
- SQL profile SYS_SQLPROF_0154103a51870000 used for this statement 46 rows selected. 我们可以看到Note部分SQL profile SYS_SQLPROF_0154103a51870000 used for this statement，这说明我们刚才接受的SQL Profile已经生效了，这同时也说明Automatic类型的SQL Profile确实可以再不改变目标SQL的SQl文本的情况下更改其执行计划接下来我们尝试将where的条件从n=1改变为n=2,并查看执行计划 SQL> select /*+ no_index（t1 idx_t1） */ * from t1 where n=2; N
----------
2 SQL> select * from table（dbms_xplan.display_cursor（null,null,"advanced"））; PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 36wrvgrswajnh, 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 | | | | 7 （100）| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 （0）| 00:00:01 | PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- Query Block Name / Object Alias （identified by operation id）:
------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data
------------- /*+ PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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）: PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- 1 - filter（"N"=2） Column Projection Information （identified by operation id）:
----------------------------------------------------------- 1 - "N"[NUMBER,22] 42 rows selected.
我们发现还是走了全表扫描，要想使上面的SQL_PROFILE 生效，我们需要加上FORCE_MATCH=TRUE,true的含义，就是where条件中值发生变化，但是SQL_Profile仍然有效SQL> execute dbms_sqltune.accept_sql_profile（task_name => "my_sql_tuning_task_2", task_owner => "SYS", replace => TRUE,force_match => true）; PL/SQL procedure successfully completed.再次查看相对应的执行计划
SQL> select /*+ no_index（t1 idx_t1） */ * from t1 where n=2;
N
----------
2
SQL> 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 | PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- Query Block Name / Object Alias （identified by operation id）:
------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data
------------- /*+ PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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）: PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- 1 - access（"N"=2） Column Projection Information （identified by operation id）:
----------------------------------------------------------- 1 - "N"[NUMBER,22] Note
----- PLAN_TABLE_OUTPUT
---------------------------------------------
- SQL profile SYS_SQLPROF_015410470fa40001 used for this statement 46 rows selected. 这是我们可以发现这次的执行计划走的是索引，为了再次验证新生成的SQL_Profile对其他值也有效，我们再次尝试n=3 SQL> select /*+ no_index（t1 idx_t1） */ * from t1 where n=3; N
----------
3 SQL> 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 | PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------- Query Block Name / Object Alias （identified by operation id）:
------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data
------------- /*+ PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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）:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------- 1 - access（"N"=3） Column Projection Information （identified by operation id）:
----------------------------------------------------------- 1 - "N"[NUMBER,22] Note
----- PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- SQL profile SYS_SQLPROF_015410470fa40001 used for this statement 46 rows selected.
结论是仍旧有效。--End.本文永久更新链接地址