HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

CBO Cost estimation

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

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

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

    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代码
  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 )   

    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代码
  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.   

    The caculated result is exactly same as the real result calculated by optimizer.
 

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   

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代码
  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.   

    Compare to its actual cost calculated by oracle optimizer.
 

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 All reseverd.

Declaration
by fuyuncat