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

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 4734|回复: 6
打印 上一主题 下一主题

[范例] 《基于ORACLE的SQL优化》总结篇

[复制链接]
跳转到指定楼层
楼主
发表于 2015-12-22 20:45:23 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
这本书比较出名,很多人都在找,那么我想把我看到的总结下吧:
目录加链接如下(未完待续,持续更新):
分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 支持支持 反对反对
回复

使用道具 举报

沙发
 楼主| 发表于 2015-12-22 20:47:48 | 只看该作者
本帖最后由 王硕 于 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:04 | 只看该作者
本帖最后由 王硕 于 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

            

回复 支持 反对

使用道具 举报

地板
发表于 2015-12-23 12:32:30 | 只看该作者
http://pan.baidu.com/s/1dDDbAoh
http://pan.baidu.com/s/1jGo5jnw
网上找到这本书的电子档,两个百度网盘的下载
回复 支持 反对

使用道具 举报

5#
发表于 2015-12-29 12:56:03 | 只看该作者
tc514821 发表于 2015-12-23 12:32
http://pan.baidu.com/s/1dDDbAoh
http://pan.baidu.com/s/1jGo5jnw
网上找到这本书的电子档,两个百度网 ...

赞一个~~~~
回复 支持 反对

使用道具 举报

6#
 楼主| 发表于 2016-1-19 20:32:43 | 只看该作者
第三部分:优化器的模式

优化器的模式由参数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资源的消耗量)
回复 支持 反对

使用道具 举报

7#
 楼主| 发表于 2016-1-19 22:21:38 | 只看该作者
第四部分: 访问数据的方法

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>

回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2024-11-1 11:39 , Processed in 0.099819 second(s), 19 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表