Oracle11gR2全表掃描成本計(jì)算(非工作量模式-noworkload)

數(shù)據(jù)庫(kù)版本Oracle11gR2SQLgt; select * from v$version where rownum=1;BANNEROracle Database 11g Enterprise Edition Release

數(shù)據(jù)庫(kù)版本Oracle11gR2
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

創(chuàng)建手動(dòng)管理的表空間，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.

創(chuàng)建測(cè)試用戶test,默認(rèn)表空間 test
SQL> create user test identified by oracle default tablespace test;

User created.

為了簡(jiǎn)便，授權(quán)DBA給test
SQL> grant dba to test;

Grant succeeded.

創(chuàng)建測(cè)試表test
SQL> create table test as select * from dba_objects where 1=0 ;

Table created.

設(shè)置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.

確保一行一個(gè)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.

收集表統(tǒng)計(jì)信息
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 |
-------------------------------------------------------------------

成本的計(jì)算方式如下：
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

注意：如果沒(méi)有收集過(guò)系統(tǒng)統(tǒng)計(jì)信息，那么Oracle采用非工作量統(tǒng)計(jì)， 如果收集了，，Oracle采用工作量統(tǒng)計(jì)的計(jì)算方法
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.

我這里因?yàn)镸BRC 為0，所以CBO采用了非工作量(noworkload)來(lái)計(jì)算成本

#SRds=0,因?yàn)槭侨頀呙瑁瑔螇K讀為0
#MRds=表的塊數(shù)/多塊讀參數(shù)=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

手工計(jì)算出來(lái)的COST用四舍五入等于219，和我們看到的220有差別， 這是由于隱含參數(shù)_tablescan_cost_plus_one參數(shù)造成的

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
根據(jù)該參數(shù)的描述，在table full scan和index fast full scan的時(shí)候會(huì)將cost+1
那么我把改參數(shù)禁止了試一試

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，與計(jì)算值正好匹配，現(xiàn)在更改db_file_multiblock_read_count參數(shù)

SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

這個(gè)時(shí)候 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 |
-------------------------------------------------------------------

與計(jì)算的Cost相匹配，從實(shí)驗(yàn)種可以得出，在11gR2中，全表掃描計(jì)算Cost的方式依然和9i/10g一樣，沒(méi)有變化。

相關(guān)鏈接

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com