sql优化技术--学习笔记

《oracle性能诊断艺术》学习笔记

常用如下几种技术来实现SQL优化：

改变访问结构，修改SQL语句，提示，改变执行环境，SQL概要，存储提纲，SQL计划基线。

      修改SQL语句 

     找一条等价的SQL

      提示(hint)

        为了影响查询优化器的决定而添加到SQL语句中的提示。(查询优化器并不总会利用它)。

        查询优化器以决策树的形式运行，通过提示减去不需要的分支。

提示的分类
       初始化参数提示（可以覆盖在系统级或会话级定义的部分初始化参数）：all_rows， cursor_sharing_exact，dynamic_sampling ，first_rows，gather_plan_statistics，no_cpu_costing，optimizer_features_enable，opt_param，(no_)result_cache以及rule
       查询转化提示：(no_)eliminate_join，no_expand，(no_)merge，(no_)outer_join_to_inner，(no_)push_pred,(no_)push_subq,no_query_transformation,(no_)rewrite,(no_)unnest,no_xmlindex_rewrite,no_xml_query_rewrite,以及use_concat。
       访问路径提示（控制访问数据的方式，是否使用索引） cluster，full，hash,(no_)index,index_asc,index_combine,index_desc,(no_)index_ffs,index_join,(no_)index_ss,index_ss_asc,以及index_ss_desc
        连接提示（不仅控制连接的方法，而且控制连接表的顺序）：leading，(no_)nlj_batching,ordered,(no_)star_transformation,(no_)swap_join_inouts,(no_)use_hash,(no_)use_merge,use_merge_cartesian,(no_)use_nl,以及use_nl_with_index。
       并行处理提示：(控制如何使用并行处理)：(no_)parallel，(no_)parallel_index，pq_distribute，以及(no_)px_join_filter。
       其他提示： (no_)append，(no_)cache，driving_site，model_min_analysis，(no_)monitor以及qb_name。

       初始化参数提示对整个SQL语句都有效。所有其他的提示仅仅对单个查询块起作用。仅仅对单个查询块起作用的提示，必须在它控制的查询块内之制定。
例如，你相对下面的查询中2个表都制定访问路径提示，则一个提示必须被加到主查询上，另一个要加到子查询上。

 

    改变执行环境
        执行环境主要是：会话级和SQL语句级。从oracle 10g起，有2张动态性能视图提供关于执行环境的信息：

       v$sys_optimizer_env提供实例级执行环境的信息。
      v$ses_optimizer_env提供会话级执行环境的信息。
      v$sql_optimizer_env提供当前库缓存中每个子游标的执行环境信息。

 

    SQL概要
    查询优化器被强制必须以最快的速度产生执行计划，典型时间在秒级内，与此不同的是，自动调整优化器可以花较长的时间来产生一个高效的执行计划。
概念：SQL概要是一个对象，它包含可以帮助查询优化器为一个特定的SQL语句整改带高效的执行计划的信息。这些信息包括执行环境，对象统计和对查询优化器所做评估的修正信息。SQL概要最大的有点之一就是在不修改SQL语句和会话执行环境的情况下影响查询优化器的决定。

下图为生成SQL概要的主要步骤，执行SQL语句时主要步骤。

 

 

