Cost Calculation for Full table Scan
How FTS cost depends on system statistics
Gather statistics manually on load:
The following command will start to gather system statistics.
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS (‘START’);
Please wait for system warm up and stop gather system statistics after work load.
This is called workload statistics.
The MBRC will be calculated automatically depending on no. of FTS occurring on database.
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS (‘STOP’);
SQL> select * from aux_stats$;
SNAME PNAME PVAL1
—————————— —————————— ———-
PVAL2
——————————————————————————–
SYSSTATS_INFO STATUS AUTOGATHERING
SYSSTATS_INFO DSTART 01-15-2018 04:44
SYSSTATS_INFO DSTOP 01-15-2018 04:46
SYSSTATS_INFO FLAGS 0
SYSSTATS_TEMP SBLKRDS 46416
SYSSTATS_TEMP SBLKRDTIM 22465.055
SYSSTATS_TEMP MBLKRDS 3864
SYSSTATS_TEMP MBLKRDTIM 4291.249
SYSSTATS_TEMP CPUCYCLES 69720
SYSSTATS_TEMP CPUTIM 23054
SYSSTATS_TEMP JOB 14
SYSSTATS_TEMP CACHE_JOB 16
SYSSTATS_TEMP MBRTOTAL 65755
SYSSTATS_MAIN CPUSPEEDNW 3024
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED 3024
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
Sometimes all values may not be populated because of nature of workload.
Example of FTS Cost calculation (System
statistics gathered on load)
sreadtim : wait time to read single block, in milliseconds .
mreadtim : wait time to read a multi block, in milliseconds.
cpuspeed : cycles per second, in millions.
MRBC : average multi block read count for sequential read, in blocks .
CPU_COST and IO_COST in PLAN_TABLE table after gathering system statistics
on load:
C##TEST@TESTDB1> select count(1) from tab1;
Execution Plan
———————————————————-
Plan hash value: 1117438016
——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 113K (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TAB1 | 1999K| 113K (2)| 00:00:05 |
——————————————————————-
Statistics
———————————————————-
0 recursive calls
0 db block gets
285857 consistent gets
0 physical reads
0 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So CPU cost:-2% of Total COST IO cost:-98% of Total COST.
10053 trace generation :-
SYS@TESTDB1> alter session set events ‘10053 trace name context forever’;
Session altered.
SYS@TESTDB1> select count(1) from c##test.tab1;
COUNT(1)
———-
1999998
SYS@TESTDB1> alter session set events ‘10053 trace name context off’;
Session altered.
Cost parameters in 10053 trace:-
—————————–
SYSTEM STATISTICS INFORMATION
—————————–
Using dictionary system stats.
Using WORKLOAD Stats
CPUSPEED: 2942 millions instructions/sec
SREADTIM: 0.621000 milliseconds
MREADTIM: 0.974000 millisecons
MBRC: 4 blocks
MAXTHR: -1 bytes/sec
SLAVETHR: -1 bytes/sec
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TAB1 Alias: TAB1
#Rows: 1999998 SSZ: 0 LGR: 0 #Blks: 286592 AvgRowLen: 1012.00 NEB: 0 ChainCnt: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
=======================================
SPD: BEGIN context at query block level
Access path analysis for TAB1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TAB1[TAB1]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Table: TAB1 Alias: TAB1
Card: Original: 1999998.000000 Rounded: 1999998 Computed: 1999998.000000 Non Adjusted: 1999998.000000
Scan IO Cost (Disk) = 112377.000000
Scan CPU Cost (Disk) = 2340947432.480000
Total Scan IO Cost = 112377.000000 (scan (Disk))
= 112377.000000
Total Scan CPU Cost = 2340947432.480000 (scan (Disk))
= 2340947432.480000
Access Path: TableScan
Cost: 113658.319374 Resp: 113658.319374 Degree: 0
Cost_io: 112377.000000 Cost_cpu: 2340947432
Resp_io: 112377.000000 Resp_cpu: 2340947432
Best:: AccessPath: TableScan
Cost: 113658.319374 Degree: 1 Resp: 113658.319374 Card: 1999998.000000 Bytes: 0.000000
Example of FTS Cost calculation…..
Formula for FTS (system statistics is gathered on load):-
iocost:=(No. of Blocks/MBRC)*mreadtim/sreadtim
cpucost:=#cpu cycles/(cpuspeed*(sreadtim*1000))—->#cpu cycles is Total Scan CPU Cost
No. of blocks:=286592
MBRC:=4
mreadtim:=0.974000
sreadtim:=0.621000
iocost:=(286592 /4)*(0.974000/0.621000)=112377
cpucost:=2340947432/(2942*(0.621000*1000))=1281
You can disable cpu cost using following command:=
alter system set “_optimizer_cost_model” =io;
You can test the explain plan in lower version of oracle by following command:=
SQL> alter system set optimizer_features_enable=”11.2.0″;
Example of FTS (System statistics gathered on
no load)
Formula for FTS (system statistics is gathered on no load):-
Sreadtim:=ioseektim+db_block_size/iotrfrspeed.
Mreadtim:=ioseektim+db_file_multiblock_read_count
*db_block_size/iotrftspeed
No. of blocks=710
db_file_multiblock_read_count=16 (This is set in session level by alter system command)
Sreadtim:=10+8192/4096=12
Mreadtim:=10+16*8192/4096=42
Iocost:=(710/16)*(42/12)=156+1=157
Example of FTS in Parallel…..
Formula for FTS in Parallel:-
10g cost at degree N=ceil(serial cost/0.9*N)
N=degree of parallelism
iocost=ceil(157/0.9*4)=44
If _optimizer_percent_parallel=75 , then 75% of parallel cost+25% of serial cost
Cardinality Calculation
Step 1: We need to create table and index as below:
Create table t1(c1 number(10),c2 char(100),c3 number(10));
Create index ind_c3 on column c3 and ind_c1_c3 on c1 and c3;
Step 2: Please insert some data as below:-
insert into t1 select 1,‘A’,1 from dual connect by rownum<1001;
insert into t1 select 2,‘A’,1 from dual connect by rownum<1001;
insert into t1 select 3,‘A’,1 from dual connect by rownum<1001;
insert into t1 select 4,‘B’,2 from dual connect by rownum<1001;
insert into t1 select 5,‘C’,NULL from dual connect by rownum<1001;
insert into t1 select 5,’D’,14 from dual connect by rownum<1001;
Step 3: Please analyze the table by compute statistics clause.
Case1 : Select * from t1 where c1=1
Cardinality=(num_rows)*(density) where Density=1/No. of distinct value
Case 2: Select * from t1 where c1 in (1,2)
Cardinality=num_rows*No. of occurrence of values in IN operator*individual
cardinality
Case 3:Select * from t1 where c1<=2
Cardinality=num_rows*[(limit-low val)/(high val-low val)+1/No. of distinct value]
Case 4:Select * from t1 where c1>=2
Cardinality=num_rows*[(high val-limit)/(high val-low val)+1/No. of distinct value]
Case 5: Select * from t1 where c2=2
If null value is there in c2,then
Cardinality=(num_rows-null values)*(density) Density=1/No. of distinct value
Case 6: Select * from t1 where c1 =1 and c2=1
Cardinality=(num_rows-null values)*(density of c1)*(density of c2)
Case 7: Select * from t1 where c1 between X and Y
Cardinality=num_rows*[(X-Y)/(high val-low val)+1/No. of distinct value+1/No. of
distinct value]
Cost Calculation for Index based table Scan
Index range scan with one predicate in where clause
Formula for Index based scan :-
Cost:=blevel + ceiling(leaf blocks * effective index selectivity)
Blevel=1
Leaf_blocks=10
Selectivity=1/distinct_value=1/3
Cost:=1+ceiling(10*1/3)=4
SQL> select c3 from t1 where c3=1;
Execution Plan
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1667 | 3334 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_C3 | 1667 | 3334 | 4 (0)| 00:00:01
Index range scan with more predicate in where clause
Formula for Index based scan :-
Cost:=blevel + ceiling(leaf blocks * effective index selectivity1*effective index
selectivity2)
Blevel=1
Leaf_blocks=14
Selectivity1=1/distinct_value=1/3 Selectivity2=1/distinct_value=1/5
Cost:=1+ceiling(14*1/3*1/5)=2
SQL> select c3 from t1 where c1=1 and c3=1;
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 333 | 1332 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_C1_C3 | 333 | 1332 | 2 (0)| 00:00:01 |
Formula for Index based table scan :-
C##TEST@TESTDB1> select index_name,blevel,distinct_keys,clustering_factor,num_rows,leaf_blocks from user_indexes;
INDEX_NAME
——————————————————————————–
BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS LEAF_BLOCKS
———- ————- —————– ———- ———–
T1_I1
2 500 9743 10000 1111
Cost:=blevel + ceiling(leaf blocks * effective index selectivity)
+ceiling(cluster_factor*effective table selectivity)
Blevel=1
Leaf_blocks=1111
Cluster_factor=9743
Selectivity1=1/distinct_values=1/500=0.002
Cost:=2+ceiling(1111*0.002)+ceiling(9743*0.002)=2+3+20=25
select
small_vc
from
t1
where
n1 = 2
and ind_pad = rpad(‘x’,40)
and n2 = 3
; 2 3 4 5 6 7 8 9
19 rows selected.
—– Plan Table —–
============
Plan Table
============
——————————————————-+———————————–+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
——————————————————-+———————————–+
| 0 | SELECT STATEMENT | | | | 25 | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 20 | 1160 | 25 | 00:00:01 |
| 2 | INDEX RANGE SCAN | T1_I1 | 20 | | 5 | 00:00:01 |
——————————————————-+———————————–+
Predicate Information:
———————-
2 – access(“N1″=2 AND “IND_PAD”=’x ‘ AND “N2″=3)
Content of other_xml column
===========================
db_version : 12.1.0.2
parse_schema : C##TEST
plan_hash_full : 162118435
plan_hash : 3320414027
plan_hash_2 : 162118435
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX_RS_ASC(@”SEL$1″ “T1″@”SEL$1” (“T1”.”N1″ “T1″.”IND_PAD” “T1”.”N2″))
BATCH_TABLE_ACCESS_BY_ROWID(@”SEL$1″ “T1″@”SEL$1”)
END_OUTLINE_DATA
*/
10053 trace shows:-
****** Costing Index T1_I1
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
ColGroup Usage:: PredCnt: 3 Matches Full: #1 Partial: Sel: 0.002000
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
ColGroup Usage:: PredCnt: 3 Matches Full: #1 Partial: Sel: 0.002000
Access Path: index (AllEqRange)
Index: T1_I1
resc_io: 25.000000 resc_cpu: 186236
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 25.005132 Resp: 25.005132 Degree: 1
Best:: AccessPath: IndexRange
Index: T1_I1
Cost: 25.005132 Degree: 1 Resp: 25.005132 Card: 20.000000 Bytes: 0.000000
***************************************
Calculation For Fast Full Index Scan
Formula for Fast Full Index scan :-
iocost:=(No. of Leaf Blocks/MBRC)*mreadtim/sreadtim
cpucost:=#cpu cycles/cpuspeed*(sreadtim*1000)
Formula for Index skip scan :-
Iocost:=no of distinct value(skipped column) * (blevel+leaf_block*density of
column in where clause)
optimizer_index_cost_adj:-
You can use parameter optimizer_index_cost_adj to adjust index scan cost.Just simply multiply the predicate optimizer_index_cost_adj/100 at the end of index cost
calculation formula.The default value in 10g is 100.So it has no effect in cost calculation.
For example :
Cost:=(blevel + ceiling(leaf blocks * effective index selectivity)
+ceiling(cluster_factor*effective table selectivity))* optimizer_index_cost_adj/100
Calculation For Index Scan with Histogram
Formula for Index scan(Histogram) :-
Cost:=blevel + ceiling(leaf blocks * effective index selectivity)
Blevel=4
Leaf_blocks=3142
Selectivity=999/21992
Cost:=147
SQL> exec dbms_stats.gather_table_stats(user,’IT1′,method_opt=>’for columns size 7 c1′,estimate_percent=>100);
We have generated frequency based histogram on column c1.So the cardinality(999) is exactly same as no of rows for
value ‘F’.With out histogram,full table scan was going on as cardinality was calculated as 21992/7 is 3142.
SQL> select c1 from it1 where c1=’F’;
999 rows selected.
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 999 | 976K| 147 (0)| 00:00:02 |
|* 1 | INDEX RANGE SCAN| IT1_C1 | 999 | 976K| 147 (0)| 00:00:02 |