oracle 如何稳定执行计划

2.5.1  automatic sql profile 调整执行计划

  适合sql无法改写或验证改写是否成功的情况

  验证:

 Oracel 账号SYS

Conn /as sysdba;

 

1>   create table t1(n number);

 

 

2>   declare

     begin

     for i in 1 ..10000

     loop

     insert into t1 values(i);

     commit;

     end loop;

     end;

     /

 

 

3>  select count(*) from t1;

 

4>  create index idx_t1 on t1(n);

 

 

5>  exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',method_opt=>'for all columns size 1',cascade=>true);

 

6>  select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;

 

7>  select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

 

8> 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 => 'SYS',

    scope => 'COMPREHENSIVE',

    time_limit => 60,

    task_name => 'my_sql_tuning_task_2 ',

    description =>  ' Task to rune a query on table t1 ');

    end;

   /

 

 

 9>  begin

      dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_2 ');

    end;

     /

 

10> 

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;

 

 

 

 

GENERAL INFORMATION SECTION

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

Tuning Task Name   : my_sql_tuning_task_2

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Execution Count    : 2

Current Execution  : EXEC_277

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 04/17/2016 12:09:37

Completed at       : 04/17/2016 12:09:37

 

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

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)

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

  为此语句找到了性能更好的执行计划。

 

  Recommendation (estimated benefit: 95%)

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

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

    execute dbms_sqltune.accept_sql_profile(task_name =>

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

 

  Validation results

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

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

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

 

                           Original Plan  With SQL Profile  % Improved

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

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time(us):                 597                68       88.6 %

  CPU Time(us):                       0                 0

  User I/O Time(us):                  0                 0

  Buffer Gets:                       20                 1         95 %

  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. original plan 已首先执行以预热缓冲区高速缓存。

  2. original plan 的统计信息是后面的 执行的平均值。

  3. SQL profile plan 已首先执行以预热缓冲区高速缓存。

  4. the SQL profile plan 的统计信息是后面的 执行的平均值。

 

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

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

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

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)

 

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

 

 

 

 

 

11>  execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2 ', task_owner => 'SYS', replace => TRUE);

 

12> 再次执行    select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;

      select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

   原来走全表现在走索引范围range扫描了,起到了变更作用,但是,一旦sql参数值或其他变化就会改变这个已调整的automatic  sql profile

 

 

13>  验证参数值发生改变,又回到全表扫描了

 

     select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;

     select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

    

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  |      |       |       |     7 (100)|          |

|*  1 |  TABLE ACCESS FULL| T1   |     1 |     4 |     7   (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.1')

      DB_VERSION('11.2.0.1')

      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]

 

 

 

14> automatic profile 永久生效添加force_match=true,默认force_match=false

 

   execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2 ', task_owner => 'SYS', replace => TRUE,force_match=>true);

  

 

 

注意  SYS_SQLPROF_0154228b55fe000是否一样

 

 

SQL_ID  fd5p89b5jz0ct, child number 0

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

select /*+ no_index(t1 idx_t1) */ * from t1 where n=4

 

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

      DB_VERSION('11.2.0.1')

      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"=4)

 

Column Projection Information (identified by operation id):

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

 

   1 - "N"[NUMBER,22]

 

Note

-----

   - SQL profile SYS_SQLPROF_0154228b55fe0001 used for this statement

 

 

已选择46行。

 

SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=5;

 

         N

----------

         5

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  6u34k01s3c4rg, child number 0

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

select /*+ no_index(t1 idx_t1) */ * from t1 where n=5

 

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

      DB_VERSION('11.2.0.1')

      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"=5)

 

Column Projection Information (identified by operation id):

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

 

   1 - "N"[NUMBER,22]

 

Note

-----

   - SQL profile SYS_SQLPROF_0154228b55fe0001 used for this statement

 

 

已选择46行。

 

posted @ 2016-04-17 21:23  172257861  阅读(144)  评论(0编辑  收藏  举报