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

# 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:
cpuspeed - CPU cycles per second

Then use below formula to calculate the IO cost.
IOCost = (

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
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 ---

 Previous：NULL IS NOT NULL in execution plan Next：Adjusted MBRC formual in CBO