oracle实验记录 （全表扫描COST计算方法）

实验下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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-613616/