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.

本文永久更新链接地址

相关内容