Oracle 11g R2 index fast full scan Cost Calculation

SQL> select * from v $ version where rownum <2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

Create index I _test_owner on test (owner );

SQL> create index I _test_owner on test (owner );

Index created.

Index fast full scan only needs to SCAN leaf blocks and use multiple reads, So query LEAF_BLOCKS

SQL> select leaf_blocks from user_indexes where index_name = 'I _ TEST_OWNER ';

LEAF_BLOCKS
-----------
22

SQL> explain plan for select count (owner) from test;

Explained.

SQL> select cpu_cost from plan_table;

CPU_COST
----------
1356672

SQL> select pname, pval1 from sys. aux_stats $ where sname = 'sysstats _ main ';

PNAME PVAL1
----------------------------------------
CPUSPEED 2500
CPUSPEEDNW 2696.05568
IOSEEKTIM 10
Iotfrspeed4096
MAXTHR
MBRC 12
MREADTIM 30
SLAVETHR
SREADTIM 5

9 rows selected.

Because MBRC is not empty, CBO uses the workload mode to calculate the Cost

The index fast full scan cost calculation formula is as follows:

Cost = (
# SRds * sreadtim +
# MRds * mreadtim +
CPUCycles/cpuspeed/1000
)/Sreadtime

# SRds-number of single block reads
# MRds-number of multi block reads
# CPUCyles-number of CPU cycles

Sreadtim-single block read time
Mreadtim-multi block read time
Cpuspeed-CPU cycles per second

Cost = (
# SRds * sreadtim + --- SRds = 0
# MRds * mreadtim + --- MRds = Leaf_Blocks/MBCR = 22/12, mreadtim = 30
CPUCycles/cpuspeed/1000 --- CPUCycles = PLAN_TABLE.CPU_COST, cpuspeed = 2500
)/Sreadtime

Therefore, the cost of manual computing is equal:
SQL> select ceil (22/12*30/5) + ceil (1356672/2500/5/1000) + 1 from dual; --- + 1 because _ table_scan_cost_plus_one is set to true

CEIL (22/12*30/5) + CEIL (1356672/2500/5/1000) + 1
--------------------------------------------
13

SQL> select count (owner) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1992658997

Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 13 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 5 |
| 2 | index fast full scan | I _TEST_OWNER | 10000 | 50000 | 13 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

Now we use non-workload statistics to calculate Cost

SQL> begin
Dbms_stats.set_system_stats ('cpuspeed', 0 );
Dbms_stats.set_system_stats ('sreadtim', 0 );
Dbms_stats.set_system_stats ('mreadtim', 0 );
Dbms_stats.set_system_stats ('mbrc', 0 );
End;
/
2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_file_multiblock_read_count integer 16

SQL> select (select pval1 from sys. aux_stats $ where pname = 'ioseektim') +
(Select value
From v $ parameter
Where name = 'db _ file_multiblock_read_count ')*
2 3 4 5 (select value from v $ parameter where name = 'db _ block_size ')/
6 (select pval1 from sys. aux_stats $ where pname = 'ottfrspeed') "mreadtim"
7 from dual;

Mreadtim
----------
42
SQL> select (select pval1 from sys. aux_stats $ where pname = 'ioseektim') +
(Select value from v $ parameter where name = 'db _ block_size ')/
(Select pval1 from sys. aux_stats $ where pname = 'iotfrspeed') "sreadtim"
From dual; 2 3 4

Sreadtim
----------
12

SQL> select cpu_cost from plan_table;

CPU_COST
----------
1356672

Cost Calculation Formula

Cost = (
# SRds * sreadtim + --- SRds = 0
# MRds * mreadtim + --- MRds = Leaf_Blocks/db_file_multiblock_read_count = 22/16, mreadtim = 42
CPUCycles/cpuspeed/1000 --- CPUCycles = PLAN_TABLE.CPU_COST, cpuspeed = 2696.05568
)/Sreadtime

The Cost calculated manually is equal:

SQL> select ceil (22/16*42/12) + ceil (1356672/2696. 05568/12/1000) + 1 from dual;

CEIL (22/16*42/12) + CEIL (1356672/2696. 05568/12/1000) + 1
----------------------------------------------------
7
SQL> set autot trace
SQL> select count (owner) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1992658997

Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 7 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 5 |
| 2 | index fast full scan | I _TEST_OWNER | 10000 | 50000 | 7 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

From the experiment, we can see that the cost algorithm of index fast full scan in 11gR2 is still the same as that of 9i and 10g, and remains unchanged.