測(cè)試了非工作量模式下Oracle11gR2全表掃描的成本計(jì)算,現(xiàn)在測(cè)試一下在工作量模式下Oracle11gR2全表掃描的成本計(jì)算
首先講表blocks增加到10003個(gè)
SQL> select owner,blocks from dba_tables where table_name='TEST' and owner='TEST';
OWNER BLOCKS
-
------------------------------ ----------
TEST 10003
然后人工設(shè)置工作量的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
成本計(jì)算公式如下:
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
所以人工計(jì)算的成本等于:
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 |
-------------------------------------------------------------------
人工計(jì)算的cost正好等于Oracle計(jì)算的Cost 這里也說(shuō)明Oracle11gR2 在工作量模式下,www.linuxidc.com全表掃描的成本計(jì)算方法依然同Oracle9i,Oracle10g
工作量模式下,從全表掃描的成本可以看出,參數(shù)db_file_multiblock_read_count 的更改對(duì)全表掃描成本計(jì)算沒(méi)有影響,有影響的是MBRC,舉個(gè)例子:
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對(duì)于成本沒(méi)有任何影響,因?yàn)楣ぷ髁磕J较碌腃OST只跟MBRC有關(guān)。
本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)!虛擬主機(jī)域名注冊(cè)頂級(jí)提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|