ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 11g sql plan baseline(1)基本使用

oracle 11g sql plan baseline(1)基本使用

Linux操作系统 作者:宕机熟手 时间:2013-11-05 16:44:32 0 删除 编辑

学习11g sqlplan baselines

sql plan baselines将取代outline,由于目前负责的几套rac版本已经是11.1.0.7.2所学习下sqlplan base lines特此记录
当想获得一个稳定的执行计划时候可以使用sqlplan baselines,原理跟outline类似
下面将具体操作,简单讲解下原理

参考资料
1.otn
2.oracle trouble shooting performance

 

SQL> show user
USER is "SYS"


建立测试用表
SQL> create table t2 (a int);

Table created.

SQL> declare
  2  begin
  3  for i in 1..1000 loop
  4  insert into t2 values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

SQL> select * from t2 where a=2;

         A
----------
         2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  78nw3vwby13ha, child number 0
-------------------------------------
select * from t2 where a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=2)


18 rows selected.

可以看到现在走的是FTS


SQL> 建立sqlplan baselins 这是用参数方式建立(session,system可设置),这种叫自动捕获方式,不需要任何权限
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;(开启自动捕获)

Session altered.

自动捕获方式原理是 第一次sql语句执行 先查看数据字典看有对应的sqlplan baseline没(用签名查找),没有的话查看一个log(log中有签名,所谓的签名就是将sql语句格式化后的一个标记,可以不分大小写空格之类),没有的话在log中根据sql_text生成一个签

第2次执行SQL语句时候 还是先看有对应的sqlplan baselines没,没有的话查看log中有对应的签名没,有的话(此时已经有了),此时才存入sql plan baselines(这个时候存入的执行计划
,就是你sql语句此时此刻的执行计划,根据统计信息之类算出来的)
所以这个方式为一个sql语句生成sqlplan baseline需要执行2次sql

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE; (生成后关闭)

Session altered.


下面这个方法比较好,从shared pool->library cache中直接加载sql plan baseline,此时的plan 为sql语句执行的plan,为fts
SQL> DECLARE
  2    ret PLS_INTEGER;
  3  BEGIN
  4    ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&sql_id',
  5                                                 plan_hash_value => NULL);
  6    dbms_output.put_line(ret || ' SQL plan baseline(s) created');
  7  END;
  8  /
Enter value for sql_id: 78nw3vwby13ha
old   4:   ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&sql_id',
new   4:   ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '78nw3vwby13ha',

PL/SQL procedure successfully completed.

 

查看
SQL> SELECT sql_handle, sql_text, enabled, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE created > systimestamp - to_dsinterval('0 00:15:00');


SQL_HANDLE                     SQL_TEXT             ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES YES
                               re a=2


新生成的sql plan baselines

SQL> SQL> create index t2_ind on t2(a);

Index created.

此时建立一个index


SQL> select * from t2 where a=2;

         A
----------
         2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  78nw3vwby13ha, child number 1
-------------------------------------
select * from t2 where a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=2)

Note
-----
   - SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement~~~~使用了sql plan baselines
 

22 rows selected.

可以看到plan 被固定为fts了

 

SQL> select * from T2 where A=2; 让SQL语句 大小写

         A
----------
         2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6ja9pu3tjhw6w, child number 1
-------------------------------------
select * from T2 where A=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=2)

Note
-----
   - SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement ~~~~使用了sqlplan baseline

 

22 rows selected.
可以看到签名的作用,这样sql语句经过格式化不区分大小写 空格之类了
下面是空格

SQL> select                  * from t2 where  a=2;

         A
----------
         2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dj9yskcth9vj7, child number 1
-------------------------------------
select                  * from t2 where  a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=2)

Note
-----
   - SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement~~~~使用了sqlplan baseline


22 rows selected.

 

删除这个对象,对应的sqlplan baseline不会删除

SQL> drop table t2;

Table dropped.

SQL> SELECT sql_handle, sql_text, enabled, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE                     SQL_TEXT             ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES NO~~~这个baseline是走index的(建立index之后的)
                               re a=2

SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES YES   ~~这个baseline是走fts的
                               re a=2

 


分析:
首先可以看到对应的baseline未删除,有2个baseline其中一个是未建立index前生成走fts的baseline
第2个是怎么生成的呢,生成第一个fts baseline后,建立index然后 再次执行sql语句,此时sql语句用签名查找sqlplan baselines,此时发现存在baselines(走fts的),关键步骤到了
此时这个sql语句用当前统计信息(此时有index统计信息了)计算出的plan与存在的baseline比较执行计划一样不,此时比较是index scan与fts比较,发现不一样,那么走index的执行计划
存入为一个新baseline,注意这个新baseline accepted属性为no此时表示这个baseline不能使用(即便他的执行计划是更好的,需要演化,稍后讲解演化),所以还是使用了走fts的baseline

 

 

此时在重新建立对象
SQL> create table t2 (a int);

Table created.

 


SQL> declare
  2  begin
  3  for i in 1..500 loop~~~~~数据量有变化
  4  insert into t2 values(i);
  5  commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.


SQL> create index t2_id on t2(a);

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

 

 


SQL> select                  * from t2 where  a=2;

         A
----------
         2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dj9yskcth9vj7, child number 1
-------------------------------------
select                  * from t2 where  a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=2)

Note
-----
   - SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement


22 rows selected.


可以看到由于sql baseline未删除 所以 可以直接使用


又多了一个baseline,原因很简单,我们重新建立的表T2 数据量变化了(上面还有index,所以产生了不一样 的plan,存入为了新baseline但accetped为no,新的都为no),还是用了fts的
baseline

SQL> SELECT sql_handle, sql_text, enabled, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE                     SQL_TEXT             ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES NO
                               re a=2

SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES NO
                               re a=2


SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES YES
                               re a=2

 

 

补充:

用dbm_spm建立baseline时候,不需要对象权限,需要下面系统权限(default dba role拥有)
SQL> select * from session_privs where privilege like '%ADMINISTER SQL MAN%';

PRIVILEGE
----------------------------------------
ADMINISTER SQL MANAGEMENT OBJECT

 

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-11-05

  • 博文量
    5
  • 访问量
    8931