BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
创建手动管理的表空间，blockssize 8k
SQL> create tablespace test datafile
'/u01/app/oracle/oradata/ROBINSON/datafile/test.dbf' size 50m autoextend on maxsize 200m
uniform size 1m segment space management manual blocksize 8k; 2 3
Tablespace created.
创建测试用户test,默认表空间 test
SQL> create user test identified by oracle default tablespace test;
User created.
为了简便，授权DBA给test
SQL> grant dba to test;
Grant succeeded.
创建测试表test
SQL> create table test as select * from dba_objects where 1=0 ;
Table created.
设置pctfree 99
SQL> alter table test pctfree 99 pctused 1;
Table altered.
SQL> insert into test select * from dba_objects where rownum<2;
1 row created.
确保一行一个block
SQL> alter table test minimize records_per_block;
Table altered.
SQL> insert into test select * from dba_objects where rownum<1000;
999 rows created.
SQL> commit;
Commit complete.
收集表统计信息
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
/ 2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SQL> select owner,blocks from dba_tables where owner='TEST' and table_name='TEST';
OWNER BLOCKS
------------------------------ ----------
TEST 1000
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
全表扫描的成本等于220
SQL> select count(*) from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 220 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 1000 | 220 (0)| 00:00:03 |
-------------------------------------------------------------------
成本的计算方式如下：
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
CPUCycles / cpuspeed
) / 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
PNAME PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW 2696.05568
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
9 rows selected.
我这里因为MBRC 为0，所以CBO采用了非工作量(noworkload)来计算成本
#SRds=0,因为是全表扫描，单块读为0
#MRds=表的块数/多块读参数=1000/16
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
2 (select value
from v$parameter
where name = 'db_file_multiblock_read_count') *
(select value from v$parameter where name = 'db_block_size') /
(se