ITPub博客

首页 > 数据库 > Oracle > 11g-sql plan management

11g-sql plan management

原创 Oracle 作者:yangzhangyue 时间:2014-06-04 16:43:26 0 删除 编辑

sql plan management

    本部分描述如何用sql plan management管理sql plan

?  sql plan baselines概述

    Oracle Database 11g引入了一个名为SQL plan Management(SPM)的新计划稳定性特性,它让数据库通过sql plan baseline

控制sql计划的演变。SPM的目的是面对数据库升级、系统和数据库更改以及应用升级和故障修复等变动时保护sql代码的性能。

 

?  sql Plan baselines结构

一个计划基线包含一个或者多个接受的plan,每个计划包含如下信息:

n  hit集合

n  plan hash value

n  plan 相关信息

plan history是一个计划集,包括接受的和不接受的.只有接受的planssql plan baseline,baselineplanplan history的一个子集。

 

?  Managing Sql Plan Baselines

?  Capturing sql plan baselines

sql plan baseline捕获阶段,数据库探测plan 改变并记录新的plan,这样可以演变。为了这个目的,数据库为每一个sql语句维护了一个

plan history。因为特殊的sql不会重复因此不会遭受性能退化,数据库只会维护重复执行sqlplan history

为了识别重复执行的sql语句,数据库维护一个包含优化器评估过的sql id的日志(通过trace,可以看到,应该记录到sqllog$)

sql解析或者执行时,如果该sql已经记录了,数据库就认为这个sql是重复执行的。

每个重复执行的sql语句,数据库维护优化器产生的所有plan的一个plan history。在plan history中被接受的子集就是sql plan baseline.

sql plan 捕获阶段,有自动捕获和手动加载两种方式。

n  自动捕获

可以通过设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES true,启用sql计划基线自动装载,如下所示:

16:20:55 sys@vposdb> alter system set optimizer_capture_sql_plan_baselines=true;

 

System altered.

optimizer_capture_sql_plan_baselines默认值为false,表示自动假话捕捉默认关闭。

数据库使用每条sql语句的优化程序的详细数据重新生成执行计划。每条sql语句的第一个计划被标记为供优化程序使用。此时,计划历史和sql计划基线

是相同的。优化器随后生成的sql语句的所有新计划成为计划历史的组成部分。在最后的sql计划基线计划阶段(sql plan baseline evolution)中,

数据库将对sql计划基线添加已证明不会导致性能衰退的计划。

手动sql计划装载

n  从库缓存中加载

我们可以使用dbms_spm.load_plans_from_cursor_cache来将sql计划基线手工导入数据字典。此函数重载了多次以支持不同的方法来识别哪些游标需要处理。

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

   sql_id            IN  VARCHAR2,

   plan_hash_value   IN  NUMBER   := NULL,

   sql_text          IN  CLOB,

   fixed             IN  VARCHAR2 := 'NO',

   enabled           IN  VARCHAR2 := 'YES')

 RETURN PLS_INTEGER;

 

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

   sql_id            IN  VARCHAR2,

   plan_hash_value   IN  NUMBER   := NULL,

   sql_handle        IN  VARCHAR2,

   fixed             IN  VARCHAR2 := 'NO',

   enabled           IN  VARCHAR2 := 'YES')

 RETURN PLS_INTEGER;

 

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

   sql_id            IN  VARCHAR2,

   plan_hash_value   IN  NUMBER   := NULL,

   fixed             IN  VARCHAR2 := 'NO',

   enabled           IN  VARCHAR2 := 'YES')

 RETURN PLS_INTEGER;

 

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

   attribute_name   IN VARCHAR2,

   attribute_value  IN VARCHAR2,

   fixed            IN VARCHAR2 := 'NO',

   enabled          IN VARCHAR2 := 'YES')

  RETURN PLS_INTEGER;

 

我们可以通过两种方式来加载。

1.         通过指定以下属性中的一个来识别一些sql语句

attribute_name

    One of possible attribute names:

   'SQL_TEXT' sql语句的文本内容

   'PARSING_SCHEMA_NAME' 用于解析游标的schema

   'MODULE'           执行了这条sql语句的模块名

   'ACTION'           执行了这条语句的活动名

   例子如下

DECLARE

  ret PLS_INTEGER;

BEGIN

  ret := dbms_spm.load_plans_from_cursor_cache(attribute_name  => 'sql_text',

                                               attribute_value => '%/* MySqlStm */%');

  dbms_output.put_line(ret || ' SQL plan baseline(s) created');

END;

/

