1、建表
SQL> create table gyj_t1(id int,name varchar2(20));
?
Table created.
?
2、插入10万行数据
SQL> begin
for i in 1 .. 100000 loop
insert into gyj_t1 values(i,lpad(‘gyj‘,‘10‘,‘j‘)||i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
?
3、收集统计信息
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘GYJ‘,
tabname => ‘GYJ_T1‘,
estimate_percent => 100,
method_opt => ‘for all columns size 1‘,
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
/
PL/SQL procedure successfully completed.
/
?
?
4、执行SQL，生成执行计划，即能看到如下全表扫描的成本
SQL> set autot traceonly;
SQL> select count(*) from gyj_t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2553183190
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| GYJ_T1 | 100K| 84 (2)| 00:00:02 |
---------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
373 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
?
?
5、全表扫描CBO公式
FTS Cost = I/O Cost + CPU Cost
I/O Cost = 1 + CEIL(#MRds * (mreadtim / sreadtim))
#MRds = #Blks / MBRC
CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)
?
6、#MRds
#MRds = #Blks / MBRC
（1）#Blks
SQL> select blocks from dba_tables where owner=‘GYJ‘ and table_name=‘GYJ_T1‘;
?
? ? BLOCKS
----------
? ? ? ?370
?
（2）多块读的参数
SQL> show parameter db_file_multiblock_read_count
?
NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE ? ? ? ?VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count ? ? ? ?integer ? ? 16
?
?
（3）#MRds
SQL> select 370/16 from dual;
?
? ?370/16
----------
?23.125
?
7、mreadtim
?
?
SQL> select (select pval1 from sys.aux_stats$ where pname = ‘IOSEEKTIM‘) +
(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"
from dual;
mreadtim
----------
42
即mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
=10+16*8192/4096=42
?
?
8、sreadtim
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;
sreadtim
----------
12
即 sreadtim=ioseektim+db_block_size/iotfrspeed
=10+8192/4096=12
?
?
?
（9）#CPUCycles
explain plan for select count(*) from GYJ_T1;
SQL> select cpu_cost from plan_table;
CPU_COST
----------
17634933
?
?
? ?
（10）CPUSPEEDNW
SQL> select pname, pval1 from sys.aux_stats$ where sname=‘SYSSTATS_MAIN‘;
PNAME PVAL1
-------------