.
\nSystem 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.<\/p>\n
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.<\/p>\n
With WORKLOAD statistics, the SREADTIM and MREADTIM as well as MBRC are measured and those are the ones that are used by the optimizer.<\/p>\n
Here is my script:<\/p>\n
set echo off\nset linesize 200 pagesize 1000\ncolumn pname format a30\ncolumn sname format a20\ncolumn pval2 format a20\nselect pname,pval2 from sys.aux_stats$ where sname='SYSSTATS_INFO';\nselect pname,pval1,calculated,formula from sys.aux_stats$ where sname='SYSSTATS_MAIN'\nmodel\n reference sga on (\n select name,value from v$sga \n ) dimension by (name) measures(value)\n reference parameter on (\n select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'\n union all\n select name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'\n union all\n select name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'\n ) dimension by (name) measures(value)\npartition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated,cast(null as varchar2(60)) as formula) rules(\n calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),\n calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) \/ pval1['IOTFRSPEED']),\n calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] \/ pval1['IOTFRSPEED']),\n calculated[' multi block Cost per block']=round(1\/calculated['MBRC']*calculated['MREADTIM']\/calculated['SREADTIM'],4),\n calculated[' single block Cost per block']=1,\n 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,\n formula['MREADTIM']=case when pval1['MREADTIM'] is null then '= IOSEEKTIM + db_block_size * MBRC \/ IOTFRSPEED' end,\n formula['SREADTIM']=case when pval1['SREADTIM'] is null then '= IOSEEKTIM + db_block_size \/ IOTFRSPEED' end,\n formula[' multi block Cost per block']='= 1\/MBRC * MREADTIM\/SREADTIM',\n formula[' single block Cost per block']='by definition',\n calculated[' maximum mbrc']=sga.value['Database Buffers']\/(parameter.value['db_block_size']*parameter.value['sessions']),\n formula[' maximum mbrc']='= buffer cache size in blocks \/ sessions'\n);\nset echo on<\/code><\/pre>\nHere is an exemple with default statistics<\/strong>:<\/p>\nPNAME PVAL1 CALCULATED FORMULA\n------------------------------ ---------- ---------- --------------------------------------------------\nCPUSPEEDNW 1519\nIOSEEKTIM 10\nIOTFRSPEED 4096\nSREADTIM 12 = IOSEEKTIM + db_block_size \/ IOTFRSPEED\nMREADTIM 26 = IOSEEKTIM + db_block_size * MBRC \/ IOTFRSPEED\nCPUSPEED\nMBRC 8 = _db_file_optimizer_read_count\nMAXTHR\nSLAVETHR\n maximum mbrc 117.152542 = buffer cache size in blocks \/ sessions\n single block Cost per block 1 by definition\n multi block Cost per block .2708 = 1\/MBRC * MREADTIM\/SREADTIM\n<\/code><\/pre>\nYou 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.<\/p>\n
Here is an example with workload system statistics<\/strong> gathering:<\/p>\nPNAME PVAL1 CALCULATED FORMULA\n------------------------------ ---------- ---------- --------------------------------------------------\nCPUSPEEDNW 1511\nIOSEEKTIM 15\nIOTFRSPEED 4096\nSREADTIM 1.178 1.178\nMREADTIM .03 .03\nCPUSPEED 3004\nMBRC 8 8 MBRC\nMAXTHR 6861824\nSLAVETHR\n maximum mbrc 114.983051 = buffer cache size in blocks \/ sessions\n single block Cost per block 1 by definition\n multi block Cost per block .0032 = 1\/MBRC * MREADTIM\/SREADTIM\n<\/code><\/pre>\nhere all values are explicitly set (added after comment from Raul: those are probably completely wrong as MREADTIM should be > SREADTIM)<\/p>\n
And an example with exadata system statistics<\/strong> that defines noworkload values and sets also the MBRC (see Chris Antognini post<\/a> about it)<\/p>\nPNAME PVAL1 CALCULATED FORMULA\n------------------------------ ---------- ---------- --------------------------------------------------\nCPUSPEEDNW 1539\nIOSEEKTIM 16\nIOTFRSPEED 204800\nSREADTIM 16.04 = IOSEEKTIM + db_block_size \/ IOTFRSPEED\nMREADTIM 18.28 = IOSEEKTIM + db_block_size * MBRC \/ IOTFRSPEED\nCPUSPEED\nMBRC 57 57 MBRC\nMAXTHR\nSLAVETHR\n maximum mbrc 114.983051 = buffer cache size in blocks \/ sessions\n single block Cost per block 1 by definition\n multi block Cost per block .02 = 1\/MBRC * MREADTIM\/SREADTIM\n<\/code><\/pre>\n