品悟性能优化学习笔记一

第四章 索引

4.1.1函数索引
基于成本CBO模式，参数query_rewrite_enabled设置为true，就可以启动函数索引。

如下时创建函数索引的例子：

create index idx_1 on DZ_SJ(to_char(JDRQ,'YYYY.MM.DD'));

但是函数索引需要维护：每次数据库进行该表的DMl操作时，Oracle都需要维护函数索引，既进行一次计算，维护成本高于普通索引。
函数索引的计算值可能大于原字段值，消耗更多的索引存储空间。

4.1.2复合索引：

      符合索引的两个重要的原则：前缀性和可选性

Oracle索引，包括复合索引都是排序的。Oracle不是智能的，它只会按图索骥，该索引结构先按省排序，只要给出省名，就能使用索引。如果没有省名，Oracke就成了无头苍蝇，乱找一气，变成了全表扫描。
参数skip scan index可以实现符合索引第一个字段没有出现在语句中，Oracke也会使用符合索引。

     复合索引的可选性
       复合索引中如何排列字段顺序？有什么规则？这就是复合索引第二个规则：可选性（Selectivity）规则。Oracle建议按字段可选性高低进行排列，即字段      值越多的排在前面。例如，(ENAME, JOB, MGR，GENDER)，（县、市、省）。这是因为，字段值越多，可选性越强，定位的记录越少，查询效率越高。例如全国     可能就一个“开福区”，而湖南省的记录则太多了。

    复合索引设计建议
   现在可以给出是否建单字段索引还是复合索引，以及复合索引的设计建议了。
    1. 分析SQL语句中的约束条件字段
    2. 如果约束条件字段比较固定，则优先考虑创建针对多字段的普通B*树复合索引。例如同时涉及到月份、纳税人识别号、税务机关代码三个字段的条件，则可以考虑建立一个复合索引
    3. 如果单个字段是主键或唯一字段，或者可选性非常高的字段，尽管约束条件字段比较固定，也不一定要建成复合索引，可建成单字段索引，降低复合索引开销。
    4. 在复合索引设计中，需首先考虑复合索引第一个设计原则：复合索引的前缀性（prefixing）。即SQL语句中，只有复合索引的第一个字段作为约束条件，该复合索引才会启用。
    5. 在复合索引设计中，其次应考虑复合索引的可选性（Selectivity或Cardinality）。即按可选性高低，进行复合索引字段的排序。例如上述索引的的字段排列顺序为：纳税人识别号、税务机关代码、月份。
    6. 如果条件涉及的字段不固定，组合比较灵活，则分别为月份、纳税人识别号、税务机关代码三个字段建立索引 ?
    7. 如果是多表连接SQL语句，注意是否可以在被驱动表（drived table）的连接字段与该表的其它约束条件字段上，创建复合索引。

4.1.3索引监控分析以及优化：索引碎片以及是否使用
      为什么索引I/O那么高？
在主要等待事件中，‘db files sequential read’ 比较高，这说明索引的读写操作比较多，已经成为系统的主要瓶颈。
1）应用软件质量较高，大量的sql语句均采用了索引
2）可能一些索引没用上，DMl操作，导致索引的维护工作量增加，即产生了大量不必要的索引I/O.
3) 由于频繁的Dml操作，可能导致和多的索引碎片，增加了索引I/O开销。
4）部分索引不合理，所用使用了索引，但实际上是索引扫描类似于全表扫描。

4.1.4如何发现多余的索引：
 1.跟据原理去判断
    跟据复合索引的前缀性以及可选性2大原理，去分析这张表各字段的记录分布情况，再结合索引对应的字段情况。
 2.利用oracle索引监控特性
    在典型的业务周期开始前，执行如下的命令，开启对指定索引的监控
          sql>alter index <索引名> monitoring usage;
          在典型业务周期结束后执行如下的命令,结束监控；
         sql>alter index <索引名> nomonitoring usage;
         查询如下视图，就可以知道该索引在业务周期有没有使用：
        sql>select * from v$object_usage;

  4.1.5如何进行索引碎片分析以及整理
     频繁对索引字段进行删除以及更新操作，会对索引造成大量碎片，从而极大地影响索引的使用效率，并造成索引I/O的增加。
     1.索引碎片分析
            执行如下语句可监控索引的碎片情况：
           analyse index <索引名> validate structure online;
           select name，del_lf_rows_len，lf_rows_len,(del_lf_rows_len/if_rows_len)*100 from index_stats;
           表中：索引碎片率（%）=(del_lf_rows_len/if_rows_len)*100。
           如果索引率超过20%，怎oracle认为索引碎片已经非常严重。
      2.索引碎片整理
         1.重建索引（rebuild）
                 sql>alter index <索引名> rebuild;
          2.压缩索引（coalesce）
                 sql>alter index <索引名> coalesce;
      建议采取定期索引重建的策略，例如在每个周末或者每个晚上对删除操作频繁表的索引进行在线重建工作。

 

