转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/44261859
***********************************************
一、CBO之Full Table Scan - FTS算法
***********************************************
1、建表
SQL> create table gyj_t1(id int,name varchar2(20));
Table created.
2、插入10万行数据
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into gyj_t1 values(i,lpad('gyj','10','j')||i);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
3、收集统计信息
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(
3 ownname => 'GYJ',
4 tabname => 'GYJ_T1',
5 estimate_percent => 100,
6 method_opt => 'for all columns size 1',
7 degree => DBMS_STATS.AUTO_DEGREE,
8 cascade=>TRUE
9 );
10 END;
11 /
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') +
2 (select value
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
----------
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') +
2 (select value from v$parameter where name = 'db_block_size') /
3 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
4 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
------------------------------ ----------
CPUSPEEDNW 2894.14695
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
（11）隐含參数：
SQL> @?/rdbms/admin/show_para
Enter value for p: table_scan_cost
old 12: AND upper(i.ksppinm) LIKE upper('%&p%')
new 12: AND upper(i.ksppinm) LIKE upper('%table_scan_cost%')
P_NAME P_DESCRIPTION P_VALUE ISDEFAULT ISMODIFIED ISADJ
---------------------------------------- -------------------------------------------------- ------------------------------ --------- ---------- -----
_table_scan_cost_plus_one bump estimated full table scan and index ffs cost TRUE TRUE FALSE FALSE
by one
（12）IO成本
I/O Cost = 1 + CEIL(#MRds * (mreadtim / sreadtim))
/O Cost = 1 + CEIL(23.125 * ((10+16*8192/4096) / (10+8192/4096)))
SQL> select 1 + CEIL(23.125 * ((10+16*8192/4096) / (10+8192/4096))) from dual;
1+CEIL(23.125*((10+16*8192/4096)/(10+8192/4096)))
-------------------------------------------------
82
(13)CPU成本
CPU Cost = ROUND(#CPUCycles / cpuspeed / 1000 / sreadtim)
ROUND(17634933/ 2894.14695 / 1000 / (10+8192/4096))=1