上面pl/sql块的意思是为库缓存里每一条文本中包含字符串MySqlStmsql语句创建一个计划基线。

2.         通过sql_id加载

通过sql_id加载比较简单,如下:

DECLARE

  ret PLS_INTEGER;

BEGIN

  ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&sql_id',

                                               plan_hash_value => NULL);

  dbms_output.put_line(ret || ' SQL plan baseline(s) created');

END;

/

这里面plan_hash_value是可选的,为null意味着该sql_id的所有可用的执行计划都会被加载。

 

CREATE TABLE t (id, n, pad, CONSTRAINT t_pk PRIMARY KEY (id))

AS

SELECT rownum, rownum, rpad('*',500,'*')

FROM dual

CONNECT BY level <= 1000;

 

CREATE INDEX i ON t (n);

 

11:01:27 sys@vposdb> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

 

COUNT(PAD)

----------

         1

 

Elapsed: 00:00:00.02

 

Execution Plan

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

Plan hash value: 2966233522

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |   505 |    21   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |   505 |            |          |

|*  2 |   TABLE ACCESS FULL| T    |     1 |   505 |    21   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("N"=42)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

         74  consistent gets

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

11:01:42 sys@vposdb> SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42;

 

COUNT(PAD)

----------

         1

 

Elapsed: 00:00:00.01

 

Execution Plan

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

Plan hash value: 3694077449

 

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

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |      |     1 |   505 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |      |     1 |   505 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   505 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I    |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("N"=42)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

明显第二个执行计划比第一个效率更高。如果我们无法修改sql可以使用sql计划基线来解决这个问题

10:45:22 SQL>

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

 

Executed in 0 seconds

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

 

COUNT(PAD)

----------

         1

 

Executed in 0.046 seconds

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

 

COUNT(PAD)

----------

         1

 

Executed in 0.047 seconds

ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

 

Executed in 0 seconds

 

13:59:08 sys@vposdb> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

 

COUNT(PAD)

----------

         1

 

Elapsed: 00:00:00.01

 

Execution Plan

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

Plan hash value: 2966233522

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |   505 |    21   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |   505 |            |          |

|*  2 |   TABLE ACCESS FULL| T    |     1 |   505 |    21   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("N"=42)

 

Note

-----

   - SQL plan baseline "SQL_PLAN_3u6sbgq7v4u8z3fdbb376" used for this statement

 

 

我们可以通过上面输出的计划名,可以通过数据字典视图dba_sql_plan_baselines找到sql计划基线的标示符:

sql句柄(handle)

14:04:55 sys@vposdb> select sql_handle

14:05:13   2  from dba_sql_plan_baselines

14:05:13   3  where plan_name='SQL_PLAN_3u6sbgq7v4u8z3fdbb376'   ;

 

SQL_HANDLE

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

SQL_3d1b0b7d8fb2691f

 

Elapsed: 00:00:00.00

14:05:14 sys@vposdb>

接下来,我们从库缓存中加载sql语句使用索引扫描的执行计划。

14:21:08 sys@vposdb> select sql_text,sql_id from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%' ;

 

SQL_TEXT

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

SQL_ID

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

 SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

7jvvrqqd0xffz

 

 explain plan for SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

4jwnyvhudy196

 

 explain plan for SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

4jwnyvhudy196

 

 

SQL_TEXT

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

SQL_ID

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

 explain plan for SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

4jwnyvhudy196

 

select sql_text,sql_id from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%'

02cnh5mgkagba

 

EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

d6pw9fs89anwp

 

 

SQL_TEXT

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

SQL_ID

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

select sql_text,sql_id from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%'

bjycuvyfj2yvs

 

SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

dat4n4845zdxc

 

select * from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%'

3hpk25qpwmxhw

 

 

9 rows selected.

 

Elapsed: 00:00:00.39

 