第五章 为应用软件设计更好的性能和可扩展性
5.1.1 下面是sql语句的主要执行过程：
       1.Parse阶段
        在该阶段，Orade 将首先在SHARE POOL 中搜索该语句，即判断该语句是否已经被分析和执行过，如果没有发现该语句，则需要检查该语句的语法、检查该语句的语义及访问权限、对视图定义和子查询进行转换操作，并最终确定该语句优化的执行计划，这个完整的分析过程叫硬解析（Hard Parse ）。如果在SHARE POOL 中发现了该语句，说明该语句已经被分析和执行过，则只需要检查该语句的语义及访问权限，而其他大部分工作则无需再进行了。这个过程叫软解析（soft Parse ）。可见硬解析比软解析的资源消耗大多了。
   2 . Bind 阶段
    在该阶段，当SQL 语句含有变量（BINO 变量）时，Oracle 通过赋值或传参等方式为这些BIND 变量赋值。
   3 . Execute 阶段
     在该阶段，oracle将实施Parse阶段确定的执行计划，开始执行DML语句，实施I/O及排序操作等。如果是DDL、DML 操作，完成此阶段，则执行过程结束。
   4.Tetch阶段
    在该阶段只适合于select操作，即进行查询记录的读取并对查询结果进行排序。为提高性能，oracle建议以数组方式成批提取记录，降低服务器和客户端的网络传输次数。

5.2 语句共享原理

 IT系统可以分为联机事务处理系统（OLTP）和决策支持系统（DSS或OLAP）。
OLTP系统具有单笔查询的资源消耗小，并发量高等特征。针对这些系统，应以系统的响应速度作为首要的优化目标。由于OLTP中单个语句的执行性能比较容易优化，因此从sql语句的4个阶段分析，应该尽量减少语句的Parce次数。
 而DSS系统具有单笔事务的资源消耗大，并发量不高等特征。针对这些系统，应该以系统整体吞吐量作为优化目标。从SQL语句的4个阶段分析，DSS中sql语句的资源消耗主要在execute和fetch阶段，因此应该保证这类应用语句执行路径的最优化，从而达到语句执行的高效率目的。

  5.2.1如何实现语句共享化
    SQL语句共享性主要针对OLTP应用，由于OLTP应用语句本身相对简单，通过简单的索引策略，即便语句中包含绑定变量，一般都能保证语句的最优化，但这些语句并发量非常高，因此减少这些语句重复Parse次数，是保证OLTP应用性能的重要方面。在技术上称之为提高SQL语句共享性。
可以采用的主要技术手段如下。
    1.应用级绑定化处理
    例如：   

  loop
        cursor cur;
        number eno:=<some value>;
        parse(cur,"select * from emp where empno=:x");
        bind(cur,":x",eno);
        execute(cur);
        fetch(cur);
        close(cur);
    end loop;

  这样，针对不同的员工信息，Oracle就只对“select * from emp where empno=:x”做一次硬解析了。
     2.系统级bind化处理
  通过将Oracle初始化参数cursor_sharing有默认的exact修改为similar/force，将原来语句的常量修改为系统bind变量SYS_B_0,SYS_B_1...，从而实现语句共享性目的。

SQL> alter system set cursor_sharing-'SIMILIAR';
“select * from emp where empno=:SYS_B_0”

其中similar和force的差异是：当设置为force时，oracel强制进行绑定化处理，而不管语句的执行疾患是否合理。而设置为similar时，Oracle会判断进行的绑定化处理之后，其执行计划是否合理。