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 的统计信息是后面的 9 执行的平均值。

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

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

 

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

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

 

时间： 2024-10-25 14:06:17

oracle 如何稳定执行计划的相关文章

【SPM】Oracle如何固定执行计划

[SPM]Oracle如何固定执行计划   1.1  BLOG文档结构图   1.2  前言部分   1.2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 固定执行计划的常用方法:outline.SQL Profile.SPM(重点) ② coe_xfr_sql_profile.sql脚本的使用     Tips:        ① 若文章代码格式有错乱,推荐使用QQ.搜狗或360浏览器,也可以下载pdf格式

Oracle中获取执行计划的几种方法分析

以下是对Oracle中获取执行计划的几种方法进行了详细的分析介绍,需要的朋友可以参考下   1. 预估执行计划 - Explain PlanExplain plan以SQL语句作为输入,得到这条SQL语句的执行计划,并将执行计划输出存储到计划表中. 首先,在你要执行的SQL语句前加explain plan for,此时将生成的执行计划存储到计划表中,语句如下: explain plan for SQL语句然后,在计划表中查询刚刚生成的执行计划,语句如下: select * from table(

oracle 12c R1执行计划新特性-table access by index rowid batched和INMOMEORY OPTION

oracle 12c R1执行计划在索引回表阶段oracle推出了batched特性,类似于oracle 11g中在nested loop中被驱动表回表时的向量IO,也是为了有效的解决表中数据无序性(索引的聚簇因子),下面看实际测试用例: 数据库版本:12.1.0.2版本 sys@CRMDB2> explain plan for SELECT offering_inst_id,        offering_id,        owner_party_role_type,        ow

怎样看oracle查询语句执行计划?

oracle|语句|执行 SQLPLUS的AutoTrace是分析SQL的执行计划,执行效率的一个非常简单方便的工具,在绝大多数情况下,也是非常有用的工具. 1.如何设置和使用AUTOTRACE SQL> connect / as sysdba SQL> @?/rdbms/admin/utlxplan.sql Table created. SQL> create public synonym plan_table for plan_table; Synonym created. SQL&

Oracle中获取执行计划的几种方法

1. 预估执行计划 - Explain Plan Explain plan以SQL语句作为输入,得到这条 SQL语句的执行计划,并将执行计划输出存储到计划表中. 首先,在你要执行的SQL语 句前加explain plan for,此时将生成的执行计划存储到计划表中,语句如下: explain plan for SQL语句 然后,在计划表中查询刚刚生成的执行计划,语 句如下: select * from table(dbms_xplan.display); 注意:Explain plan 只生成执

使用Oracle脚本查看执行计划

声明:脚本来自<Pro Oracle SQL>一书,pln.sql 下面只是一个查看执行计划一种方法,就是通过加上备注表示唯一SQL语句: [oracle@maa3 ~]$ cat pln.sql SELECT xplan.* FROM     (   select max(sql_id) keep  (dense_rank last order by last_active_time) sql_id , max(child_number) keep  (dense_rank last ord

如何分析ORACLE的SQL执行计划 .

1,先举个例子: -------------------------------------------------------------------------------- | Id  | Operation                        | Name                    | Rows  | Byt -------------------------------------------------------------------------------

ORACLE数据库查看执行计划

基于ORACLE的应用系统很多性能问题,是由应用系统SQL性能低劣引起的,所以,SQL的性能优化很重要,分析与优化SQL的性能我们一般通过查看该SQL的执行计划,本文就如何看懂执行计划,以及如何通过分析执行计划对SQL进行优化做相应说明. 一.什么是执行计划(explain plan) 执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述. 二.如何查看执行计划 1: 在PL/SQL下按F5查看执行计划.第三方工具toad等. 很多人以为PL/SQL的执行计划只能看到基数.优化器.耗

ORACLE数据库查看执行计划的方法_oracle

一.什么是执行计划(explain plan) 执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述. 二.如何查看执行计划 1: 在PL/SQL下按F5查看执行计划.第三方工具toad等. 很多人以为PL/SQL的执行计划只能看到基数.优化器.耗费等基本信息,其实这个可以在PL/SQL工具里面设置的.可以看到很多其它信息,如下所示 2: 在SQL*PLUS(PL/SQL的命令窗口和SQL窗口均可)下执行下面步骤 复制代码 代码如下: SQL>EXPLAIN PLAN FOR SEL