14:22:09 sys@vposdb> SELECT * FROM table (

14:23:34   2     DBMS_XPLAN.DISPLAY_CURSOR('dat4n4845zdxc'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  dat4n4845zdxc, child number 0

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

SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

 

Plan hash value: 3694077449

 

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

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |      |     1 |   505 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   505 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I    |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("N"=42)

 

 

20 rows selected.

 

Elapsed: 00:00:00.04

14:26:18 sys@vposdb> DECLARE

14:26:53   2    ret PLS_INTEGER;

14:26:53   3  BEGIN

14:26:53   4    ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => 'dat4n4845zdxc',

14:26:53   5                                                 plan_hash_value => NULL,

14:26:53   6                                                 sql_handle      =>'SQL_3d1b0b7d8fb2691f');

14:26:53   7    dbms_output.put_line(ret || ' SQL plan baseline(s) created');

14:26:53   8  END;

14:26:54   9  /

1 SQL plan baseline(s) created

 

PL/SQL procedure successfully completed.

14:27:47 sys@vposdb> select plan_name from dba_sql_plan_baselines where sql_handle='SQL_3d1b0b7d8fb2691f';

 

PLAN_NAME

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

SQL_PLAN_3u6sbgq7v4u8z3fdbb376

SQL_PLAN_3u6sbgq7v4u8z59340d78

我们现在已经有两个执行计划了。我们将全表扫描的baseline删除掉。

SET SERVEROUTPUT ON

14:56:40 sys@vposdb> DECLARE

14:56:59   2     ret PLS_INTEGER;

14:56:59   3   BEGIN

14:56:59   4     ret := dbms_spm.drop_sql_plan_baseline(sql_handle =>'SQL_3d1b0b7d8fb2691f',

14:56:59   5                                            plan_name  =>'SQL_PLAN_3u6sbgq7v4u8z3fdbb376'

14:56:59   6                                            );

14:56:59   7     dbms_output.put_line(ret || ' SQL plan baseline(s) created');

14:56:59   8   END;

14:56:59   9   /

1 SQL plan baseline(s) created

 

PL/SQL procedure successfully completed.

接下来,我们再次执行sql看看是否选用新的计划基线。

14:58:13 sys@vposdb>  SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

 

COUNT(PAD)

----------

         1

 

Elapsed: 00:00:00.07

 

Execution Plan

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

Plan hash value: 3694077449

 

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

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |      |     1 |   505 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |      |     1 |   505 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   505 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I    |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("N"=42)

 

Note

-----

   - SQL plan baseline "SQL_PLAN_3u6sbgq7v4u8z59340d78" used for this statement

我们看到及时sql语句中包含提示full,执行计划也不在使用全表扫描。

此外,我们还可以检查v$sqlsql_plan_baseline列。

16:21:40 sys@vposdb> select sql_plan_baseline from v$sql where sql_id='bhk25zu92v046';

 

SQL_PLAN_BASELINE

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

SQL_PLAN_3u6sbgq7v4u8z3fdbb376

SQL_PLAN_3u6sbgq7v4u8z3fdbb376

 

?  显示sql计划基线。

当我们让sql使用了baseline后,baseline对应什么plan呢,这个是我在看到这部门内容之前,思考但是没有查找到答案的问题。

使用

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_CURSOR('61p8t9216bc71',1));

并不能显示正确的执行计划,这个只是最初的执行计划,当然我们有一个方法,就是将sql_id剔出库缓存,

重新加载后,生成的执行计划为baseline之一(当一个sql有多个baseline)

SELECT * FROM TABLE(

    DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

        sql_handle=>'SQL_3d1b0b7d8fb2691f'));

       

16:51:18 sys@vposdb> SELECT * FROM TABLE(

17:11:54   2      DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

17:11:54   3          sql_handle=>'SQL_PLAN_3u6sbgq7v4u8z59340d78'));

 

PLAN_TABLE_OUTPUT

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

Error: specified SQL handle SQL_PLAN_3u6sbgq7v4u8z59340d78 does not exist

 

Elapsed: 00:00:00.02

17:11:55 sys@vposdb> SELECT * FROM TABLE(

17:12:21   2      DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

17:12:21   3          sql_handle=>'SQL_3d1b0b7d8fb2691f'));

 

PLAN_TABLE_OUTPUT

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

 

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

SQL handle: SQL_3d1b0b7d8fb2691f

SQL text:  SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42

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

 

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

Plan name: SQL_PLAN_3u6sbgq7v4u8z3fdbb376         Plan id: 1071362934

Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

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

 

Plan hash value: 2966233522

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |   505 |    21   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |   505 |            |          |

|*  2 |   TABLE ACCESS FULL| T    |     1 |   505 |    21   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("N"=42)

 

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

Plan name: SQL_PLAN_3u6sbgq7v4u8z59340d78         Plan id: 1496583544

Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD

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

 

Plan hash value: 3694077449

 

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

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |      |     1 |   505 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |      |     1 |   505 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |   505 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I    |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("N"=42)

 

46 rows selected.

 

Elapsed: 00:00:00.16

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

下一篇: awr的备份和恢复
请登录后发表评论 登录
全部评论

注册时间:2013-07-09

  • 博文量
    36
  • 访问量
    219937