Oracle11gR2 full table scan cost calculation (non-workload mode-noworkload)

Database Version Oracle11gR2
SQL> select * from v $ version where rownum = 1;

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

Create a manually managed tablespace, blockssize 8 k
SQL> create tablespace test datafile
'/U01/app/oracle/oradata/ROBINSON/datafile/test. dbf' size 50 m autoextend on maxsize 200 m
Uniform size 1 m segment space management manual blocksize 8 k; 2 3

Tablespace created.

Create a test user. The default tablespace is test.
SQL> create user test identified by oracle default tablespace test;

User created.

For ease of use, authorize DBA to test
SQL> grant dba to test;

Grant succeeded.

Create test table
SQL> Create Table Test as select * From dba_objects where 1 = 0;

Table created.

Set pctfree 99
SQL> ALTER TABLE test pctfree 99 pctused 1;

Table altered.

SQL> insert into test select * From dba_objects where rownum <2;

1 row created.

Make sure that one block exists in one row.
SQL> ALTER TABLE test minimize records_per_block;

Table altered.

SQL> insert into test select * From dba_objects where rownum <1000;

999 rows created.

SQL> commit;

Commit complete.

Collect table statistics
SQL> begin
Dbms_stats.gather_table_stats (ownname => 'test ',
Tabname => 'test ',
Estimate_percent = & gt; 100,
Method_opt => 'for all columns size 1 ',
Degree => dbms_stats.auto_degree,
Cascade => TRUE
);
END;
/2 3 4 5 6 7 8 9 10

PL/SQL procedure successfully completed.

SQL> select owner, blocks from dba_tables where owner = 'test' and table_name = 'test ';

OWNER BLOCKS
----------------------------------------
TEST 1, 1000

SQL> show parameter db_file_multiblock_read_count

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

The cost of full table scan is 220.
SQL> select count (*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 220 (0) | 00:00:03 |
| 1 | sort aggregate | 1 |
| 2 | table access full | TEST | 1000 | 220 (0) | 00:00:03 |
-------------------------------------------------------------------

The cost is calculated as follows:
Cost = (
# SRds * sreadtim +
# MRds * mreadtim +
CPUCycles/cpuspeed
)/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

Note: If the system statistics are not collected, Oracle uses non-workload statistics. If the statistics are collected, Oracle uses the workload statistics calculation method.
SQL> select pname, pval1 from sys. aux_stats $ where sname = 'sysstats _ main ';

PNAME PVAL1
----------------------------------------
CPUSPEED
Cpuspeednw 2696.05568
Ioseektim 10
Iotfrspeed4096
Maxthr
Mbrc
Mreadtim
Slavethr
Sreadtim

9 rows selected.

Because mbrc is 0, CBO uses noworkload to calculate the cost.

# Srds = 0. Because it is a full table scan, the reading speed of a single block is 0.
# MRDS = number of table blocks/multiple read parameters = 1000/16

Mreadtim = ioseektim + db_file_multiblock_count * db_block_size/iotftspeed
SQL> select (select pval1 from SYS. aux_stats $ where pname = 'ioseektim') +
2 (select Value
From v $ Parameter
Where name = 'db _ file_multiblock_read_count ')*
(Select value from V $ parameter where name = 'db _ block_size ')/
(Select pval1 from SYS. aux_stats $ where pname = 'iotfrspeed') "mreadtim"
3 4 5 6 7 from dual;

Mreadtim
----------
42

Sreadtim = ioseektim + db_block_size/iotfrspeed
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

CPUCycles is equal to CPU_COST in PLAN_TABLE.

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

Explained.

SQL> select cpu_cost from plan_table;

CPU_COST
----------
7271440

Cpuspeed is equal to CPUSPEEDNW = 2696.05568

So COST = 1000/16*42/12 + 7271440/2696. 05568/12/1000

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

CEIL (1000/16*42/12 + 7271440/2696. 05568/12/1000)
----------------------------------------------
219

The manually calculated COST is rounded to 219, which is different from the 220 we see. This is caused by the implicit parameter _ tablescan_cost_plus_one.

SQL> SELECT x. ksppinm NAME, y. ksppstvl VALUE, x. ksppdesc describ
FROM x $ ksppi x, x $ ksppcv y
WHERE x. inst_id = USERENV ('instance ')
AND y. inst_id = USERENV ('instance ')
AND x. indx = y. indx
AND x. ksppinm LIKE '% _ table_scan_cost_plus_one %'
/2 3 4 5 6 7

NAME VALUE DESCRIB
----------------------------------------------------------------------
_ Table_scan_cost_plus_one TRUE bump estimated full table scan
And index ffs cost by one
According to the description of this parameter, cost + 1 is added during table full scan and index fast full scan.
So I disabled the parameter change.

SQL> alter session set "_ table_scan_cost_plus_one" = false;

Session altered.

SQL> set autot trace
SQL> select count (*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 219 (0) | 00:00:03 |
| 1 | sort aggregate | 1 |
| 2 | table access full | TEST | 1000 | 219 (0) | 00:00:03 |
-------------------------------------------------------------------

The Cost obtained this time is equal to 219, exactly the same as the calculated value. Now, change the db_file_multiblock_read_count parameter.

SQL> alter session set db_file_multiblock_read_count = 32;

Session altered.

At this time, sreadtim = 12

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

Mreadtim = 74

SQL> select (select pval1 from SYS. aux_stats $ where pname = 'ioseektim') +
(Select Value
2 3 from V $ Parameter
4 where name = 'db _ file_multiblock_read_count ')*
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
----------
74

So cost is equal

SQL> select Ceil (1000/32*74/12 + 7271440/2696. 05568/12/1000) from dual;

Ceil (1000/32*74/12 + 7271440/2696. 05568/12/1000)
----------------------------------------------
193
SQL> set autot trace
SQL> select count (*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| ID | operation | Name | rows | cost (% CPU) | time |
-------------------------------------------------------------------
| 0 | SELECT statement | 1 | 193 (0) | 00:00:03 |
| 1 | sort aggregate | 1 |
| 2 | table access full | TEST | 1000 | 193 (0) | 00:00:03 |
-------------------------------------------------------------------

Matching the calculated Cost, we can see from the experiment that in 11gR2, the full table scan and Cost calculation method is still the same as 9i/10g, and remains unchanged.