CPU cost对全表扫描成本的影响

Oracle在未引入CPU cost model之前,它会假设如下两个事实是成立的:

1single-block reads are just as expensive as multiblock reads

2、全表扫描的时候,会根据MBDivisormultiblock read divisor)来计算全表扫描的成本

 

9i里,MBDivisor会基于db_file_multiblock_read_count的值的变化而变化,一个大致的估算公式为:

MBDivisor = 1.6765 * power(db_file_multiblock_read_count, 0.6581)

 

而基于MBDivisor全表扫描的成本的估算公式为:

tsc cost = Num Blocks / MBDivisor

 

我们来看一个实际计算的例子:

***************************************

PARAMETERS USED BY THE OPTIMIZER

********************************

OPTIMIZER_FEATURES_ENABLE = 9.2.0

OPTIMIZER_MODE/GOAL = Choose

……省略显示其他内容

DB_FILE_MULTIBLOCK_READ_COUNT = 16

 

***************************************

BASE STATISTICAL INFORMATION

***********************

Table stats    Table: OPFQ_QUE_ITM   Alias: OPFQ_QUE_ITM

  PARTITION [4]    CDN: 1338983  NBLKS:  32408  AVG_ROW_LEN:  105

  TOTAL ::  CDN: 1338983  NBLKS:  32408  AVG_ROW_LEN:  105

 

TABLE: OPFQ_QUE_ITM     ORIG CDN: 1338983  ROUNDED CDN: 7114  CMPTD CDN: 7114

  Access path: tsc  Resc:  3118  Resp:  3118

 

这里全表扫描的成本Resc3118,这里tsc表示tablescanResc表示resource cost

Resp表示response cost,意思是指在有并行情况下的成本,这里没有使用并行表扫描,所以RespResc相等。

我们来看一下上述3118是怎样算出来的:

Num Blocks这里为32408

DB_FILE_MULTIBLOCK_READ_COUNT16,所以MBDivisor10.3952

 

SQL> select 1.6765 * power(16, 0.6581) from dual;

 

1.6765*POWER(16,0.6581)

———————–

       10.3952495216659

 

Num BlocksMBDivisor带入上述公式:

tsc cost = Num Blocks / MBDivisor

       = 32408 / 10.3952

       = 3117.5927

从上述结果可以看到,计算出来的全表扫描的成本四舍五入后就是3118

 

Oracle9i开始,引入了CPU cost model,通过dbms_stats.gather_system_stats收集系统级的统计信息,这样Oracle就能够规避上述两个假设(尤其是第一个假设,通常情况下,一次单快读的时间是要小于一次多快读的时间)所带来的成本计算不准问题。

CPU cost model所引入的系统级别的统计信息存储在表AUX_STATS$里,当你执行dbms_stats.gather_system_stats的时候,Oracle会在AUX_STATS$里记录如下四个方面的信息:

1Assumed CPU speed in MHz

2Single-block read average time in milliseconds

3Multiblock read average time in milliseconds

4Typical achieved multiblock read

 

正是因为Oracle会记录目标数据库所在的database server上的Typical achieved multiblock read,所以这实际上会对Oracle计算全表扫描的成本带来重大影响,因为这种情况下MBDivisor实际上就和DB_FILE_MULTIBLOCK_READ_COUNT的值没有关系了

 

Oracle9i开始,通过一个隐含参数_optimizer_cost_model来控制是否开启CPU cost model,它的默认值为CHOOSE,意思是如果AUX_STATS$里有相关记录,则开启CPU cost model,否则还是沿用以前的成本计算模型(即计算的全部是IO cost)。

 

9.2.0.1AUX_STATS$里没有记录:

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

Connected as scott

SQL> select name,value from sys.all_parameters where name like ‘%optimizer_cost_model%’;

 

NAME                           VALUE

—————————— ———-

_optimizer_cost_model          CHOOSE

 

SQL> select count(*) from sys.aux_stats$;

 

  COUNT(*)

———-

         0

 

SQL> show parameter multiblock;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_multiblock_read_count        integer     16

 

所以9.2.0.1里全表扫描的成本实际上还是基于db_file_multiblock_read_count的值:

Table stats    Table: T1   Alias: T1

  TOTAL ::  CDN: 588639  NBLKS:  8000  AVG_ROW_LEN:  93

