default noworkload table full scan cost

从oracle 10g开始,有三种不同的system statistics模式(即CPU cost)可以供我们选择。
1.默认的noworkload system statistics
2.以noworkload模式收集system statistics
3.以workload模式收集system statistics

本文是针对第一种模式。
测试环境信息:
Oracle Enterprise Linux x86_64 11.2.0.4.7 RAC
Blocksize 8k,MSSM local management

system statistics

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
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR
SQL> drop table t1 purge;

Table dropped.

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> alter session set "_table_scan_cost_plus_one" = false;  ---turn off _table_scan_cost_plus_one

Session altered.

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

SQL> show parameter db_file_multiblock_read_count

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- -------------
db_file_multiblock_read_count                                integer     128


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

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

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

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

SQL> set autotrace off


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

Explained.

SQL> select cpu_cost from plan_table;

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

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

公式中各个部分的解释如下:
#SRds – number of single block reads(单数据块读的次数)
#MRds – number of multi block reads(多数据块读的次数)
#CPUCyles – number of CPU cycles(完成查询所需要发出的CPU指令数)
sreadtim – single block read time(一次多数据块读的时间)
mreadtim – multi block read time(一次多数据块读的时间)
cpuspeed – CPU cycles per second(CPU的处理速度)

计算过程
上述SQL查询使用的是全表扫描,所以#SRDS为0

在non-workload模式下
#MRds为TABBLKS/MBRC=block/db_file_multiblock_read_count = 10000/128
mreadtim= ioseektim + mbrc*blocksize/ iotftspeed = ioseektim+db_file_multiblock_count*db_block_size/iotftspeed = 10+128*8192/4096=266
sreadtim=ioseektim+db_block_size/iotfrspeed = 10 + 8192/4096 = 12
cpuspeed=cpuspeednw=1360

TABBLKS:表在HWM下的数据块数
MBRC:多数据块读的一次读取的数据块数,Multi_Block_Read_Count;
IOSEEKTIM:IO寻址时间,System Statistics中给出,默认为10ms
IOTFRSPEED:IO传输速度,System Statistics中给出,默认为4096字节/ms。

所以
COST = (#MRds * mreadtim + CPUCycles /cpuspeed) / sreadtime  
     = 10000/128*266/12 + 72914400/1000/1360/12

SQL> select 10000/128*266/12 + 72914400/1000/1360/12 from dual;

10000/128*266/12+72914400/1000/1360/12
--------------------------------------
                            1736.23863

与输出中的cost一致。

NOTE:
可以使用nocpu_costingcpu_costing hint来disable system statistics
或者修改optimizer_features_enable

SQL> alter session set "optimizer_features_enable" = '9.2.0';

Session altered.

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

SQL> select max(val) from t1;

  MAX(VAL)
----------
       369

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

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |   246 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |       |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |   246 |
-----------------------------------------------------------

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

从Oracle 10gR2之后,在进行多块读的时候,优化器用于COST计算所使用的MBRC与数据库在实际运行中使用的MBRC是不同的。
在10.2中默认的db_file_multiblock_read_count是8,在不同的操作系统下可能使用最大化的I/O读取,实际上是128*8K=1MB,这一特性在10.2后由隐含参数_db_file_exec_read_count控制。

11.2.0.4和12.1.0.2

SQL> @pd _db_file_exec_read_count
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                   VALUE                          DESCRIPTION
---------- ----- ------------------------------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------
      1603   643 _db_file_exec_read_count                               128                            multiblock read count for regular clients

pd.sql脚本来自tanel poder,用于显示隐含参数。

此条目发表在CBO分类目录。将固定链接加入收藏夹。

One Response to default noworkload table full scan cost

  1. Leo说道:
    select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') + (select value
              from v$parameter
             where name = 'db_file_multiblock_read_count') *
           (select value from v$parameter where name = 'db_block_size') /
           (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
    from dual;
    
    select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
           (select value from v$parameter where name = 'db_block_size') /
           (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
    from dual;
    

发表评论

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