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
-------------