ITPub博客

首页 > 数据库 > Oracle > AUTOMATIC SQL Profile稳定执行计划

AUTOMATIC SQL Profile稳定执行计划

原创 Oracle 作者:oracle单细胞 时间:2018-08-11 11:21:21 0 删除 编辑

实验目的: 借助SQL调优工具进行对改变执行计划,从而达到稳定执行计划。

关键词: dbms_stats.gather_table_stats、 DBMS_SQLTUNE.EXECUTE_TUNING_TASK、 DBMS_SQLTUNE.EXECUTE_TUNING_TASK、

              DBMS_SQLTUNE.REPORT_TUNING_TASK、 EXECUTE  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE

一、创建表,收集表统计信息

SQL> create  table t1( n number);

表已创建。

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 过程已成功完成。

 SQL>   select  count(*)  from  t1;

  COUNT(*)

----------

     10000

SQL> create index  idx_t1  on  t1(n);

索引已创建。  

 

SQL> exec dbms_stats.gather_table_stats( ownname =>'TEST' , tabname =>'T1' , method_opt =>'for all columns size 1', CASCADE => TRUE);

PL/SQL 过程已成功完成。

二、查询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  gn8zuq00kd86g, 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行。

三、使用SQL调优工具,创建任务、执行优化

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  =>'TEST',

  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 过程已成功完成。

SQL> BEGIN

  2      DBMS_SQLTUNE.EXECUTE_TUNING_TASK( TASK_NAME =>'my_sql_tuning_task_2');

  3      END;

  4  /

PL/SQL 过程已成功完成

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')

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : my_sql_tuning_task_2

Tuning Task Owner  : TEST

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 03/27/2018 14:46:51

Completed at       : 03/27/2018 14:46:52

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: TEST

SQL ID     : agf66q2by2qvp

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.9%)

  -----------------------------------------

  - 考虑接受推荐的 SQL 概要文件。

    execute dbms_sqltune.accept_sql_profile(task_name =>

            'my_sql_tuning_task_2', task_owner => 'TEST', replace => TRUE);

  Validation results

  ------------------

  已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时 间内就完成,

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  则另一计划可能只执行了一部分。

                           Original Plan  With SQL Profile  % Improved

                           -------------  ----------------  ----------

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .000121           .000013      89.25 %

  CPU Time (s):                       0                 0

  User I/O Time (s):                  0                 0

  Buffer Gets:                       22                 2       90.9 %

  Physical Read Requests:             0                 0

  Physical Write Requests:            0                 0

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. the original plan 的统计信息是 10 执行的平均值。

  2. the SQL profile plan 的统计信息是 10 执行的平均值。

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> EXECUTE  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE( TASK_NAME =>'my_sql_tuning_task_2',task_owner=> 'TEST' ,REPLACE => TRUE);

PL/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  gn8zuq00kd86g, 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_0162663bdb700000 used for this statement

已选择46行。

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  c7rgfm3dqkzz7, 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 |    ---发现当条件改变,执行计划仍然是全表扫描,可以通过    DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 添加                                                                                                                              --- force_match => true 实现SQL脚本具体输入值变化而执行计划不变

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行。

SQL> EXECUTE  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE( TASK_NAME =>'my_sql_tuning_task_2',task_owner => 'TEST' ,REPLACE => TRUE, force_match => true );

PL/SQL 过程已成功完成。

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  c7rgfm3dqkzz7, 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_01626643a6130001 used for this statement

已选择46行。


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

请登录后发表评论 登录
全部评论

注册时间:2014-07-07

  • 博文量
    16
  • 访问量
    10927