oracle實驗記錄 （全表掃描COST計算方法）

fufuh2o發表於2009-08-31
Oracle



實驗下FTS 時候 COST 的計算

 


公式:    
According to the CPU costing model:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +~~~~~~~~~~~~~~~~~~~~~~~~~~~~來自於cost-based oracle書中
#CPUCycles / cpuspeed
) / sreadtim
where
#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second


COST=CPU COST+IO COST (8I 不計算CPU COST)
SQL> select * from v$version;實驗版本

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

測試
SQL> create table t1 (a int);

表已建立。

 

SQL> ed
已寫入 file afiedt.buf

  1  declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into t1 values(i);
  5  end loop;
  6* end;
SQL> /

PL/SQL 過程已成功完成。

SQL> select blocks from user_tables where table_name='T1';

    BLOCKS
----------


SQL> exec dbms_stats.gather_table_stats('SYS','T1');

PL/SQL 過程已成功完成。

SQL> select blocks from user_tables where table_name='T1';

    BLOCKS
----------
        20

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> show parameter db_file_mu

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


SQL> alter session set "_optimizer_cost_model"=CPU;

會話已更改。

 

SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t1;

已選擇10000行。


執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 30000 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 30000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計資訊
----------------------------------------------------------
        135  recursive calls
          0  db block gets
        703  consistent gets
          0  physical reads
          0  redo size
     176812  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)

 


SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';~~~檢視CBO計劃

會話已更改。

SQL> select * from t1;

已選擇10000行。


執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 30000 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 30000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        690  consistent gets
          0  physical reads
          0  redo size
     176812  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  6.28  Resp: 6.28  Degree: 0
      Cost_io: 6.00  Cost_cpu: 1642429~~~~~~~~~~~~~~~~~~~~~~IO部分cost=6
      Resp_io: 6.00  Resp_cpu: 1642429
  Best:: AccessPath: TableScan
         Cost: 6.28  Degree: 1  Resp: 6.28  Card: 10000.00  Bytes: 0

**********Cost_io: 6.00  IO 部分  ,CPU=0.28
SQL> select pname,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     484.974958  speed in millions of operations per second
IOSEEKTIM                              10  disk seek time in milliseconds
IOTFRSPEED                           4096   disk transfer time in bytes per millisecond
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

已選擇9行。以上沒有資訊收集 ORACLE 會用DEFAULT 計算************

計算*使用 預設資訊**************************
MBRC=db_file_multiblock_read_count=16
sreadtim=IOSEEKTIM+db+block_size/IOTFRSPEED=12
mreadtime=IOSEEKTIM+db_file_multiblock_read_count*db+block_size/IOTFRSPEED=42

MDRS=BLOCKS/MBRC
#SRds =0(single-block reads),因為是 FTS 都是多塊讀取
最早的公式變換一下(除開)
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +**************io cost
#CPUCycles / (cpuspeed * sreadtim)********cpu cost

SQL> select 20/16*42/12 from dual;

20/16*42/12
-----------
      4.375

4.375=5 由於
_optimizer_ceil_cost           TRUE                      TRUE      FALSE      FALSE

CEIL cost in CBO~~~~~~~~~~取整

SQL> select ceil(20/16*42/12) from dual;

CEIL(20/16*42/12)
-----------------
                5~~~~~~~~~~~~~~~~~~~~~~~~~~~IO 部分IO COST

又由於 _table_scan_cost_plus_one           = true   加1 所以IO COST=6
true時候COST+1 表示開始需要訪問表的SEGMENT HEADER BLOCK,這是優化器的內建技巧之一(避免小表中用INDEX)
********oracle cost-based oracle原文描述 _table_scan_cost_plus_one
This is the type of minor tweak that appears surprisingly frequently in Oracle, and makes
it very difficult to produce any sort of documentation about the optimizer that is both concise
and complete. In this case, the change may represent the requirement for the necessary access
to the table’s segment header block at the start of a tablescan; alternatively it may simply be
one of several tricks built into the optimizer to bias it towards using indexed access paths on
very small tables.
***********
CPU 部分 轉換成IO COST 單位

#CPUCycles / (cpuspeed * sreadtim)
#CPUCycles:1642429
cpuspeed default 使用 CPUSPEEDNW
sreadtime:12(milliseconds)=12000(microseconds)
 
SQL> select 1642429/(484*12000) from dual;

1642429/(484*12000)
-------------------
         .282787362

 

 

*********************
實驗改_table_scan_cost_plus_on為FALSE
SQL> alter system set "_table_scan_cost_plus_one"=false;

系統已更改。

 

SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t1;

已選擇10000行。


執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 30000 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 30000 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        690  consistent gets
          0  physical reads
          0  redo size
     176812  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

 