— Index stats

  INDEX NAME: IDX_T1  COL#: 4

    TOTAL ::  LVLS: 2   #LB: 1284  #DK: 29915  LB/K: 1  DB/K: 19  CLUF: 588639

_OPTIMIZER_PERCENT_PARALLEL = 0

***************************************

SINGLE TABLE ACCESS PATH

Column:  OBJECT_ID  Col#: 4      Table: T1   Alias: T1

    NDV: 29915     NULLS: 0         DENS: 3.3428e-005 LO:  2  HI: 30857

    NO HISTOGRAM: #BKT: 1 #VAL: 2

  TABLE: T1     ORIG CDN: 588639  ROUNDED CDN: 20  CMPTD CDN: 20

  Access path: tsc  Resc:  771  Resp:  771

  Access path: index (equal)

      Index: IDX_T1

  TABLE: T1

      RSC_CPU: 0   RSC_IO: 23

  IX_SEL:  0.0000e+000  TB_SEL:  3.3428e-005

  BEST_CST: 23.00  PATH: 4  Degree:  1

 

SQL> select 8000/(1.6765*power(16,0.6581)) from dual;

 

8000/(1.6765*POWER(16,0.6581))

——————————

              769.582296540965

这里算出来的值为769.58,约等于770,跟trace文件中记录的实际计算结果差了1,这可能是受隐含参数_table_scan_cost_plus_one的影响,它的默认值是true

 

10.2.0.1AUX_STATS$里就有记录了,虽然这里我并没有通过dbms_stats.gather_system_stats来收集系统级别的统计信息。但它还是会记录Oracle认为目标数据库所在database server上关于系统级别统计信息的一些内部默认值:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> select name,value from sys.all_parameters where name like ‘%optimizer_cost_model%’;

 

NAME                           VALUE

—————————— ———-

_optimizer_cost_model          CHOOSE

 

 

SQL> select * from sys.aux_stats$;

 

SNAME                          PNAME                               PVAL1 PVAL2

—————————— —————————— ———- ——————————————————————————–

SYSSTATS_INFO                  STATUS                                    COMPLETED

SYSSTATS_INFO                  DSTART                                    08-30-2005 15:04

SYSSTATS_INFO                  DSTOP                                     08-30-2005 15:04

SYSSTATS_INFO                  FLAGS                                   1

SYSSTATS_MAIN                  CPUSPEEDNW                     484.974958

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM                                 

SYSSTATS_MAIN                  MREADTIM                                 

SYSSTATS_MAIN                  CPUSPEED                                 

SYSSTATS_MAIN                  MBRC                                     

SYSSTATS_MAIN                  MAXTHR                                   

SYSSTATS_MAIN                  SLAVETHR                                 

 

13 rows selected

 

SQL> show parameter multiblock;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_multiblock_read_count        integer     16

 

我们来看一下在10.2.0.1里,AUX_STATS$里有一些内部默认值的情况下Oracle如何计算全表扫描的成本:

*****************************

SYSTEM STATISTICS INFORMATION

*****************************

  Using NOWORKLOAD Stats

  CPUSPEED: 485 millions instruction/sec

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: T1  Alias: T1

    #Rows: 592951  #Blks:  8329  AvgRowLen:  93.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1305  #DK: 51868  LB/K: 1.00  DB/K: 11.00  CLUF: 592940.00

***************************************

