. On a test database that has no activity, I\u2019m creating a 1000 blocks table. My goal is to gather WORKLOAD system statistics during a simple table full scan on that table, and see how it calculates SREADTIM, MREADTIM and MBRC.<\/p>\n Then I run a simple select between the calls to \u2018start\u2019 and \u2018stop\u2019 procedures of the dbms_stats WORKLOAD system stats gathering.<\/p>\n I check the physical read statistics (this is why have reconnected my session so that I can query v$mystat without doing the delta)<\/p>\n I\u2019ve read 1000 blocks in 15 i\/o calls so I’m sure it is multiblock reads. All of them (1000 x 8k) was eligible for SmartScan and those 1000 blocks have been processed by the storage cell.<\/p>\n If you wonder why I have only 7 ‘physical read total multi block requests’ it’s because it accounts only the ‘full’ multiblock reads – not those that are limited by extent boundary. See here<\/a> for that analysis.<\/p>\n If you wonder why I have only 22 ‘physical read total IO requests’ then I’ve not the answer. The sql_trace shows only the 15 ‘direct path read’. And dbms_stats counts only the ‘physical read IO requests’. If you have any idea, please comment.<\/p>\n I stop my WORKLOAD statistics gathering:<\/p>\n And check the system statistics that have been set:<\/p>\n I have no SREADTIM which is expected as I’ve done only multiblock reads. I have a MREADTIM. But I don’t have the MBRC set.<\/p>\n Let’s do the same after disabling serial direct-path reads:<\/p>\n I do the same as before, but now my session stats show only conventional reads:<\/p>\n and here are the gathered stats:<\/p>\n Now the MBRC is set with the gathered value.<\/p>\n This proves that MBRC is set only for conventional multiblock reads. Direct-path reads are not accounted.<\/p>\n If you are on Exadata, you probably want to benefit from SmartScan. Then you probably want the CBO to choose FULL TABLE SCAN which will do direct-path reads for large tables (according that they don’t have a lot of updated buffers in SGA). If you gather WORKLOAD statistics they will set MBRC without accounting for those direct-path reads and it will probably be set lower than the average actual multiblock read (which – in direct-path reads – is close the the db_file_multiblock_read – default or set value).<\/p>\n This is the reason why Oracle introduced the EXADATA mode: it sets the MBRC from the db_file_multiblock_read value. An alternative to EXADATA mode can be setting those values as NOWORKLOAD statistics and keep the db_file_multiblock_read_count set. You will have the same behavior because CBO uses db_file_multiblock_read_count when it is set and there are no MBRC system stats. But the danger is that if someone resets the db_file_multiblock_read_count (and I often advise to keep defaults) then the CBO will use a value of 8 and that will probably increase the cost of full table scans too much.<\/p>\n All formulas are here with a script that shows what is used by the CBO.<\/p>\n Well, that blog post title is too extreme because:<\/p>\n @FranckPachot<\/a> @kevinclosson<\/a> @fritshoogland<\/a> @moustafa_dba<\/a> @JLOracle<\/a> never say never \ud83d\ude09<\/p>\n
\nFor Exadata, oracle has introduced an ‘EXADATA’ mode which sets a high transfer rate (with IOTFRSPEED as in NOWORKLOAD statistics) and set a MBRC (as in WORKLOAD statistics). Those values are set rather than gathered because all the SmartScan optimization done at storage cell level, which makes the multiblock reads less expensive, is difficult to measure from the database.
\nHere I will explain what I stated in a previous blog<\/a>: direct-path reads are not counted as multiblock reads for the MBRC system statistic<\/strong>. And direct-path read should be the main i\/o path in Exadata as you probably bought that machine to benefit from SmartScan.<\/p>\nWith direct-path reads<\/h3>\n
SQL> connect demo\/demo\nConnected.\n\nSQL> drop table DEMO;\nTable dropped.\n\nSQL> create table DEMO pctfree 99 as select rpad('x',1000,'x') n from dual connect by level <=1000;\nTable created.\n<\/code><\/pre>\nSQL> exec dbms_stats.gather_system_stats('start');\nPL\/SQL procedure successfully completed.\n\nSQL> connect demo\/demo\nConnected.\n\nSQL> select count(*) from DEMO;\n\n COUNT(*)\n----------\n 1000\n\n<\/code><\/pre>\nSQL> select name,value from v$mystat join v$statname using(statistic#) where (name like 'phy%' or name like 'cell%') and value>0;\n\nNAME VALUE\n------------------------------------------------------------ ----------\nphysical read total IO requests 22\nphysical read total multi block requests 7\nphysical read total bytes 8306688\ncell physical IO interconnect bytes 8306688\nphysical reads 1000\nphysical reads direct 1000\nphysical read IO requests 15\nphysical read bytes 8192000\ncell scans 1\ncell blocks processed by cache layer 1000\ncell blocks processed by txn layer 1000\ncell blocks processed by data layer 1000\ncell physical IO bytes eligible for predicate offload 8192000\ncell physical IO interconnect bytes returned by smart scan 130760\ncell IO uncompressed bytes 8192000\n<\/code><\/pre>\nSQL> exec dbms_stats.gather_system_stats('stop');\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nSQL> select * from sys.aux_stats$;\n\nSNAME PNAME PVAL1\n--------------- ---------- ----------\nSYSSTATS_INFO STATUS\nSYSSTATS_INFO DSTART\nSYSSTATS_INFO DSTOP\nSYSSTATS_INFO FLAGS 1\nSYSSTATS_MAIN CPUSPEEDNW 2300\nSYSSTATS_MAIN IOSEEKTIM 10\nSYSSTATS_MAIN IOTFRSPEED 4096\nSYSSTATS_MAIN SREADTIM\nSYSSTATS_MAIN MREADTIM .151\nSYSSTATS_MAIN CPUSPEED 2300\nSYSSTATS_MAIN MBRC\nSYSSTATS_MAIN MAXTHR\nSYSSTATS_MAIN SLAVETHR\n<\/code><\/pre>\nWith conventional (aka buffered) reads<\/h3>\n
SQL> alter session set \"_serial_direct_read\"=never;\nSession altered.\n<\/code><\/pre>\nNAME VALUE\n------------------------------------------------------------ ----------\nphysical read total IO requests 44\nphysical read total multi block requests 28\nphysical read total bytes 8192000\ncell physical IO interconnect bytes 8192000\nphysical reads 1000\nphysical reads cache 1000\nphysical read IO requests 44\nphysical read bytes 8192000\nphysical reads cache prefetch 956\n\n<\/code><\/pre>\nSNAME PNAME PVAL1\n--------------- ---------- ----------\nSYSSTATS_INFO STATUS\nSYSSTATS_INFO DSTART\nSYSSTATS_INFO DSTOP\nSYSSTATS_INFO FLAGS 1\nSYSSTATS_MAIN CPUSPEEDNW 2300\nSYSSTATS_MAIN IOSEEKTIM 10\nSYSSTATS_MAIN IOTFRSPEED 4096\nSYSSTATS_MAIN SREADTIM\nSYSSTATS_MAIN MREADTIM .028\nSYSSTATS_MAIN CPUSPEED 2300\nSYSSTATS_MAIN MBRC 23\nSYSSTATS_MAIN MAXTHR\nSYSSTATS_MAIN SLAVETHR\n\n<\/code><\/pre>\nConclusion<\/h3>\n
\nThey also set the IOTFRSPEED to a high value because gathering MREADTIM will probably get a very low value – lower than SREADTIM – thanks to the SmartScan. And CBO ignores values where MREADTIM is less than SREADTIM.<\/p>\nNever say never<\/h3>\n