--dbms_output.put_line();
  
     SQL概要的主要优点之一是，虽然它应用在一个特定的SQL语句上，但并不需要为此而改动SQL语句本身。事实上，SQL概要是存储在数据字典中的，查询优化器会自动选择它们。首先，将SQL 语句标准化，使其不仅不区分大小写而且也不受空白的影响。为结果SQL语句计算一个签名( signature ）。然后，基于此签名查询数据字典。只要发现有相同签名的SQL概要，就检查它以确定要优化的SQL语句和此SQL概要所指的SQL语句是否相同。这一步很必要，因为鉴名是一个哈希值，因此，有可能发生哈希冲突。如果检测是成功的，这个SQL概要将被收入执行计划的生成过程中。
       如果SQL语句包含的字面量发生改变，那么作为哈希值的签名也可能随之改变。因此，SQL概要会失效，因为它依赖于一条非常特定的SQL语句，而这条语句可能再也不会执行。从Orade 10gR2 起，为了避免这个问题，数据库引擎能够在标准化期问排除字面量的影响。这需要在接受SQL概要时将参数force＿match设置为true。
      要探索文本标准化的工作机制，可以使用包dbms_sqltune的函数sqltext_to_signature。此函数要输入两个参数，sql_text和force＿match。前者指定SQL语句，后者指定使用的文本标准化的类型。下面是脚本sqltext_to_signature.sql 输出的摘要，展示了参数force_match对有差异但相似的SQL语句生成签名的影响。

 force_match设置为false。：不区分空表和大小写。

 

 force_match设置为 true：空白、大小写以及字面量均不区分。不过，如果SQL语句中出现绑定变量，就不能消除字面量的影响。

 

   存储提纲
      存储提纲被设计用来提供稳定的执行计划，以消除执行环境或者对象统计信息的改变造成的影响。因此，这个特性也被称作计划稳定性。具体的讲，存储提纲是一个提示的集合，更精确地说，所有这些提示强制查询优化器为一个给定的SQL语句，稳定地产生一个特殊的执行计划。但实践中，遗憾的是，即使使用存储提纲，还是可能观察到执行计划的改变。存储提纲不是总能提供一个稳定的执行计划，Oracle 11g自身就证实了这一点，从这个版本起，不再赞成使用存储提纲，而是推荐SQL计划基线。

 

   SQL计划基线
      可以认为SQL计划基线是存储提纲的一个改进版本，事实上，SQL 计划基线不仅和存储提纲有许多相同的特性，而且也和存储提纲一样被设计用来提供稳定的执行计划，以防执行环境和对象统计信息的改变对执行计划产生影响。此外，和存储提纲类似，‘臼也可以在不修改语句的情况下调优应用程序。注意在Orade 文档中，维持执行计划的稳定性是SQL 计划基线唯一被提及的用途．由于某些未知的原因，对于也可以在不修改应用程序的情况下使用它来更改当前执行计划（涉及一条给定SQL 语句）的用法，并未提及。
    SQL 计划基线是什么
     SQL计划基线是一个与SQL语句相关联的对象，它被设计用来影响查询优化器产生执行计划时的决定。具体地讲，SQL计划基线主要是一个提示的集合。基本上，SQL计划基线就是用来迫使查询优化器为一条给定的SQL语句产生个特定的、稳定的执行计划。
SQL计划基线的优点之一是它应用到一条特定的SQL语句，但在使用它的时候，SQL语句自身不需要进行修改。事实上，SQL计划基线存储在数据字典中，并且查询优化器会自动选择它们．首先，SQL语句以传统的方法执行换句话说，就是要杳询优化器在没有SQL计划基线支持的条件下产生执行计划。然后，对SQL语句进行标准化，使其不区分大小写而且不受空白的影响。为标准化后的SQL语句计算生成一个签名。然后，基于此签名查询数据字典。只要发现可接受的（信任的）并且又有相同签名的SQL计划基线可用，就检查它以确定要优化的SQL语句和此SQL计划基线所指的SQL语句是否一致。这一步十分必要，因为签名是一个哈希值，因此，有可能会发生哈希冲突。如果检测是成功的，SQL计划基线中的提示将被放入执行计划的生成过程里。注意：如果有多个可用的SQL计划基线，查询优化器会选择代价最小的那个。

捕获SQL计划基线
    有多种方法可用来捕获SQL计划基线。基本上，它们都是由数据库引擎自动创建或数据库管理员手动创建。下面的3 小节将分别介绍这3 种主要方法。
     自动捕获
     当动态初始化参数optlmlzer_capture_sql_plan_baselines设置为true的时候，查询优化器将自动创建一个新的SQL计划基线。这个初始化参数被默认设置为FALSE，可以在系统级和会话级修改它。当自动捕获开启后，查询优化器为每条重复执行过（就是至少执行过两次）的SQL 语句存储一个新的SQL 计划基线。为此．它会将每条SQL 语句的签名插入一个日志中，以便于管理。这意味着当一条SQL语句第一次执行的时候，仅把它的签名插入日志。然后，当第二次执行相同的语句的时候，如果不存在与此语句相对应的SQL计划基线，就新建一个并存储起来。如果与SQL语句相对应的SQL计划基线已经存在，查询优化器仍然会对比当前的执行计划和基于此SQL计划基线的执行计划。如果它们不匹配，那么这个描述当前执行计划的新的SQL计划基线将被存储。然而就像你在前面见到的，不能直接使用当前的执行计划。查询优化器被强制使用在SQL 计划基线的辅助下产生的执行计划。