trace中
  _table_scan_cost_plus_one           = false

Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  20  AvgRowLen:  3.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  5.28  Resp: 5.28  Degree: 0
      Cost_io: 5.00  Cost_cpu: 1642429
      Resp_io: 5.00  Resp_cpu: 1642429
  Best:: AccessPath: TableScan
         Cost: 5.28  Degree: 1  Resp: 5.28  Card: 10000.00  Bytes: 0

可以看出 IO COST=5   CPU=0.28

 


*************************
SQL> alter system set "_optimizer_ceil_cost"=false;

發現改過這個引數後 oracle 會出現些混亂

系統已更改。
NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ

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

KSPPDESC
-----------------------------------------------------------------------------------
-------------------------------------------------
_optimizer_ceil_cost           FALSE                     FALSE     FALSE      FALSE

CEIL cost in CBO


SQL> /
輸入 par 的值:  table_scan_cost_plus_one
原值   14:   x.ksppinm like '%_&par%'
新值   14:   x.ksppinm like '%_table_scan_cost_plus_one%'

NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ

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

KSPPDESC
-----------------------------------------------------------------------------------
-------------------------------------------------
_table_scan_cost_plus_one      FALSE                     FALSE     FALSE      FALSE

bump estimated full table scan and index ffs cost by one

 

SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t1;

已選擇10000行。


執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 30000 |     6  (17)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 30000 |     6  (17)| 00:00:01 |
--------------------------------------------------------------------------


統計資訊
----------------------------------------------------------
        154  recursive calls
          0  db block gets
        706  consistent gets
         23  physical reads
          0  redo size
     176812  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> exit


_table_scan_cost_plus_one           = false

 

Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  20  AvgRowLen:  3.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  5.28  Resp: 5.28  Degree: 0
      Cost_io: 5.00  Cost_cpu: 1642429
      Resp_io: 5.00  Resp_cpu: 1642429
  Best:: AccessPath: TableScan
         Cost: 6.00  Degree: 1  Resp: 5.28  Card: 10000.00  Bytes: 0          注意cost  Cost: 6.00  Degree: 1(並行度)  Resp: 5.28(Cost for full parallel   execution of a step.) 這個是指並行時候的COST ,現在並行度為1 就代表串 行 ,oracle向上取整COST 到了 6(將總5.28) 原來是5.28 autotrace中 顯示為5 現在為6

理應 為4才對 不取整 不加一 IO COST應該等於4 ，而實驗結果有點奇怪


SQL> alter system set "_optimizer_ceil_cost"=true;(這個引數只在CBO COST計算時 使用CPU_costing時候 才有用)

系統已更改。
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t1;

已選擇10000行。


執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 30000 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 30000 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        690  consistent gets
          0  physical reads
          0  redo size
     176812  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> alter session set events '10053 trace name context off';               改回後正常了

 

 


會話已更改。


****************************模擬下oracle 8i         傳統COST計算 (只計算IO部分)

SQL> alter system set "_table_scan_cost_plus_one"=true;

系統已更改。


SQL> alter system set "_optimizer_cost_model"=io~~~~~~~~~~~~~換成傳統IO成本計算~~這將只單一的結算IO COST 並且不使用系統統計資訊
  2  ;

系統已更改。

SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t1;

已選擇10000行。


執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 30000 |     4 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 30000 |     4 |
----------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)


統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        690  consistent gets
          0  physical reads
          0  redo size
     176812  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> alter session set events '10053 trace name context off';

會話已更改。

TRACE中
  _optimizer_cost_model               = io
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  20  AvgRowLen:  3.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  4.00  Resp: 4.00  Degree: 0
      Cost_io: 4.00  Cost_cpu: 0
      Resp_io: 4.00  Resp_cpu: 0
  Best:: AccessPath: TableScan
         Cost: 4.00  Degree: 1  Resp: 4.00  Card: 10000.00  Bytes: 0


這個4  COST=(HWM下block/adjusted dbf_mbrc)得出

ADJUSTER_MBRC=blocs/cost=20/4=5(大約 應該反覆測試) 一般就是這麼推出的(9I 和10G 不一樣 )


db_file_multiblock_read_count Cost Adjusted dbf_mbrc(這個表來自COST-BASED ORACLE)9I條件下
4    2,396       4.17
8     1,518   6.59
16    962    10.40
32    610     16.39
64    387     25.84
128   245    40.82

與db_file_multiblock_read_count 大小有關係


Block Size Cost of 10,000 Block Scans Adjusted dbf_mbrc Cost for 80MB Scan(這個表來自COST-BASED ORACLE)9I條件下
2KB 611 16.39 2,439
4KB 963 10.40 1,925
8KB 1,519 6.59 1,519
8KB ASSM 1,540 n/a 1,540
16KB 2,397 4.17 1,199

塊大小的影響

SQL> create table t2 (a int);

表已建立。


SQL> ed
已寫入 file afiedt.buf

  1  declare
  2  begin
  3  for i in 1..5000 loop
  4  insert into t2 values(i);
  5  end loop;
  6* end;
SQL> /

