执行计划(CBO)

原创 2006年06月05日 17:28:00

导致不使用CBO而使用RBO的三个原因:
1.optimizer_mode设置为rule或optimizer_goal设置为rule
2.查询中包含有rule提示.
3.查询的所有表都没有被分析过,并且查询不包含任何提示.

RBO的工作方式:
    RBO在决定SQL的执行计划时,只使用少量信息:
        SQL文本.
        SQL的FROM后面的基本信息,如表,簇,视图,参考的其他字段的数据类型.
        与SQL相关表上的索引.
        数据字典信息只对本地数据库有用. 如参考远程数据库,数据字典信息对RBO无效.
    簇的缺点超过它的优点.
    很少在应用中使用簇.    
   
结论:
    RBO只考虑少量可能的访问方式.
    对RBO来说,索引总是好的,即使有的时候索引不一定好.
    RBO经常使用没有意义的窍门来解释SQL,为什么FROM后面的顺序会与次相关?!很多人借解释了CBO的不稳定性,但是有多少次索引重建后RBO的执行计划因此改变了呢.
    100%的命中率意味着没有逻辑IO导致物理IO,这清楚地解释了一点:缓存命中率对优化执行计划没有什么意义. 减少执行,减少逻辑IO的数量才是更好的目标.

    Oracle最后不得不抛弃IO. 自从介绍RBO到CBO出现并很好地应用于生产系统,RBO运行了7年之久.

   
CBO的工作原理:
    CBO是一个数学处理器,它使用公式去计算SQL的成本. 成本本质上被转换为物理IO,逻辑IO在Oracle的SGA的Buffer Cache中被完全处理.
    CBO使用更多的可能的访问方式,它计算每个可能的访问方式的逻辑IO的数量,然后简单地选择最低成本的一个执行方式.
    RBO是线性的设计,一步步地做决定,先最决定,然后下个决定以该决定为基础,初始决定对最终的结果影响很大.

  
什么情况下可能出错?
    CBO只是选择执行计划中成本最低的一种,至少两种情况可能会出错:
    1.可能接收到错误的数据,
    2.一个或多个公式没有包括重要的因子或是错误的.
    这两个问题贯穿Oracle7始终.

    Oracle7中,analyze命令存在很多BUG,到了Oracle8中,很多BUG被修复了. Oracle 8i中DBMS_stats包比analyze命令更好的统计信息. 剩下的就是对CBO使用的计算成本的公式进行细微的修正了.

Cost是什么意思?
    CBO计算的成本主要由物理IO组成. 实际的公式:IO+CPU/1000+NetIO*1.5
    该公式中,IO表示物理IO请求, CPU描述逻辑IO请求, NetIO描述通过数据库连接到远程数据库的逻辑IO请求. 这以为着物理IO是最"昂贵"的组成部分. 从中也可以看出,分布式数据库应用会有更高的COST. 逻辑IO和物理IO的区别是什么?  前者是对缓存在Oracle SGA的Buffer Cache中的数据块的访问.  后者是从Oracle系统中取得的不缓存在Buffer Cache的数据块的请求数.  CBO计算所有可能的执行计划的物理IO的请求数. 排除不是最低的执行计划.
   
Oracle IO规则:
    Oracle不访问行,而是访问包含行的数据块, 不是去访问磁盘,而是首先去查Oracle SGA中的Buffer Cache内存中的块(逻辑读). 逻辑读:consistent gets和db block gets(current gets).  一致读是提取的特定版本或时间点的SQL数据块, db block gets是当前的时间点提取的数据块,一般是修改数据如插入、更新、删除。
    v$sysstat或v$sesstat中不会有“logical read”,只有“consistent read”和"db block gets" ,将两者相加就是了。
    如果在Buffer Cache中找到了数据块,则系统就使用找到的数据块,如果没有找到,系统执行物理读,将块读到Buffer Cache中。
    如果一个服务器进程正在执行一个物理读,而另一个进程执行逻辑读时,没有发现缓存中有数据块,那么该进程也会去进行一个物理读吗?不,第一个进程在发现数据块没有在缓存中时,在实际执行物理读之前会预先分配一个缓冲器给该块,然后锁定缓冲器并发送物理IO请求,第二个进程试图进行逻辑读,而缓冲器被锁定,则会POST一个"buffer busy wait"的等待事件并等待该块可用,减少没有必要的IO。
   

在cache buffers chain上工作
    Buffer Cache是一个填满缓冲块的高速缓冲存储器,由LRU(least recently used)运
算法则管理,使用该法则,最经常被访问的缓冲块被放在链表的MRU(most recently used)端, 新读到缓冲存储器中的块被放置在MRU端,逻辑读时,他们被移到MRU列表一端,如果块没有被访问,它就会逐渐地向LRU端移动。当它达到LRU端末的时候,新读到的块就会覆盖该缓冲块,该块也就掉离列表的LRU.  Oracle8i中使用了中点插入算法来减少因块移出列表特别是“热”块移动到列表的MRU端的latch处理。

