By Franck Pachot

.
For 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.
Here I will explain what I stated in a previous blog: direct-path reads are not counted as multiblock reads for the MBRC system statistic. And direct-path read should be the main i/o path in Exadata as you probably bought that machine to benefit from SmartScan.

With direct-path reads

On a test database that has no activity, I’m 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.

SQL> connect demo/demo
Connected.

SQL> drop table DEMO;
Table dropped.

SQL> create table DEMO pctfree 99 as select rpad('x',1000,'x') n from dual connect by level <=1000;
Table created.

Then I run a simple select between the calls to ‘start’ and ‘stop’ procedures of the dbms_stats WORKLOAD system stats gathering.

SQL> exec dbms_stats.gather_system_stats('start');
PL/SQL procedure successfully completed.

SQL> connect demo/demo
Connected.

SQL> select count(*) from DEMO;

  COUNT(*)
----------
      1000

I check the physical read statistics (this is why have reconnected my session so that I can query v$mystat without doing the delta)

SQL> select name,value from v$mystat join v$statname using(statistic#) where (name like 'phy%' or name like 'cell%') and value>0;

NAME                                                              VALUE
------------------------------------------------------------ ----------
physical read total IO requests                                      22
physical read total multi block requests                              7
physical read total bytes                                       8306688
cell physical IO interconnect bytes                             8306688
physical reads                                                     1000
physical reads direct                                              1000
physical read IO requests                                            15
physical read bytes                                             8192000
cell scans                                                            1
cell blocks processed by cache layer                               1000
cell blocks processed by txn layer                                 1000
cell blocks processed by data layer                                1000
cell physical IO bytes eligible for predicate offload           8192000
cell physical IO interconnect bytes returned by smart scan       130760
cell IO uncompressed bytes                                      8192000

I’ve 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.

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 for that analysis.

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.

I stop my WORKLOAD statistics gathering:

SQL> exec dbms_stats.gather_system_stats('stop');
PL/SQL procedure successfully completed.

And check the system statistics that have been set:

SQL> select * from sys.aux_stats$;

SNAME           PNAME           PVAL1
--------------- ---------- ----------
SYSSTATS_INFO   STATUS
SYSSTATS_INFO   DSTART
SYSSTATS_INFO   DSTOP
SYSSTATS_INFO   FLAGS               1
SYSSTATS_MAIN   CPUSPEEDNW       2300
SYSSTATS_MAIN   IOSEEKTIM          10
SYSSTATS_MAIN   IOTFRSPEED       4096
SYSSTATS_MAIN   SREADTIM
SYSSTATS_MAIN   MREADTIM         .151
SYSSTATS_MAIN   CPUSPEED         2300
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

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.

With conventional (aka buffered) reads

Let’s do the same after disabling serial direct-path reads:

SQL> alter session set "_serial_direct_read"=never;
Session altered.

I do the same as before, but now my session stats show only conventional reads:

NAME                                                              VALUE
------------------------------------------------------------ ----------
physical read total IO requests                                      44
physical read total multi block requests                             28
physical read total bytes                                       8192000
cell physical IO interconnect bytes                             8192000
physical reads                                                     1000
physical reads cache                                               1000
physical read IO requests                                            44
physical read bytes                                             8192000
physical reads cache prefetch                                       956

and here are the gathered stats:

SNAME           PNAME           PVAL1
--------------- ---------- ----------
SYSSTATS_INFO   STATUS
SYSSTATS_INFO   DSTART
SYSSTATS_INFO   DSTOP
SYSSTATS_INFO   FLAGS               1
SYSSTATS_MAIN   CPUSPEEDNW       2300
SYSSTATS_MAIN   IOSEEKTIM          10
SYSSTATS_MAIN   IOTFRSPEED       4096
SYSSTATS_MAIN   SREADTIM
SYSSTATS_MAIN   MREADTIM         .028
SYSSTATS_MAIN   CPUSPEED         2300
SYSSTATS_MAIN   MBRC               23
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

Now the MBRC is set with the gathered value.

This proves that MBRC is set only for conventional multiblock reads. Direct-path reads are not accounted.

Conclusion

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).

This is the reason why Oracle introduced the EXADATA mode: it sets the MBRC from the db_file_multiblock_read value.
They 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.

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.

All formulas are here with a script that shows what is used by the CBO.

Never say never

Well, that blog post title is too extreme because:

So I should say:
Never gather WORKLOAD stats on Exadata… except if your workload is not an Exadata optimized one.
If you are using Exadata for OLTP, then yes, you can gather WORKLOAD statistics as they probably fit OLTP behaviour. But in any case, always check the gathered stats and see if they are relevant.

Post Views: 1,252
Thumbnail [60x60]
by
Oracle Team