SINGLE TABLE ACCESS PATH

  Column (#4): OBJECT_ID(NUMBER)

    AvgLen: 5.00 NDV: 51868 Nulls: 11 Density: 1.9280e-005 Min: 2 Max: 55096

  Table: T1  Alias: T1    

    Card: Original: 592951  Rounded: 11  Computed: 11.43  Non Adjusted: 11.43

  Access Path: TableScan

    Cost:  1860.68  Resp: 1860.68  Degree: 0

      Cost_io: 1824.00  Cost_cpu: 213483714

      Resp_io: 1824.00  Resp_cpu: 213483714

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 15.00  resc_cpu: 113902

    ix_sel: 1.9280e-005  ix_sel_with_filters: 1.9280e-005

    Cost: 15.02  Resp: 15.02  Degree: 1

  Best:: AccessPath: IndexRange  Index: IDX_T1

         Cost: 15.02  Degree: 1  Resp: 15.02  Card: 11.43  Bytes: 0

注意到上述trace文件中有这样一句话——“Using NOWORKLOAD Stats,这表示CPU cost model已经被启用了,只不过用来计算CPU cost的时候是用系统在没有负载情况下的系统统计信息,即用来计算CPU cost的是AUX_STATS$里的内部默认值。

从上述trace文件里可以看到,现在在表t18329个块的情况下,全表扫描的成本是1860.68,这其中IO Cost1824.00,它们之间的差值1860.681824.00就应该是CPU cost,这个剩下的CPU cost应该是基于Cost_cpu: 213483714和一个根据AUX_STATS$里的内部默认值而计算出来的,例如用213483714除以一个计算出来的CPU_FACTOR啥的。

在上述10.2.0.1里,db_file_multiblock_read_count同样是16,在表的块数差异不大的情况下(9.2.0.1800010.2.0.18329),全表扫描的成本却从771增加到1860.68

 

显然此时的MBDivisor就和DB_FILE_MULTIBLOCK_READ_COUNT没有关系了,也不再满足公式MBDivisor = 1.6765 * power(db_file_multiblock_read_count, 0.6581)

 

我们来算一下此时的MBDivisor:

此时的MBDivisor = Num Blocks / tsc cost

                = 8329 / 1860.68

                = 4.4763204849

这可比9.2.0.1中的10.3952要小太多。

 

我们再来看同一台机器上的11.2.0.1的情况:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> select name,value from sys.all_parameters where name like ‘%optimizer_cost_model%’;

 

NAME                           VALUE

—————————— ———-

_optimizer_cost_model          CHOOSE

 

可以看到默认值CPUSPEEDNW发生了变化,其他默认值跟10.2.0.1一样。

SQL> select * from sys.aux_stats$;

 

SNAME                          PNAME                               PVAL1 PVAL2

—————————— —————————— ———- ——————————————————————————–

SYSSTATS_INFO                  STATUS                                    COMPLETED

SYSSTATS_INFO                  DSTART                                    04-02-2010 14:19

SYSSTATS_INFO                  DSTOP                                     04-02-2010 14:19

SYSSTATS_INFO                  FLAGS                                   1

SYSSTATS_MAIN                  CPUSPEEDNW                     1683.65129

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM                                 

SYSSTATS_MAIN                  MREADTIM                                 

SYSSTATS_MAIN                  CPUSPEED                                 

SYSSTATS_MAIN                  MBRC                                     

SYSSTATS_MAIN                  MAXTHR                                   

SYSSTATS_MAIN                  SLAVETHR                                 

 

13 rows selected

 

db_file_multiblock_read_count的值也从16变成了128

SQL> show parameter multiblock;

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_file_multiblock_read_count        integer     128

 

我们来看一下在11.2.0.1里,AUX_STATS$里有一些内部默认值,但CPUSPEEDNW发生了变化的情况下Oracle如何计算全表扫描的成本:

—————————–

SYSTEM STATISTICS INFORMATION

—————————–

  Using NOWORKLOAD Stats

  CPUSPEEDNW: 1684 millions instructions/sec (default is 100)

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

  MBRC: -1 blocks (default is 8)

 

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: T1  Alias: T1

    #Rows: 575408  #Blks:  9103  AvgRowLen:  97.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1308  #DK: 71925  LB/K: 1.00  DB/K: 8.00  CLUF: 575400.00

Access path analysis for T1

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for T1[T1]

  Table: T1  Alias: T1

    Card: Original: 575408.000000  Rounded: 8  Computed: 8.00  Non Adjusted: 8.00

  Access Path: TableScan

    Cost:  2477.61  Resp: 2477.61  Degree: 0

      Cost_io: 2467.00  Cost_cpu: 214434308

      Resp_io: 2467.00  Resp_cpu: 214434308

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 11.00  resc_cpu: 83376

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    Cost: 11.00  Resp: 11.00  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_T1

         Cost: 11.00  Degree: 1  Resp: 11.00  Card: 8.00  Bytes: 0

注意到上述trace文件中同样有这样一句话——“Using NOWORKLOAD Stats,这表示CPU cost model已经被启用了,只不过用来计算CPU cost的时候是用系统在没有负载情况下的系统统计信息,即用来计算CPU cost的是AUX_STATS$里的内部默认值。

从上述trace文件里可以看到,现在在表t19103个块的情况下,全表扫描的成本是2477.61,这其中IO Cost2467.00

在上述11.2.0.1里,db_file_multiblock_read_count的值由16变成了128,在表的块数差异不大的情况下(10.2.0.1832911.2.0.19103),全表扫描的成本从1860.68增加到了2477.61

 

显然此时的MBDivisor也和DB_FILE_MULTIBLOCK_READ_COUNT没有关系,也不再满足公式MBDivisor = 1.6765 * power(db_file_multiblock_read_count, 0.6581)

 

我们来算一下此时的MBDivisor:

此时的MBDivisor = Num Blocks / tsc cost

                = 9103 / 2477.61

                = 3.6741052869

这也比9.2.0.1中的10.3952要小太多,同时也比10.2.0.1里的4.4763要小。

 

现在我用dbms_stats.delete_system_stats删掉系统统计信息:

SQL> exec dbms_stats.delete_system_stats();

 

PL/SQL procedure successfully completed

 

可以看到执行完后系统级别统计信息的一些内部默认值依然没有被删除,只不过这里CPUSPEEDNW发生了改变:

SQL> select * from sys.aux_stats$;

 

SNAME                          PNAME                               PVAL1 PVAL2

—————————— —————————— ———- ——————————————————————————–

SYSSTATS_INFO                  STATUS                                    COMPLETED

SYSSTATS_INFO                  DSTART                                    06-09-2012 23:25

SYSSTATS_INFO                  DSTOP                                     06-09-2012 23:25

SYSSTATS_INFO                  FLAGS                                   0

SYSSTATS_MAIN                  CPUSPEEDNW                        755.942

SYSSTATS_MAIN                  IOSEEKTIM                              10

SYSSTATS_MAIN                  IOTFRSPEED                           4096

SYSSTATS_MAIN                  SREADTIM                                 

SYSSTATS_MAIN                  MREADTIM                                 

SYSSTATS_MAIN                  CPUSPEED                                 

SYSSTATS_MAIN                  MBRC                                     

SYSSTATS_MAIN                  MAXTHR                                   

SYSSTATS_MAIN                  SLAVETHR                                 

 

13 rows selected

 

我们再来看对表t1执行同样sqlcost计算结果:

—————————–

SYSTEM STATISTICS INFORMATION

—————————–

  Using NOWORKLOAD Stats

  CPUSPEEDNW: 756 millions instructions/sec (default is 100)

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

  IOSEEKTIM: 10 milliseconds (default is 10)

  MBRC: -1 blocks (default is 8)

 

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: T1  Alias: T1

    #Rows: 575408  #Blks:  9103  AvgRowLen:  97.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1308  #DK: 71925  LB/K: 1.00  DB/K: 8.00  CLUF: 575400.00

Access path analysis for T1

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for T1[T1]

  Table: T1  Alias: T1

    Card: Original: 575408.000000  Rounded: 8  Computed: 8.00  Non Adjusted: 8.00

  Access Path: TableScan

    Cost:  2490.64  Resp: 2490.64  Degree: 0

      Cost_io: 2467.00  Cost_cpu: 214434308

      Resp_io: 2467.00  Resp_cpu: 214434308

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 11.00  resc_cpu: 83376

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    Cost: 11.01  Resp: 11.01  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_T1

         Cost: 11.01  Degree: 1  Resp: 11.01  Card: 8.00  Bytes: 0

trace文件里可以看到全表扫描的成本从2477.61增加到了2490.64,这是可以理解的,CPUSPEEDNW的值降低了,进而根据这些值算出来的CPU_FACTOR也会减少,而Cost_cpu的值没变(Cost_cpu: 214434308),所以算出来的CPU cost(可以近似看作CPU cost = Cost_cpu/CPU_FACTOR)会增加,进而总的全表扫描的成本会增加。

 

我们再来测一下针对同样的11.2.0.1,当我把CPU cost model禁掉后的情况:

SQL> oradebug setmypid

已处理的语句

SQL> oradebug unlimit

SQL> alter session set “_optimizer_cost_model”=’IO’;

 

会话已更改。

 

SQL> oradebug event 10053 trace name context forever, level 1

已处理的语句

SQL> explain plan for select * from scott.t1 where object_id=999;

 

已解释。

 

SQL> oradebug tracefile_name

c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5256.trc

SQL> oradebug event 10053 trace name context off

已处理的语句

 

我们来看一下产生的trace文件的内容:

***************************************

PARAMETERS USED BY THE OPTIMIZER

********************************

  *************************************

  PARAMETERS WITH ALTERED VALUES

  ******************************

Compilation Environment Dump

_optimizer_cost_model               = io

 

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: T1  Alias: T1

    #Rows: 575408  #Blks:  9103  AvgRowLen:  97.00

Index Stats::

  Index: IDX_T1  Col#: 4

    LVLS: 2  #LB: 1308  #DK: 71925  LB/K: 1.00  DB/K: 8.00  CLUF: 575400.00

Access path analysis for T1

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for T1[T1]

  Table: T1  Alias: T1

    Card: Original: 575408.000000  Rounded: 8  Computed: 8.00  Non Adjusted: 8.00

  Access Path: TableScan

    Cost:  1383.00  Resp: 1383.00  Degree: 0

      Cost_io: 1383.00  Cost_cpu: 0

      Resp_io: 1383.00  Resp_cpu: 0

  Access Path: index (AllEqRange)

    Index: IDX_T1

    resc_io: 11.00  resc_cpu: 0

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    Cost: 11.00  Resp: 11.00  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_T1

         Cost: 11.00  Degree: 1  Resp: 11.00  Card: 8.00  Bytes: 0

 

Optimizer state dump:

Compilation Environment Dump

optimizer_mode_hinted               = false

……省略显示相关内容

optimizer_features_enable           = 11.2.0.1

……省略显示相关内容

_db_file_optimizer_read_count       = 8

……省略显示相关内容

db_file_multiblock_read_count       = 128

 

从上述文件里我们可以看到现在的全表扫描成本为1383.00,而且确实没有CPU cost了。

注意,这个时候计算MBDivisor就不是用db_file_multiblock_read_count的值了,而是会用隐含参数_db_file_optimizer_read_count

 

11.2.0.1里计算MBDivisor使用如下公式:

MBDivisor = 1.6765 * power(_db_file_optimizer_read_count, 0.6581)

 

我们来计算一下上述全表扫描的成本:

tsc cost = Num Blocks / MBDivisor

       = 9103 / (1.6765 * power(8, 0.6581))

       = 1381.8390

 

SQL> select 9103/(1.6765 * power(8, 0.6581)) from dual;

 

9103/(1.6765*POWER(8,0.6581))

—————————–

             1381.83905981934

 

1381.8390约等于1382,又因为_table_scan_cost_plus_one11.2.0.1里也是true,所以最终的全表扫描的成本值要加1,即应该是1383,刚好和trace文件里的结果一致。

 

最后,我们来总结一下这篇文章的结论:

1、在9i的时候虽然已经引入CPU cost model,但因为aux_stats$里并没有记录默认值,所以9i的全表扫描的成本在默认情况下(_optimizer_cost_model的值为CHOOSE)实际上全部是IO Cost,并没有包含CPU Cost;此时全表扫描成本的计算公式为:

tsc cost = Num Blocks / MBDivisor

= Num Blocks/1.6765 * power(db_file_multiblock_read_count, 0.6581)

这时全表扫描的成本会倚赖于参数db_file_multiblock_read_count的值;

 

2、在10g11gaux_stats$已经有默认值即相当于CPU cost model在默认情况下已被开启那么现在全表扫描的成本就和参数db_file_multiblock_read_count的值就没有关系了;

 

3、如果你在11g中禁用CPU cost model,采用原先旧的IO cost model,则全表扫描成本的计算公式变为:

tsc cost = Num Blocks / MBDivisor

= Num Blocks/1.6765 * power(_db_file_optimizer_read_count, 0.6581)

即这种情况下全表扫描的成本依然和参数db_file_multiblock_read_count的值没有关系。



Leave a Reply

Your email address will not be published. Required fields are marked *