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 等于 PLAN_TABLE里面的CPU_COST
SQL> explain plan for select count(*) from test;
Explained.
SQL> select cpu_cost from plan_table;
CPU_COST
----------
7271440
cpuspeed 等于 CPUSPEEDNW= 2696.05568
那么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
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
根据该参数的描述，在table full scan和index fast full scan的时候会将cost+1
那么我把改参数禁止了试一试
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 |
-------------------------------------------------------------------
这次得到的Cost等于219，与计算值正好匹配，现在更改db_file_multiblock_read_count参数
SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
这个时候 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 = 'IOTFRSPEED') "mreadtim"
7 from dual;
mreadtim
----------
74
那么cost等于
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 tra