《基于ORACLE的SQL优化》总结篇

王硕

这本书比较出名，很多人都在找，那么我想把我看到的总结下吧：
目录加链接如下（未完待续，持续更新）：

王硕

第一章： ORACLE里面的优化器
优化器（Optimizer），
分为：      
                 RBO（Rule-Based-Optimizer)  基于规则的优化器
                 CBO（Cost-Based-Optimizer）基于成本的优化器

第一部分，基于规则的优化器
RBO，通过硬编码在ORACLE数据库代码中的一系列固定的规则，来执行目标SQL的执行计划。

分15个等级，并认为等级1所对应的执行路径执行效率最高。通过rowid来访问效率最高（single row by rowid），全表扫描效率最低( full table scan)。

适用于OLTP类型。

10g开始，RBO已不再被ORACLE支持，但RBO的相关实现代码并没有被移除。这意味着，在11gR2中，我们依然可以通过修改优化器模式，或者使用RULE HINT 来继续使用RBO。

但出现如下情形，会强制走CBO，而不能使用RBO，即使你修改了优化器模式，或者使用了RULE HINT ：
                              目标SQL中设计的对象有IOT（Index Organized Table）
                              目标SQL中涉及的对象有分区表
                              使用了并行查询，或者并行DML
                              使用了星型连接
                              使用了哈希连接
                              使用了索引快速扫描
                              使用了函数索引
                              ......

RBO 如果不是最佳执行计划，这种情况很难做调整，因为不能使用Hint，因为使用hint就等于启动了CBO。（两个例外：RULE HINT和DRIVING_SITE_Hint） 可行的办法就是改写等价SQL：
                               比如，在SQL里的where条件 对NUMBER或DATE 类型 的类加上 0，对VARCHAR2 ， CHAR类型加一个空格符 ||
                                          让原本可以走的索引走不了（列如：select * from emp where mgr>100 and deptno+0>100;）
                               还可以改变表连接的顺序，进而调整执行计划。                             
        如果SQL认为两个执行计划出现两个及以上的同等级的执行路径，RBO会依据相关对象在数据字典缓存（Data Dictionary Cache） 中的缓存顺序判 断


开启RULE HINT 强制使用RBO：    alter  session  set optimizer_mode= 'RULE';

启动跟踪：   set autotrace traceonly explain
   Note
-----
   - rule based optimizer used (consider using cbo)
   表示正在使用RBO，推荐使用CBO。


       ---严格意义说，排序合并连接并没有驱动表和被驱动表。

王硕

第二部分，基于成本的优化器

RBO可能会选择错误的执行计划，因为没有考虑目标SQL中所涉及的对象的实际数据量、实际数据分布等情况。

从7开始，引入CBO。成本是指ORACLE根据相关对象的统计信息计算出来一个值，它实际上代表了ORACLE根据相关统计信息估算出来的目标SQL的对应执行步骤 的I/O ,CPU 和网络资源的消耗量。

集的势（Cardinality） ，集合所包含的记录数，也就是结果集的行数。也就是针对整个目标SQL，此时Cardinality对该SQL最终执行结果所包含记录数的估算。某个执行步骤所对应的Cardinality值越大，那么其成本往往越大。

可选择率（Selectivity），简单说就是where后选择出来的数据占所有查询数据的百分比。

设置一个列为非空：  alter table emp modify (mgr not null);
创建索引： create index idx_emp_mgr on emp(mgr);

使用DBMS_STATS包来对 表EMP、表EMP的所有的列、表EMP上的所有索引做统计信息：
      exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'EMP',estimate_percent=>100,cascade => true,method_opt =>'for all columns size 1',no_invalidate => false);
   
讲索引IDX_EMP_MGR对应其索引页数据的统计信息修改为10万：
      exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'IDX_EMP_MGR',numlblks=>100000,no_invalidate=>false);


可传递性（Transitivity）
利用可传递性对目标SQL做简单的等价改写仅仅试用于CBO。
            1. 简单的谓语传递
                  t1.c1=t2.c1 and t1.c1=10    等价于  t1.c1=t2.c1 and t1.c1=10 and t2.c1=10
            2. 连接谓语传递
                  t1.c1=t2.c1 adn t2.c1=t3.c1  等价于  t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1
            3. 外连接谓语传递
                  t1.c1=t2.c1(+) and t1.c1=10  等价于  t1.c1=t2.c1(+) and t1.c1=10 and t2.c1(+)=10



