Oracle 11g R2 full table scan cost calculation (workload mode-workload)

Tested the cost calculation of full table scan for Oracle11gR2 in non-workload mode. Now we can test the cost calculation of full table scan for Oracle11gR2 in workload mode.

First, we will increase the table blocks to 10003.

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

OWNER BLOCKS
----------------------------------------
TEST 1, 10003

Then manually set the CPU speed of the workload to 2500, one read is equal to 5, multiple reads are equal to 30, and MBRC is equal to 12.

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

PL/SQL procedure successfully completed.

Use the explain plan to obtain CPU_COST --- the value is 72735764.

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

Explained.

SQL> select cpu_cost from plan_table;

CPU_COST
----------
72735764

The 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 = BLOCKS/MBCR = 10003/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 (10003/12*30/5) + ceil (72735764/2500/5/1000) + 1 from dual;

CEIL (10003/12*30/5) + CEIL (72735764/2500/5/1000) + 1
------------------------------------------------
5009

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 | 5009 (1) | 00:00:26 |
| 1 | sort aggregate | 1 |
| 2 | table access full | TEST | 10000 | 5009 (1) | 00:00:26 |
-------------------------------------------------------------------

The cost of manual computing is exactly equal to the Cost of Oracle computing. Here it also shows that in the workload mode, the cost calculation method of www.bkjia.com full table scan is still the same as that of Oracle9i and Oracle10g.

In the workload mode, we can see from the cost of full table scan that the change to the db_file_multiblock_read_count parameter has no impact on the cost calculation of full table scan. MBRC is the impact. For example:

SQL> show parameter db_file_multiblock_read_count

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

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 | 5009 (1) | 00:00:26 |
| 1 | sort aggregate | 1 |
| 2 | table access full | TEST | 10000 | 5009 (1) | 00:00:26 |
-------------------------------------------------------------------

SQL> alter session set db_file_multiblock_read_count = 32;

Session altered.

SQL> select count (*) from test;

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

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

We can see that the change of db_file_multiblock_read_count has no impact on the COST, because the COST in the workload mode is only related to MBRC.

Related Links