两类IO:
    Oracle读数据文件有两中类型的IO:
    1.单块,随机访问读取,通常是与索引访问有关的(该IO请求发生时,产生等待事件db file sequential read).
    2.多块,顺序访问读取,通常与全表扫描访问有关(该IO请求发生时,将产生db file scattered read等待事件),但是可能与排序和并行查询(等待事件:direct path read).
    这两种IO类型规则十分相似,进程需要数据块时,首先检查Buffer Cache,如果块存在,就使用它,并且把它移动到LRU链的MRU端。如果块不存在,就从磁盘上的数据文件读取放到缓冲区中,并将它防止到LRU链的MRU端。
    不可避免地,每个新读到的块都将被放置到LRU链的MRU端,当前面的块被LRU链的LRU端时,最后被推出LRU链或被覆盖。全表扫描时,需要扫描的块大于Buffer Cache时,将完全刷新Buffer Cache的内容。

Oracle中的缓存IO:
    Oracle7中一个改变来防止这种缓冲区被刷新的情况(cache flushing effect), 首先,多块顺序访问的全表扫描的处理和LRU的算法是不同的,当全表扫描时,读到的块将被防止到LRU链的LRU端. 索引扫描的单块读没有改变,继续被放置在LRU链的MRU端. 全表扫描的数据块计划会立刻被覆盖,除非这些块很快被再次访问(将被移动到MRU端).
    全表扫描的逻辑读一般很在Buffer Cache中找到需要的块,因此,全表扫描总是表现为大量的物理读.  但是被全表扫描重新访问的块有多块的频率? 大多数情况下很少. 所以Oracle7的设计者公认地允许块很快被覆盖,同时保留下Buffer Cache的原来真实内容.
    但是小表没有必要每次物理读,于是他们设计Oracle7时将小表的全表扫描块防止在LRU链的MRU端,作为特例,加速对小表全表扫描的访问.  参数SMALL_TABLE_THESHOLD存储了可以使用的数据块数, 默认db_block_buffers的2%. Oracle7前没有该参数.  有人将该参数设置过大,已存储更多的小表, 但是这样将导致buffer cache可用变小,反问容易出现不希望出现的cache flush.

Oracle7 + 版本中的IO:
    small_table_threshold已经被设置为隐含参数_small_table_threshold.考虑到开发者可能会将某表驻留在Buffer Cache中, 所以在建表或修改表的语句中设置了cache和nocache关键字, 设置为cache的表块将被防止在LRU链的MRU端.
    考虑到可能有大量的表缓存,所以Oracle设置一个参数:CACHE_SIZE_THRESHOLD,超过该参数设置的数量的数据块数的表,将不能被cache到buffer中. 该参数默认Buffer Cache的大小的10%,可以根据需要进行更改.

    索引扫描的块总是放在LRU链的MRU端. 标记为cache的表并且块要比cache_size_threshold参数设置的小时,该表会处理LRU链的MRU端. 全表扫描的大表块将被在LRU链的LRU端.

    全表扫描的几乎每次逻辑读都将导致物理读,这是正常的,也是有利的. 全表扫描通常Buffer Cache的命中率只有20%设置更少,常是1-5%. 意味着除了少数逻辑读外,都需要物理读. 索引扫描通常表现膸很高的Buffer Cache命中率95%或以上.


OPTIMIZER_INDEX_COST_ADJ的调整
    系统运行一段时间后(需要将参数timed_statistics设置为TRUE),查询v$system_event,db file sequential read和db file scattered read事件的average_wait字段分别显示的是索引扫描和全表扫描的平均等待时间,单位为1/100秒.
可以将OPTIMIZER_INDEX_COST_ADJ 参数设置为(前者/后者)*100
   

相关文章推荐

Mysql 执行计划解读

  • 2017年10月12日 14:07
  • 812KB
  • 下载

执行计划编程

  • 2015年10月14日 16:37
  • 21KB
  • 下载

如何看懂Postgres的执行计划

如何看懂Postgres的执行计划 test=# insert into test select id from (select generate_series(1,10000000))ids(id...

综测环保网络营销执行计划

  • 2015年05月05日 14:51
  • 785KB
  • 下载

mysql文档执行计划

  • 2013年06月21日 13:31
  • 529KB
  • 下载

DB查询分析器7.01新增的周、月SQL执行计划功能

本文将以中文版本DB 查询分析器7.01版本为例,详细介绍 新增的强大的周和月的“SQL 执行计划”、执行的多条SQL语句返回的记录集保存到多个文件中、执行结果保存文件时用户自定义导出文件中的字段间分...

ORACLE执行计划和SQL调优

  • 2013年10月17日 15:12
  • 164KB
  • 下载

SQL执行计划实验

  • 2014年05月23日 20:00
  • 104KB
  • 下载

hive sql执行计划树解析

如下两个sql语句,其实执行时间并没有什么差别,两者执行计划近乎相同,其中查询1抒写更方便 hql1: hive -e "use acorn_3g;explain select t.app_id,co...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:执行计划(CBO)
举报原因:
原因补充:

(最多只允许输入30个字)