CBO 的局限性：
            1. CBO默认SQL语句WHERE条件中出现的各个列之间是独立的，没有关联：（动态采样，和多列统计信息两个解决方案也不能完美解决问题）

            2. CBO会假设所有SQL都是单独执行的，并且互不干扰：（目标SQL执行所用到的 索引叶子块、数据块，可能已经被缓存在了Buffer Cache 中， 不需要重新获取相关信息）
            
            3. CBO对直方图统计有诸多限制：
                     （1）12C 以前Frequency 类型的直方图对应的Bucket的数量不能超过254，如果超过就会使用Height Balanced 类型的直方图。Height Balanced类型的直方图不会记录所有的 nonpopular value值，可能会执行错误的执行计划。
                      （2）针对文本型的字段收集直方图统计，会去取文本值得头32个字节（实际是是15个字节），并将其换成一个浮点数并存储在直方图统计中，对于超过32 字节的字段，只要前32位相同，就会默认为后面的文本值也相同，并作出错误的判断。

           4. CBO在解析多变关联的SQL，可能会漏选正确的执行计划: 多表关联会出现几何级数的连接方法，在11gR2中受隐含参数              

                                                                                    _optimizer_max_permutations限制，只会考虑下面的2000 的值得连接方式。
                       SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
                                 from x$ksppi a, x$ksppcv b
                                 where a.indx = b.indx and a.ksppinm like '_optimizer_max_permutations';


                                NAME                                         VALUE
                                -------------------------------------------------------------------------------- -------------------
                                _optimizer_max_permutations                             2000

            

tc514821

http://pan.baidu.com/s/1dDDbAoh
http://pan.baidu.com/s/1jGo5jnw
网上找到这本书的电子档，两个百度网盘的下载

admin

tc514821 发表于 2015-12-23 12:32
http://pan.baidu.com/s/1dDDbAoh
http://pan.baidu.com/s/1jGo5jnw
网上找到这本书的电子档，两个百度网 ...

赞一个~~~~

王硕

第三部分：优化器的模式

优化器的模式由参数OPTIMIZER_MODE的值决定，
                   有RULE、CHOOSE、FIRST_ROWS_n(n=1,10,100,1000)、FIRST_ROWS或ALL_ROWS

1.RULE
     代表只使用RBO
2.CHOOSE
     从9i的默认值，如果SQL涉及的表对象有统计信息则使用CBO，反之，则使用RBO。
3.FIRST_ROWS_n(n=1,10,100,1000)
     Oracle以最快的响应速度返回头1,10,100,1000条记录。
4.FIRST_ROWS
     9i后已经过时，其兼并CBO，RBO
5.ALL_ROWS
     10g及后续版本的默认值，使用CBO解析目标SQL，侧重于最佳的吞吐量（即最小的系统I/O和CPU资源的消耗量）

王硕

第四部分： 访问数据的方法

1.全表扫描
   从表所在的第一个区（EXTENT）的第一个块开始扫描，一直扫描到表的高水位线（HWM，High Water Mark），所有数据块。
   优点：全表扫描使用多块读，在数据量不大时效率非常高
   缺点：全表扫描的目标SQL执行时间不稳定，不可控，随数据量的递增而递增。
            对于delete 删除的数据，不会降低高水位线，SQL依然会扫描已经删除的空的数据。

2.ROWID扫描

   直接通过数据所在的rowid去定位并访问这些数据。
   ROWID表示Oracle中的数据记录所在的物理存储地址，ROWID与数据块一一对应。
   查询ROWID：
   SQL> col location for a22
SQL> select empno,ename ,rowid,dbms_rowid.rowid_relative_fno(rowid) || '_' ||dbms_rowid.rowid_block_number(rowid)  || '_' || dbms_rowid.rowid_row_number(rowid) location from emp;

     EMPNO ENAME      ROWID              LOCATION
---------- ---------- ------------------ ----------------------
      7369 SMITH      AAAVREAAEAAAACXAAA 4_151_0
      7499 ALLEN      AAAVREAAEAAAACXAAB 4_151_1
      7521 WARD       AAAVREAAEAAAACXAAC 4_151_2
      7566 JONES      AAAVREAAEAAAACXAAD 4_151_3
      7654 MARTIN     AAAVREAAEAAAACXAAE 4_151_4
      7698 BLAKE      AAAVREAAEAAAACXAAF 4_151_5
      7782 CLARK      AAAVREAAEAAAACXAAG 4_151_6
      7788 SCOTT      AAAVREAAEAAAACXAAH 4_151_7
      7839 KING       AAAVREAAEAAAACXAAI 4_151_8
      7844 TURNER     AAAVREAAEAAAACXAAJ 4_151_9
      7876 ADAMS      AAAVREAAEAAAACXAAK 4_151_10
      7900 JAMES      AAAVREAAEAAAACXAAL 4_151_11
      7902 FORD       AAAVREAAEAAAACXAAM 4_151_12
      7934 MILLER     AAAVREAAEAAAACXAAN 4_151_13

14 rows selected      AAAVREAAEAAAACXAAA为伪列， 4_151_0 代表4号文件第151个数据块第0号记录（从0开始记录）

    根据伪列，可以查出具体的表信息
     SQL> select empno,ename  from emp where rowid='AAAVREAAEAAAACXAAA';

     EMPNO ENAME
---------- ----------
      7369 SMITH

      SQL>