CBO Cost estimation

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2010-03-08 03:22:28

Share to 

There are 2 kind of CPU mode in CBO, IO & CPU. In IO mode, only IO cost will be calcualted, while in CPU mode, IO and CPU Cycles of processing blocks and rows will both be converted to the same unit, CPU time of one single block, and then be combined together.       To convert the IO, optimizer need the system statistics data:     sreadtim - single block read time     mreadtim - multi block read time     cpuspeed - CPU cycles per second       Then use below formula to calculate the IO cost.     IOCost = (         #SRds * sreadtim +         #MRds * mreadtim +       ) / sreadtim     The formula of converting the CPUCycles looks like more simple.     CPUCost = #CPUCycles / cpuspeed / sreadtim       However, how can I get the #CPUCycles? We should first understand what is the CPUCycles. In general, It's the CPU cycles that oracle needs to process the data, for example, to compare the colmun value with the filter value. For different access path, it will contain different parts. We will disscuss the Full Table Scan and Index Scan.   Full Table Scan     First, look into the Full Table Scan with filter. Take below sql as example.   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 )        To get the data via full table scan, four steps required.       Process the blocks be read;       Process the rows from blocks;       Predicate the rows with filter;       Projcet the columns to be queried.        They are the sources of cpu cycles. Since we get the formula to calculate number of cpu cycles.   #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)     While, in full tables scan, all of blocks under hwm will be reads, and all of the rows will be processed. The formula become simple.   #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)     Base on this, I built a simple estimator to calculate the cost of an execution plan.   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.        The caculated result is exactly same as the real result calculated by optimizer.   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    Index Scan       Here I will just discuss the index scan, kick the "Table Access By Index Rowid" out.       To scan an index, it involves 2 parts of processing, Index Blocks & Index Rows. The formula is,   #CPUCYCLES = (Index Blocks to be read)*(cpu cycles to process 1 index block) + (Index Rows to be processed)*(cpu cycles to process 1 row)     As we know, a normal index is a B*Tree structure. Unlike Full Table Scan, the real be proccedd blocks&rows are the ones matched the filter predication. For blocks, it's number of blevel plus with number leaf blocks be selected. For rows, its the number of rows be selected.       Base on this, anther estimator for index scan comes out.   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.        Compare to its actual cost calculated by oracle optimizer.   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 ---