26 Oracle深度学习笔记——SQL基线

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

               

26.Oracle深度学习笔记——SQL基线

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50830903

1.  SQL计划基线

     SQL计划基线可以理解是存储提纲的一个改进版本，不仅和存储提纲有许多相同的特性，而且也和存储提纲一样被设计用来提供稳定的执行计划，以防执行环境和对象统计信息的改变对执行计划产生影响。此外，和存储提纲类似，也可以在不修改语句的情况下调优应用程序。

维持执行计划的稳定性是SQL计划基线唯一被提及的用途．

    SQL计划基线是一个与SQL语句相关联的对象，它被设计用来影响查询优化器产生执行计划时的决定。具体地讲，SQL计划基线主要是一个提示的集合。基本上，SQL计划基线就是用来迫使查询优化器为一条给定的SQL语句产生个特定的、稳定的执行计划。

    有多种方法可用来捕获SQL计划基线。基本上，它们都是由数据库引擎自动创建或数据库管理员手动创建。

2.  基线自动捕获

       当动态初始化参数optimizer_use_sql_plan_baselines设置为true的时候，查询优化器将自动创建一个新的SQL计划基线。这个初始化参数被默认设置为FALSE，可以在系统级和会话级修改它。当自动捕获开启后，查询优化器为每条重复执行过（就是至少执行过两次）的SQL 语句存储一个新的SQL 计划基线。为此．它会将每条SQL 语句的签名插入一个日志中，以便于管理。

    这意味着当一条SQL语句第一次执行的时候，仅把它的签名插入日志。然后，当第二次执行相同的语句的时候，如果不存在与此语句相对应的SQL计划基线，就新建一个并存储起来。如果与SQL语句相对应的SQL计划基线已经存在，查询优化器仍然会对比当前的执行计划和基于此SQL计划基线的执行计划。如果它们不匹配，那么这个描述当前执行计划的新的SQL计划基线将被存储。然而就像你在前面见到的，不能直接使用当前的执行计划。查询优化器被强制使用在SQL 计划基线的辅助下产生的执行计划。

计划基线用来指导优化器始终选择某一个执行计划。通过计划基线，可以将执行计划存储在数据库的表中并进行管理。计划基线由一个或多个已经被接受到的SQL查询执行计划组成。运行一个查询，且该查询已经存在计划基线，优化器就会优先考虑计划基线中的执行计划。

3.  测试

用SQL_ID创建基线

tpcc@TOADDB> create table t as select * fromall_objects;

Table created.
tpcc@TOADDB>create index t_idx on t(object_name);

Index created.

收集统计信息：

tpcc@TOADDB> execdbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

tpcc@TOADDB> select/*test_01*/object_id,object_type from t where object_name ='DUAL';

 OBJECT_ID OBJECT_TYPE

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

      142 TABLE

      143 SYNONYM

查找所执行SQL的ID

tpcc@TOADDB> select sql_id,sql_text from v$sqlwhere sql_text like 'select /*test_01*/%';

SQL_ID

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

SQL_TEXT

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

farns9fxz7dum

select /*test_01*/object_id,object_typefrom t where object_name ='DUAL'

SQL计划基线的管理，需要拥有administersql management object权限。

查看已有基线：

SQL>select sql_handle,plan_name fromdba_sql_plan_baselines where sql_text like 'select /*test_01*/%';

no rows selected

使用dbms_spm.load_plans_from_cursor_cache加载sql_id对应的SQL的执行计划。

declare

      x pls_integer;

   begin

      x := dbms_spm.load_plans_from_cursor_cache(sql_id => 'farns9fxz7dum');

   end;

  /

再查看创建的基线如下：

tpcc@TOADDB> select sql_handle,plan_name fromdba_sql_plan_baselines where sql_text like 'select /*test_01*/%';

SQL_HANDLE

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

PLAN_NAME

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

SQL_718c094fd7a20a4d

SQL_PLAN_733099zbu42kda0b930be

如此就为需要创建计划基线的SQL创建了计划基线

用sql文本来创建计划基线

如下

tpcc@TOADDB>select object_id from t whereobject_name = 'DUAL';

 OBJECT_ID

----------

      142

      143

declare

       xpls_integer;

    begin

       x :=dbms_spm.load_plans_from_cursor_cache(

               attribute_name => 'SQL_TEXT',

              attribute_value => 'select object_id from t%'); 

      dbms_output.put_line(x);

    end;

  /

PL/SQL procedure successfully completed.

查看如下：

tpcc@TOADDB> select sql_handle,plan_name fromdba_sql_plan_baselines where sql_text like 'select object_id from t%';

SQL_HANDLE

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

PLAN_NAME

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

SQL_b7598beb2a522d9d

SQL_PLAN_bfqcbxcp54bcxa0b930be

显示一条SQL的执行计划

我们可以使用dbms_xplan.display_sql_plan_baseline来完成，如下：

tpcc@TOADDB> select sql_handle

   from dba_sql_plan_baselines

   where plan_name = 'SQL_PLAN_bfqcbxcp54bcxa0b930be'

    /

SQL_HANDLE

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

SQL_b7598beb2a522d9d

tpcc@TOADDB> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=> 'SQL_b7598beb2a522d9d'));

PLAN_TABLE_OUTPUT

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

 

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

SQL handle: SQL_b7598beb2a522d9d

SQL text: select object_id from t whereobject_name = 'DUAL'

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

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