oracle 11g r2 价格,Oracle11gR2 全表扫描成本

数据库版本Oracle11gR2

SQL> select * from v$version where rownum=1;

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

注意：如果没有收集过系统统计信息，那么Oracle采用非工作量统计，如果收集了，Oracle采用工作量统计的计算方法

SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

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') /

(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"

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

手工计算出来的COST用四舍五入等于219，和我们看到的220有差别，这是由于隐含参数_tablescan_cost_plus_one参数造成的

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 trace

SQL> select count(*) from test;

Execution Plan

----------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |   193   (0)| 00:00:03 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST |  1000 |   193   (0)| 00:00:03 |

-------------------------------------------------------------------

与计算的Cost相匹配，从实验种可以得出，在11gR2中，全表扫描计算Cost的方式依然和9i/10g一样，没有变化