HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

CBO代价估算

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2010-03-08 03:22:28

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

    CBO有两种模式来估算查询计划的代价:IO和CPU。在IO模式中,代价估算器仅估算IO代价,不考虑CPU代价;而在CPU模式中,估算器会将IO代价和CPU代价都转换为同一单位:读取一个数据块的时间,然后将两部分结果相加,得到最终估算代价。
 

    要完成这一转换,优化器首先需要由系统统计数据计算得到以下几个数据
  sreadtim - 读取单个数据块的时间
  mreadtim - 读取多个数据块的时间
  cpuspeed - CPU每秒钟的转速
 

    以下公式用于计算IO部分的代价.
  IOCost = (
        #SRds * sreadtim +
        #MRds * mreadtim +
      ) / sreadtim

    而计算CPU部分的代价的公式看起来更为简单:
CPUCost = #CPUCycles / cpuspeed / sreadtim

    但是却存在一个问题:如何得到CPU的运转次数(#CPUCycles)呢?这里的CPU运转次数是优化器执行查询计划是所需要消耗的CPU资源,例如读取数据块、按照过滤条件对数据记录进行过滤等等操作。对于不同的访问路径(Access Path),消耗的CPU由不同的部分组成。我们这里讨论两个主要的访问路径:全表扫描(Full Table Scan)和索引扫描(Index Range Scan)。
 

全表扫描

    以下面的查询语句为例,它是一个包含了复杂的过滤条件的全表扫描语句:
 

SQL代码
  1. select /*+full(t)*/* from DEMO.T_TEST1 t where OBJECT_ID = :1 OR OBJECT_ID = :3 OR OBJECT_NAME> :5 AND OBJECT_NAME < :6 AND ( OBJECT_NAME=:3 OR OBJECT_NAME= :4 AND OWNER= :7 )  

    完成这样一次全表扫描,需要经过以下几个步骤:

  •       读取、处理数据块;
  •       处理数据块上的数据记录;
  •       将数据记录与过滤条件进行匹配;
  •       获取匹配成功的记录上所需要查询的字段;

    因此,由这4个操作步骤,就构成了全表扫描下的4个CPU消耗的部分,得到以下公式:
  #CPUCYCLES= (number of blocks to be read from disk)*(cycles to read one block from disk)+(number of rows to be processed)*((cycles to process 1 row)+(number of rows to be predicated)*(cycles to predicate 1 row))+(cycles to project columns)

    而在全表扫描中,HWM以下的所有数据块和数据块上的所有记录都将需要被处理,因此公式可以简化为:
  #CPUCYCLES= (table blocks)*(cycles to read one block from disk)+(table rows)*((cycles to process 1 row)+(table rows)*(cycles to predicate 1 row))+(cycles to project columns)

    按照这样一个公式,我构建了一个简单的估算器对全表扫描进行代价估算:
 

SQL代码
  1. HELLODBA.COM>exec plan_estimator('select /*+full(t)*/* from DEMO.T_TEST1 t where OBJECT_ID = :1 OR OBJECT_ID = :3 OR OBJECT_NAME> :5 AND OBJECT_NAME<  
  2. :6 AND ( OBJECT_NAME=:3 OR OBJECT_NAME= :4 AND OWNER= :7 )');   
  3.   
  4. --==========================System Infromation Begin==========================--   
  5. IOSEEKTIM:10; IOTFRSPEED:4096; CPUSPEED:983; MBRC:16   
  6. --==========================System Infromation End==========================--   
  7.   
  8. --==========================Object Infromation Begin==========================--   
  9. TABROWS:47582; TABBLKS:680; TABBLKSIZ:8192; MAXFLTCOLPOS:4; EFFQRYCOLNUM:9   
  10. --==========================Object Infromation End==========================--   
  11.   
  12. --==========================Estimiation Begin==========================--   
  13. SRDS:0; MRDS:43; TTIOSEEKTIM:430; TTIOTFRTIM:1360   
  14. SREADTIM:12; MREADTIM:42; IOSEEKCOST:36; IOTFRCOST:114   
  15. IOCOST:150   
  16.   
  17. Filter Selective:0.000042130162399090975928517545913890070793; Cardinality:2   
  18. CPUCYCLES:31619454.19999097838751420809348373586745   
  19.   CYCLES Processing blocks: 4842579.2   
  20.   CYCLES Processing rows:6185660   
  21.   CYCLES Predication:3806560   
  22.   CYCLES projecting columns:16784294.99999097838751420809348373586745   
  23. CPUCOST:2.68052341471608836788014649826074396977   
  24.   
  25. FINAL_COST:153   
  26. --==========================Estimiation End==========================--   
  27.   
  28. PL/SQL procedure successfully completed.   

    对比Oracle优化器对该查询计划计算所得的代价,结果完全正确:
 

SQL代码
  1. HELLODBA.COM>select operation, options, cardinality, cost, cpu_cost, io_cost   
  2.   2  from plan_table where statement_id = 'Test';   
  3.   
  4. OPERATION           OPTIONS    CARDINALITY  COST       CPU_COST   IO_COST   
  5. ------------------ ----------- ------------ ---------- ---------- ----------   
  6. SELECT STATEMENT                          2        153   31619454        150   
  7. TABLE ACCESS       FULL                   2        153   31619454        150   

索引扫描

    在这里,我们仅仅考虑索引扫描,不考虑“Table Access By Index Rowid”。
 

    索引扫描的操作组成更为简单,仅包含索引数据块和索引记录的处理,其公式为:
  #CPUCYCLES = (Index Blocks to be read)*(cpu cycles to process 1 index block) + (Index Rows to be processed)*(cpu cycles to process 1 row)

    我们知道,普通索引的结构是B*Tree,因此所需要处理的记录数就是为实际满足匹配条件的索引记录数。不像全表扫描,需要将全部记录对比一遍。而所需要访问的数据块数就由索引高度和查询条件的索引选择性决定。
 

    基于这样的一个分析,可以创建一个简单的索引扫描的代价估算器:
 

SQL代码
  1. HELLODBA.COM>exec plan_estimator_idx('select /*+index(t T_TEST1_IDX4) OPT_PARAM(''_optimizer_cost_model'' ''cpu'')*/LAST_DDL_TIME from DEMO.T_TEST1 t  
  2. where LAST_DDL_TIME = :1 and OBJECT_NAME > :2');   
  3.   
  4. --==========================System Infromation Begin==========================--   
  5. IOSEEKTIM:10; IOTFRSPEED:4096; CPUSPEED:983   
  6. MBRC:16; OPTSIZ:8192; OPTICA:60   
  7. --==========================System Infromation End==========================--   
  8.   
  9. --==========================Object Infromation Begin==========================--   
  10. TABROWS:47582; TABBLKS:680; TABBLKSIZ:8192   
  11. INDROWS:47582; BLEVEL:2; LFBLKS:322; INDBLKSIZ:8192   
  12. --==========================Object Infromation End==========================--   
  13.   
  14. --==========================Estimiation Begin==========================--   
  15. Table Selective:.0001412429378531075; Index Selective:.00002542372881355935   
  16. Cardinality:7   
  17. SRDS:3; MRDS:0; TTIOSEEKTIM:30; TTIOTFRTIM:.0163728813559322214   
  18. IOCOST:1.8   
  19. CPUCYCLES:13059   
  20.   CYCLES Processing blocks: 12819   
  21.   CYCLES Processing rows:240   
  22. CPUCOST:.001107070193285859613428280773143438453717   
  23. FINAL_COST:2   
  24. --==========================Estimiation End==========================--   
  25.   
  26. PL/SQL procedure successfully completed.   

    同样,对比实际结果,完全正确。
 

SQL代码
  1. HELLODBA.COM>explain plan set statement_id='Test' for select /*+index(t T_TEST1_IDX4) OPT_PARAM('_optimizer_cost_model' 'cpu')*/LAST_DDL_TIME from  
  2.  DEMO.T_TEST1 t where LAST_DDL_TIME = :1 and OBJECT_NAME > :2;   
  3.   
  4. Explained.   
  5.   
  6. HELLODBA.COM>select operation, options, cardinality, cost, cpu_cost, io_cost   
  7.   2  from plan_table where statement_id = 'Test';   
  8.   
  9. OPERATION           OPTIONS    CARDINALITY  COST       CPU_COST   IO_COST   
  10. ------------------ ----------- ------------ ---------- ---------- ----------   
  11. SELECT STATEMENT                          7          2      13059          2   
  12. INDEX              RANGE SCAN             7          2      13059          2   

--- Fuyuncat ---
 

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat