ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Automatic的SQL Profile来稳定执行计划

Automatic的SQL Profile来稳定执行计划

原创 Oracle 作者：Otakuzel 时间：2016-04-13 23:43:21 0 删除 编辑
我们都希望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.




来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30604784/viewspace-2080871/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： STA,SAA的简单实验
下一篇： 使用SPM来稳定执行计划
请登录后发表评论 登录
全部评论
Otakuzel

注册时间：2015-12-01

  • 博文量
    58
  • 访问量
    220280

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593