重庆思庄Oracle、Redhat认证学习论坛

标题: 《基于ORACLE的SQL优化》总结篇 [打印本页]

作者: 王硕 时间: 2015-12-22 20:45
标题: 《基于ORACLE的SQL优化》总结篇
这本书比较出名，很多人都在找，那么我想把我看到的总结下吧：
目录加链接如下（未完待续，持续更新）：

作者: 王硕 时间: 2015-12-22 20:47
本帖最后由王硕于 2015-12-22 21:49 编辑

第一章： 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。

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

作者: 王硕 时间: 2015-12-22 23:02
本帖最后由王硕于 2015-12-24 15:33 编辑

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

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 时间: 2015-12-23 12:32
http://pan.baidu.com/s/1dDDbAoh
http://pan.baidu.com/s/1jGo5jnw
网上找到这本书的电子档，两个百度网盘的下载

作者: admin 时间: 2015-12-29 12:56

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

赞一个~~~~

作者: 王硕 时间: 2016-1-19 20:32
第三部分：优化器的模式

优化器的模式由参数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资源的消耗量）

作者: 王硕 时间: 2016-1-19 22:21
第四部分：访问数据的方法

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>

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)