Infrastructure at your Service

Franck Pachot

Oracle system statistics: Display AUX_STATS$ with calculated values and formulas

By October 15, 2014 Oracle 9 Comments

By Franck Pachot

.
System statistics can be gathered in NOWORKLOAD or WORKLOAD mode. Different values will be set depending on that and the others will be calculated – derived from them. We can see defined values from SYS.AUX_STATS$ but here is a script that shows the calculated ones as well.

With no system statistics or NOWORKLOAD the values of IOSEEKTIM (latency in ms) and IOTFRSPEED (transfer in bytes/ms) are set and the SREADTIM (time to read 1 block in ms) and MREADTIM (for multiblock read) are calculated from them. MBRC depends on the defaults or the db_file_multiblock_read_count settings.

With WORKLOAD statistics, the SREADTIM and MREADTIM as well as MBRC are measured and those are the ones that are used by the optimizer.

Here is my script:
set echo off
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 on

Here is an exemple with default statistics:

PNAME PVAL1 CALCULATED FORMULA
------------------------------ ---------- ---------- --------------------------------------------------
CPUSPEEDNW 1519
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 12 = IOSEEKTIM + db_block_size / IOTFRSPEED
MREADTIM 26 = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED
CPUSPEED
MBRC 8 = _db_file_optimizer_read_count
MAXTHR
SLAVETHR
maximum mbrc 117.152542 = buffer cache size in blocks / sessions
single block Cost per block 1 by definition
multi block Cost per block .2708 = 1/MBRC * MREADTIM/SREADTIM

You see the calculated values for everything. Note the ‘maximum mbrc’ which limits the multiblock reads when the buffer cache is small. It divides the buffer cache size (at startup – can depend on ASMM and AMM settings) by the sessions parameter.

Here is an example with workload system statistics gathering:
PNAME PVAL1 CALCULATED FORMULA
------------------------------ ---------- ---------- --------------------------------------------------
CPUSPEEDNW 1511
IOSEEKTIM 15
IOTFRSPEED 4096
SREADTIM 1.178 1.178
MREADTIM .03 .03
CPUSPEED 3004
MBRC 8 8 MBRC
MAXTHR 6861824
SLAVETHR
maximum mbrc 114.983051 = buffer cache size in blocks / sessions
single block Cost per block 1 by definition
multi block Cost per block .0032 = 1/MBRC * MREADTIM/SREADTIM

here all values are explicitly set (added after comment from Raul: those are probably completely wrong as MREADTIM should be > SREADTIM)

And an example with exadata system statistics that defines noworkload values and sets also the MBRC (see Chris Antognini post about it)
PNAME PVAL1 CALCULATED FORMULA
------------------------------ ---------- ---------- --------------------------------------------------
CPUSPEEDNW 1539
IOSEEKTIM 16
IOTFRSPEED 204800
SREADTIM 16.04 = IOSEEKTIM + db_block_size / IOTFRSPEED
MREADTIM 18.28 = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED
CPUSPEED
MBRC 57 57 MBRC
MAXTHR
SLAVETHR
maximum mbrc 114.983051 = buffer cache size in blocks / sessions
single block Cost per block 1 by definition
multi block Cost per block .02 = 1/MBRC * MREADTIM/SREADTIM

And finally here is a workload system statistics result but with explicitly setting the db_file_multiblock_read_count to 128:
PNAME PVAL1 CALCULATED FORMULA
------------------------------ ---------- ---------- --------------------------------------------------
CPUSPEEDNW 1539
IOSEEKTIM 15
IOTFRSPEED 4096
SREADTIM 17 = IOSEEKTIM + db_block_size / IOTFRSPEED
MREADTIM 271 = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED
CPUSPEED
MBRC 128 db_file_multiblock_read_count
MAXTHR
SLAVETHR
maximum mbrc 114.983051 = buffer cache size in blocks / sessions
single block Cost per block 1 by definition
multi block Cost per block .1245 = 1/MBRC * MREADTIM/SREADTIM

Here you see that the MBRC in noworkload is coming from the value which is set by the db_file_multiblock_read_count rather from the value 8 which is used by default by the optimizer when it is not set. And the MREADTIM is calculated from that i/o size

For more historical information about system statistics and how multiblock reads are costed (index vs. full table scan choice) see my article on latest OracleScene

As usual, if you find anything to improve in that script, please share.

 

9 Comments

  • Lauri says:

    Hi Frank,

    Interesant article. But what does db_file_multiblock_read_count has to do with Exadata? As I understood, that the “smart scans” mechanism does not use the multi-blocks read.
    What do mean the parameters CPUSPEEDNW,… and how to determine if they are correctly sized?

    Kind Regards

     
  • Hi lauri,
    db_file_multiblock_read_count is still used in exadata smartscan because it defines the size of the direct path reads. But here it is only about the optimizer. The MBRC is used by the optimizer to cost the full table scan.
    chris Antognini blog details that but I see that the link has disappeared. I’ll fix that.

     
  • Dean says:

    Hi Franck

    How important is gathering System Stats, seems we have been using default values from year dot?

    Dean

     
  • Hi Dean,
    The most important thing is that the ratio between single block read and multiblock read is ok, because the choice between full scan and index access is based on that.
    Defaults are usually ok. They cost single block reads to 12 milliseconds and multi-block reads to 26, which is probably fine.
    If you have a storage with very low latency but same bandwidth (SSD for example) then accurate system statistics may be better and favor index access over full table scans because this storage is optimal for that.
    Regards,
    Franck.

     
  • Cyrille says:

    Hi Franck,

    My db_file_multiblock_read_count is currently at 128, I gathered a system workload statistics which indicates
    MBRC=49
    Should I set my parameter to this value?

    Thanks

    Cyrille

     
    • Hi Cyrille,
      Sorry for the late reply…
      There is no reason to set db_file_multiblock_read_count there. Just keep the default. You will be able to do up to 1MB reads. But optimizer will count on 49 which is the observed average.

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

     
  • Raul Kaubi says:

    Hi

    I see you stated that:
    “The most important thing is that the ratio between single block read and multiblock read is ok, because the choice between full scan and index access is based on that.”

    While looking default values:
    SREADTIM 12
    MREADTIM 26

    Then after statistics gathering, values are totally different, which messes up ratio between these two values big time:
    SREADTIM 1.178
    MREADTIM .03

    Don’t this cause a problem..?

    Regards
    Raul

     

Leave a Reply

Franck Pachot
Franck Pachot

Technology Leader