This was first published on https://blog.dbi-services.com/oracle-system-statistics-display-auxstats-with-calculated-values-and-formulas (2014-10-15)
Republishing here for new followers. The content is related to the the versions available at the publication date
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/SREADTIMYou 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/SREADTIMhere all values are explicitely set
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/SREADTIMAnd 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/SREADTIMHere 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.
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.
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.
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模式的信息,附在文章末尾 […]