Oracle 11g R2 全表掃描成本計算(非工作量模式-noworkload)

資料庫版本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採用非工作量統計，www.bkjia.com 如果收集了，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有差別，www.bkjia.com 這是由於隱含參數_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一樣，沒有變化。

相關連結