PL/SQL 過程已成功完成。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T2');

PL/SQL 過程已成功完成。

SQL> select blocks from user_tables where table_name='T2';

    BLOCKS
----------
        12

SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t2;

已選擇5000行。


執行計劃
----------------------------------------------------------
Plan hash value: 1513984157

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 | 15000 |     3 |
|   1 |  TABLE ACCESS FULL| T2   |  5000 | 15000 |     3 |
----------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)~~~~~~~關閉


統計資訊
----------------------------------------------------------
        135  recursive calls
          0  db block gets
        362  consistent gets
          0  physical reads
          0  redo size
      88572  bytes sent via SQL*Net to client
       4048  bytes received via SQL*Net from client
        335  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       5000  rows processed
SQL> select ceil(12/5)from dual;

CEIL(12/5)
----------
         3
            
TRACE中結果
_optimizer_cost_model               = io

 

SINGLE TABLE ACCESS PATH
  Table: T2  Alias: T2    
    Card: Original: 5000  Rounded: 5000  Computed: 5000.00  Non Adjusted: 5000.00
  Access Path: TableScan
    Cost:  3.00  Resp: 3.00  Degree: 0
      Cost_io: 3.00  Cost_cpu: 0
      Resp_io: 3.00  Resp_cpu: 0
  Best:: AccessPath: TableScan
         Cost: 3.00  Degree: 1  Resp: 3.00  Card: 5000.00  Bytes: 0

 

 

 

**********************關於 收集系統統計
SQL> alter system set "_optimizer_cost_model"=choose
  2  ;

系統已更改。
SQL> exec dbms_stats.gather_system_stats('START');

PL/SQL 過程已成功完成。

SQL> select count(*) from t1;

  COUNT(*)
----------
     10000

SQL> select count(*) from t2;

  COUNT(*)
----------
      5000

SQL> select count(*) from dba_users;

  COUNT(*)
----------
        28

SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      1582

SQL> exec dbms_stats.gather_system_stats('STop');~~~~~~~~~~~~~~~~執行一陣收集資訊

PL/SQL 過程已成功完成。
SQL> /

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              743
CPUSPEEDNW                     484.974958
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR                               2048
MBRC                                    4
MREADTIM                            6.364
SLAVETHR
SREADTIM                            5.769

已選擇9行。

SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t1;

已選擇10000行。


執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 30000 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 30000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        690  consistent gets
          0  physical reads
          0  redo size
     176812  bytes sent via SQL*Net to client
       7711  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

 

Cost = (
#SRds +
#MRds * mreadtim / sreadtim +**************io cost
#CPUCycles / (cpuspeed * sreadtim)********cpu cost


COST=20/4*6.364/5.796

SQL> select ceil(20/4*6.364/5.769) from dual;

CEIL(20/4*6.364/5.769)
----------------------
                     6
另外 table _table_scan_cost_plus_one 為TRUCE 所以 IO COST =7
TRACE中
_table_scan_cost_plus_one           = true
SYSTEM STATISTICS INFORMATION
*****************************
  Using WORKLOAD Stats
  CPUSPEED: 743 millions instructions/sec
  SREADTIM: 6 milliseconds
  MREADTIM: 6 millisecons
  MBRC: 4.000000 blocks
  MAXTHR: 2048 bytes/sec
  SLAVETHR: -1 bytes/sec
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  20  AvgRowLen:  3.00
***************************************
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1    
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  7.38  Resp: 7.38  Degree: 0
      Cost_io: 7.00  Cost_cpu: 1642429
      Resp_io: 7.00  Resp_cpu: 1642429
  Best:: AccessPath: TableScan
         Cost: 7.38  Degree: 1  Resp: 7.38  Card: 10000.00  Bytes: 0

這是採用收集系統統計資訊之後COST 有所上升


cpu部分計算=1642429/(743*5769)=0.38   將CPU_COST 換算成IO 單位
? CPUSPEED = 743MHZ
? sreadtim = 5769 milliseconds = 5769 microseconds (standardizing units of time)
? #CPUCycles (called cpu_cost in the plan_table) = 1642429
#CPUCycles / (cpuspeed * sreadtim)
SQL> select 1642429/(743*5769) from dual;

1642429/(743*5769)
------------------
        .383175076

另外可以自己設定系統統計資訊
delete aux_stats$
execute
dbms_stats.set_system_stats('CPUSPEED',500);
dbms_stats.set_system_stats('SREADTIM',5.0);
dbms_stats.set_system_stats('MREADTIM',30.0);
dbms_stats.set_system_stats('MBRC',12);


小結:oracle 10g _optimizer_cost_model CHOOSE模式 基本就是使用CPU_COSTING計算,所以需要系統統計資訊 沒有時候用DEFALUT的,不再用8I 那樣傳統的COST計算(只簡單的計

算IO)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613616/，如需轉載，請註明出處，否則將追究法律責任。

相關文章