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

測試了非工作量模式下Oracle11gR2全表掃描的成本計算，現在測試一下在工作量模式下Oracle11gR2全表掃描的成本計算
首先講表blocks增加到10003個

SQL> select owner,blocks from dba_tables where table_name='TEST' and owner='TEST';

OWNER                              BLOCKS
-
 

------------------------------ ----------
TEST                                10003

然后人工設置工作量的CPUSPEED=2500，單塊讀等于5，多塊讀等于30，MBRC等于12

SQL> begin
   dbms_stats.set_system_stats('CPUSPEED',2500);
   dbms_stats.set_system_stats('SREADTIM',5);
   dbms_stats.set_system_stats('MREADTIM',30);
   dbms_stats.set_system_stats('MBRC',12);
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

利用explain plan得到CPU_COST---這里等于 72735764

SQL> explain plan for select count(*) from test;

Explained.

SQL> select cpu_cost from plan_table;

  CPU_COST
----------
  72735764

成本計算公式如下：

Cost = (
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed /1000
       ) / 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

Cost = (
       #SRds * sreadtim +                            ---SRds=0
       #MRds * mreadtim +                          ---MRds=BLOCKS/MBCR=10003/12, mreadtim=30
       CPUCycles / cpuspeed / 1000         ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2500
       ) / sreadtime

所以人工計算的成本等于：

SQL> select ceil(10003/12*30/5)+ceil(72735764/2500/5/1000)+1 from dual;

CEIL(10003/12*30/5)+CEIL(72735764/2500/5/1000)+1
------------------------------------------------
                                            5009

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 |  5009   (1)| 00:00:26 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 10000 |  5009   (1)| 00:00:26 |
-------------------------------------------------------------------

人工計算的cost正好等于Oracle計算的Cost 這里也說明Oracle11gR2 在工作量模式下，www.linuxidc.com全表掃描的成本計算方法依然同Oracle9i,Oracle10g

工作量模式下，從全表掃描的成本可以看出，參數db_file_multiblock_read_count 的更改對全表掃描成本計算沒有影響，有影響的是MBRC，舉個例子：

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

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 |  5009   (1)| 00:00:26 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 10000 |  5009   (1)| 00:00:26 |
-------------------------------------------------------------------

SQL> alter session set db_file_multiblock_read_count=32;

Session altered.

SQL>  select count(*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5009   (1)| 00:00:26 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 10000 |  5009   (1)| 00:00:26 |
-------------------------------------------------------------------

可以看到更改db_file_multiblock_read_count對于成本沒有任何影響，因為工作量模式下的COST只跟MBRC有關。

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]