CBO代价估算

[English]

作者： fuyuncat

来源： www.HelloDBA.com

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

分享到 

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代码 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代码 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<   :6 AND ( OBJECT_NAME=:3 OR OBJECT_NAME= :4 AND OWNER= :7 )');       --==========================System Infromation Begin==========================--    IOSEEKTIM:10; IOTFRSPEED:4096; CPUSPEED:983; MBRC:16    --==========================System Infromation End==========================--       --==========================Object Infromation Begin==========================--    TABROWS:47582; TABBLKS:680; TABBLKSIZ:8192; MAXFLTCOLPOS:4; EFFQRYCOLNUM:9    --==========================Object Infromation End==========================--       --==========================Estimiation Begin==========================--    SRDS:0; MRDS:43; TTIOSEEKTIM:430; TTIOTFRTIM:1360    SREADTIM:12; MREADTIM:42; IOSEEKCOST:36; IOTFRCOST:114    IOCOST:150       Filter Selective:0.000042130162399090975928517545913890070793; Cardinality:2    CPUCYCLES:31619454.19999097838751420809348373586745      CYCLES Processing blocks: 4842579.2      CYCLES Processing rows:6185660      CYCLES Predication:3806560      CYCLES projecting columns:16784294.99999097838751420809348373586745    CPUCOST:2.68052341471608836788014649826074396977       FINAL_COST:153    --==========================Estimiation End==========================--       PL/SQL procedure successfully completed.        对比Oracle优化器对该查询计划计算所得的代价，结果完全正确：   SQL代码 HELLODBA.COM>select operation, options, cardinality, cost, cpu_cost, io_cost      2  from plan_table where statement_id = 'Test';       OPERATION           OPTIONS    CARDINALITY  COST       CPU_COST   IO_COST    ------------------ ----------- ------------ ---------- ---------- ----------    SELECT STATEMENT                          2        153   31619454        150    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代码 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   where LAST_DDL_TIME = :1 and OBJECT_NAME > :2');       --==========================System Infromation Begin==========================--    IOSEEKTIM:10; IOTFRSPEED:4096; CPUSPEED:983    MBRC:16; OPTSIZ:8192; OPTICA:60    --==========================System Infromation End==========================--       --==========================Object Infromation Begin==========================--    TABROWS:47582; TABBLKS:680; TABBLKSIZ:8192    INDROWS:47582; BLEVEL:2; LFBLKS:322; INDBLKSIZ:8192    --==========================Object Infromation End==========================--       --==========================Estimiation Begin==========================--    Table Selective:.0001412429378531075; Index Selective:.00002542372881355935    Cardinality:7    SRDS:3; MRDS:0; TTIOSEEKTIM:30; TTIOTFRTIM:.0163728813559322214    IOCOST:1.8    CPUCYCLES:13059      CYCLES Processing blocks: 12819      CYCLES Processing rows:240    CPUCOST:.001107070193285859613428280773143438453717    FINAL_COST:2    --==========================Estimiation End==========================--       PL/SQL procedure successfully completed.        同样，对比实际结果，完全正确。   SQL代码 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    DEMO.T_TEST1 t where LAST_DDL_TIME = :1 and OBJECT_NAME > :2;       Explained.       HELLODBA.COM>select operation, options, cardinality, cost, cpu_cost, io_cost      2  from plan_table where statement_id = 'Test';       OPERATION           OPTIONS    CARDINALITY  COST       CPU_COST   IO_COST    ------------------ ----------- ------------ ---------- ---------- ----------    SELECT STATEMENT                          7          2      13059          2    INDEX              RANGE SCAN             7          2      13059          2    --- Fuyuncat ---