workload mode table full scan cost

本文手动设置workload模式的system statistics。

Oracle Enterprise Linux x86_64 11.2.0.4.7 RAC 表空间为8k,手动管理 local management。
当MBRC的值不为0的时候,系统采用的是WORKLOAD MODE。
实验的脚本来自jonathan的cost based oracle fundemental一书。

demo output

SQL> begin
  2     begin           execute immediate 'purge recyclebin';
  3     exception       when others then null;
  4     end;
  5
  6     dbms_stats.set_system_stats('CPUSPEED',2000);
  7     dbms_stats.set_system_stats('SREADTIM',10);
  8     dbms_stats.set_system_stats('MREADTIM',20);
  9     dbms_stats.set_system_stats('MBRC',12);
 10
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------
SYSSTATS_MAIN                  CPUSPEEDNW                           1360
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                               10
SYSSTATS_MAIN                  MREADTIM                               20
SYSSTATS_MAIN                  CPUSPEED                             2000
SYSSTATS_MAIN                  MBRC                                   12
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

9 rows selected.

SQL> drop table t1 purge;

Table dropped.

SQL>
SQL> create table t1
  2  pctfree 99
  3  pctused 1
  4  as
  5  with generator as (
  6     select  --+ materialize
  7             rownum  id
  8     from    all_objects
  9     where   rownum <= 3000
 10  )
 11  select
 12     /*+ ordered use_nl(v2) */
 13     rownum                                  id,
 14     trunc(100 * dbms_random.normal)         val,
 15     rpad('x',100)                           padding
 16  from
 17     generator       v1,
 18     generator       v2
 19  where
 20     rownum <= 10000  21  ; 
Table created. 

SQL>
SQL> begin
  2     dbms_stats.gather_table_stats(
  3             user,
  4             't1',
  5             cascade => true,
  6             estimate_percent => null,
  7             method_opt => 'for all columns size 1'
  8     );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks from user_tables;

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
T1                                  10000      10000

SQL> explain plan for select max(val) from t1;

Explained.

SQL> select cpu_cost from plan_table;

  CPU_COST
----------
  72914400

SQL> alter session set "_table_scan_cost_plus_one" = false;

Session altered.

SQL>  set autotrace traceonly explain;
SQL>
SQL>
SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  1671   (1)| 00:00:17 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  1671   (1)| 00:00:17 |
---------------------------------------------------------------------------

COST的计算模型为
Cost = (#SRds * sreadtim + #MRds * mreadtim + CPUCycles /cpuspeed) / sreadtime

公式中各个部分的解释如下:
#SRds – number of single block reads
#MRds – number of multi block reads = BLOCK/MBRC
#CPUCyles – number of CPU cycles
sreadtim – single block read time
mreadtim – multi block read time
cpuspeed – CPU cycles per second

上述SQL查询使用的是全表扫描,所以#SRDS为0
在workload模式下
#MRds为BLOCKS/MBRC = 10000/12

所以
COST = (#MRds * mreadtim + CPUCycles /cpuspeed) / sreadtime
=BLOCKS/MBRC * mreadtim + CPUCycles /cpuspeed) / sreadtime
= (10000/12*20 + 72914400/2000/1000)/10
= 1670.31239
取ceil

SQL> select ceil((10000/12*20 + 72914400/2000/1000)/10) from dual;

CEIL((10000/12*20+72914400/2000/1000)/10)
-----------------------------------------
                                     1671

与执行计划中显示的COST一致。

WORKLOAD模式下的成本计算
COST = (#MRds * mreadtim + CPUCycles /cpuspeed) / sreadtime
=BLOCKS/MBRC * mreadtim + CPUCycles /cpuspeed) / sreadtime

模型中只涉及到的参数与数据库的多块读是没有关系的。下面测试下db_file_multiblock_count的更改是否会对执行计划的cost产生影响。

SQL> alter session set "_table_scan_cost_plus_one" = false;

Session altered.

SQL> alter session set db_file_multiblock_read_count = 16;

Session altered.

SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  1671   (1)| 00:00:17 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  1671   (1)| 00:00:17 |
---------------------------------------------------------------------------

SQL> alter session set db_file_multiblock_read_count = 64;

Session altered.

SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  1671   (1)| 00:00:17 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  1671   (1)| 00:00:17 |
---------------------------------------------------------------------------

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

SQL> select max(val) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |  1671   (1)| 00:00:17 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  1671   (1)| 00:00:17 |
---------------------------------------------------------------------------

SQL> set autotrace off

update

Franck Pachot 写了一个很好的脚本来获取workload模式的信息,附在文章末尾

set echo on
set linesize 200 pagesize 1000
column pname format a30
column sname format a20
column pval2 format a20
select pname,pval2 from sys.aux_stats$ where sname='SYSSTATS_INFO';
select pname,pval1,calculated,formula from sys.aux_stats$ where sname='SYSSTATS_MAIN'
model
  reference sga on (
    select name,value from v$sga
        ) dimension by (name) measures(value)
  reference parameter on (
    select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'
    union all
    select name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'
    union all
    select name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'
        ) dimension by (name) measures(value)
partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated,cast(null as varchar2(60)) as formula) rules(
  calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),
  calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),
  calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),
  calculated['   multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),
  calculated['   single block Cost per block']=1,
  formula['MBRC']=case when pval1['MBRC'] is not null then 'MBRC' when parameter.value['db_file_multiblock_read_count'] is not null then 'db_file_multiblock_read_count' when parameter.value['_db_file_optimizer_read_count'] is not null then '_db_file_optimizer_read_count' else '= _db_file_optimizer_read_count' end,
  formula['MREADTIM']=case when pval1['MREADTIM'] is null then '= IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED' end,
  formula['SREADTIM']=case when pval1['SREADTIM'] is null then '= IOSEEKTIM + db_block_size        / IOTFRSPEED' end,
  formula['   multi block Cost per block']='= 1/MBRC * MREADTIM/SREADTIM',
  formula['   single block Cost per block']='by definition',
  calculated['   maximum mbrc']=sga.value['Database Buffers']/(parameter.value['db_block_size']*parameter.value['sessions']),
  formula['   maximum mbrc']='= buffer cache size in blocks / sessions'
);
set echo off

针对本文的参数设置,输出如下

PNAME                               PVAL1 CALCULATED FORMULA
------------------------------ ---------- ---------- ------------------------------------------------------------
CPUSPEEDNW                           1360
IOSEEKTIM                               3
IOTFRSPEED                           4096
SREADTIM                               10         10
MREADTIM                               20         20
CPUSPEED                             2000
MBRC                                   12         12 MBRC
MAXTHR
SLAVETHR
   maximum mbrc                                  640 = buffer cache size in blocks / sessions
   single block Cost per block                     1 by definition
   multi block Cost per block                  .1667 = 1/MBRC * MREADTIM/SREADTIM
此条目发表在CBO分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s