This discussion is archived
10 Replies Latest reply: Feb 27, 2011 8:58 PM by 740885 RSS

System Stats MBRC and MREADTIM both null

740885 Newbie
Currently Being Moderated
Hi all,

I have just run the workload stats collection and was suprised to see MREADTIM set as null - could this be because there were no full table scans during the 1 hour collection period?

db_file_mutltiblock_read_count is unset in the database spfile, so maybe that explains the null value for MBRC?

Are these a valid set of statistics to run with?

This is 4 node RAC 10.2.0.4.4 on Linux x86_64
SQL> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    12-05-2005 03:41
SYSSTATS_INFO                  DSTOP                                     12-05-2005 03:41
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     687.167357
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS ( gathering_mode => 'START');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS ( gathering_mode => 'STOP');

PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    02-21-2011 10:42
SYSSTATS_INFO                  DSTOP                                     02-21-2011 11:37
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                     687.167357
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                            4.129
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED                             1365
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR                           28182528
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.
Many thanks
  • 1. Re: System Stats MBRC and MREADTIM both null
    sb92075 Guru
    Currently Being Moderated
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:67994814192949
  • 2. Re: System Stats MBRC and MREADTIM both null
    740885 Newbie
    Currently Being Moderated
    Thanks,

    That post shows examples and explanations of MREADTIM being less than SREADTIM but not null.

    I'm not sure it explains why there is no value at all for both MBRC and MREADTIM in my environment?

    Thanks
  • 3. Re: System Stats MBRC and MREADTIM both null
    sb92075 Guru
    Currently Being Moderated
    I'm not sure it explains why there is no value at all for both MBRC and MREADTIM in my environment?
    same for me.
     1* select SNAME,PNAME     , pval2 from sys.aux_stats$
    SQL> /
    
    SNAME                      PNAME                     PVAL2
    ------------------------------ ------------------------------ ------------------------------
    SYSSTATS_INFO                 STATUS                     COMPLETED
    SYSSTATS_INFO                 DSTART                     08-15-2009 00:49
    SYSSTATS_INFO                 DSTOP                     08-15-2009 00:49
    SYSSTATS_INFO                 FLAGS
    SYSSTATS_MAIN                 CPUSPEEDNW
    SYSSTATS_MAIN                 IOSEEKTIM
    SYSSTATS_MAIN                 IOTFRSPEED
    SYSSTATS_MAIN                 SREADTIM
    SYSSTATS_MAIN                 MREADTIM
    SYSSTATS_MAIN                 CPUSPEED
    SYSSTATS_MAIN                 MBRC
    SYSSTATS_MAIN                 MAXTHR
    SYSSTATS_MAIN                 SLAVETHR
    
    13 rows selected.
    Why are you looking at internal tables when contents are NOT documented?

    Edited by: sb92075 on Feb 20, 2011 8:32 PM
  • 4. Re: System Stats MBRC and MREADTIM both null
    740885 Newbie
    Currently Being Moderated
    The table may not be documented, but it hardly a secret and even the column names are referenced in the official documentation

    [http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#g43900]

    Anyway, reading further in that documentation it does say

    +"During the gathering process of workload statistics, it is possible that mbrc and mreadtim will not be gathered if no table scans are performed during serial workloads, as is often the case with OLTP systems. On the other hand, FTS occur frequently on DSS systems but may run parallel and bypass the buffer cache. In such cases, sreadtim will still be gathered since index lookup are performed using the buffer cache. If Oracle cannot gather or validate gathered mbrc or mreadtim, but has gathered sreadtim and cpuspeed, then only sreadtim and cpuspeed will be used for costing. FTS cost will be computed using analytical algorithm implemented in previous releases. Another alternative to computing mbrc and mreadtim is to force FTS in serial mode to allow the optimizer to gather the data."+

    Thanks anyway.
  • 5. Re: System Stats MBRC and MREADTIM both null
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    skulls wrote:
    SNAME                          PNAME                               PVAL1 PVAL2
    ------------------------------ ------------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------
    SYSSTATS_INFO                  STATUS                                    COMPLETED
    SYSSTATS_INFO                  DSTART                                    02-21-2011 10:42
    SYSSTATS_INFO                  DSTOP                                     02-21-2011 11:37
    SYSSTATS_INFO                  FLAGS                                   1
    SYSSTATS_MAIN                  CPUSPEEDNW                     687.167357
    SYSSTATS_MAIN                  IOSEEKTIM                              10
    SYSSTATS_MAIN                  IOTFRSPEED                           4096
    SYSSTATS_MAIN                  SREADTIM                            4.129
    SYSSTATS_MAIN                  MREADTIM
    SYSSTATS_MAIN                  CPUSPEED                             1365
    SYSSTATS_MAIN                  MBRC
    SYSSTATS_MAIN                  MAXTHR                           28182528
    SYSSTATS_MAIN                  SLAVETHR
    Notwithstanding comments to the contrary in the manuals, but subject to variation with version of Oracle (and I've just tested 10.2.0.3):

    Oracle will behave as if MBRC matches the dbfile_optimizer_read_count parameter - which will probably be 8 if you haven't set db_file_multiblock_read_count but will match db_file_multiblock_read_count if you have set it. Then it will synthesise the mreadtim from the default block, assumed MBRC, ioseektim and iotfrtim.

    In a typical system, with 8KB blocks and MBRC assumed at 8, this means your mreadtim will be: 8 (blocks) * 8192 (blocksize) / 4096 (iotfrspeed) + 10 (ioseektim) for a total of 26 ms, and the sreadtim will be synthesised at 8192 (blocksize) / 4096 (iotfrspeed) + 10 (ioseektim) = 12 ms.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk


    A general reminder about "Forum Etiquette / Reward Points": http://forums.oracle.com/forums/ann.jspa?annID=718

    If you never mark your questions as answered people will eventually decide that it's not worth trying to answer you because they will never know whether or not their answer has been of any use, or whether you even bothered to read it.

    It is also important to mark answers that you thought helpful - again it lets other people know that you appreciate their help, but it also acts as a pointer for other people when they are researching the same question, moreover it means that when you mark a bad or wrong answer as helpful someone may be prompted to tell you (and the rest of the forum) what's so bad or wrong about the answer you found helpful.
  • 6. Re: System Stats MBRC and MREADTIM both null
    740885 Newbie
    Currently Being Moderated
    Hi Jonathan,

    Thank you for the reply.

    Do I imply from your response that it is normal for the MBRC and mreadtim to be null if there have been no Full table scans during the gathering period?

    A much longer collection period gave me the following
    SNAME           PNAME                     PVAL1  PVAL2
    --------------- ---------------- --------------  -----------------
    SYSSTATS_INFO   STATUS                           COMPLETED
    SYSSTATS_INFO   DSTART                           02-21-2011 15:49
    SYSSTATS_INFO   DSTOP                            02-22-2011 07:26
    SYSSTATS_INFO   FLAGS                          1
    SYSSTATS_MAIN   CPUSPEEDNW            687.167357
    SYSSTATS_MAIN   IOSEEKTIM                     10
    SYSSTATS_MAIN   IOTFRSPEED                  4096
    SYSSTATS_MAIN   SREADTIM                   3.535
    SYSSTATS_MAIN   MREADTIM                   1.513
    SYSSTATS_MAIN   CPUSPEED                    1436
    SYSSTATS_MAIN   MBRC                          16
    SYSSTATS_MAIN   MAXTHR                  37744640
    SYSSTATS_MAIN   SLAVETHR
    SYSSTATS_MAIN                  SLAVETHR
    From this and reading your blog, I believe the mreadtim is being affected by our EMC SAN storage cache?

    To correct this, I was going to use your reader.c program, but I wasn't sure how to run it to get the appropriate mreadtim value anyway and how I could run it against an ASM LUN?

    You also mentioned iozone and there were some other tools mentioned on your blog. What I am unsure of, is what value from the results of whichever tool I use should be using to replace mreadtim
    and how to use these with ASM configured LUNS?

    For example, I ran the new DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure and got the following results but am not sure they can be used to derive a mreadtim value ?
    SQL> select * from DBA_RSRC_IO_CALIBRATE;
    
    START_TIME                   END_TIME                       MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
    ---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ------------------
    22-FEB-11 04.52.05.933751 PM 22-FEB-11 05.03.09.122433 PM       1605        156        158          9                  8
    Best regards.
  • 7. Re: System Stats MBRC and MREADTIM both null
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    skulls wrote:

    Do I imply from your response that it is normal for the MBRC and mreadtim to be null if there have been no Full table scans during the gathering period?
    Correct - although to nitpick about words: you infer from the thing I imply.


    A much longer collection period gave me the following
    SNAME           PNAME                     PVAL1  PVAL2
    --------------- ---------------- --------------  -----------------
    SYSSTATS_MAIN   SREADTIM                   3.535
    SYSSTATS_MAIN   MREADTIM                   1.513
    SYSSTATS_MAIN   MBRC                          16
    From this and reading your blog, I believe the mreadtim is being affected by our EMC SAN storage cache?
    I would agree with that. (You said that you hadn't set db_file_muitblock_read_count, though, but your MBRC is 16; it has to be something, but 16 is one of those funny numbers that looks like a deliberate choice, is this just a coincidence ?)
    >
    SQL> select * from DBA_RSRC_IO_CALIBRATE;
    
    START_TIME                   END_TIME                       MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
    ---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ------------------
    22-FEB-11 04.52.05.933751 PM 22-FEB-11 05.03.09.122433 PM       1605        156        158          9                  8
    The thing about this is that it suggests that your single block read time should be at least 9 ms (latency), combined with your max mb per sec and your current MBRC this would suggest sreadtim=9, mreadtim = 10. However, my favourite approach is to take the hardware specs from the manufacturer for seek time and transfer rate and use those for ioseektim and ittfrspeed - in your case, since you've got an MBRC I'd leave that in place rather than deleting it.

    Regards
    Jonathan Lewis
  • 8. Re: System Stats MBRC and MREADTIM both null
    740885 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:

    Correct - although to nitpick about words: you infer from the thing I imply.
    Thanks Jonathan, for both the technical and grammatical lessons :)
    Jonathan Lewis wrote:

    I would agree with that. (You said that you hadn't set db_file_muitblock_read_count, though, but your MBRC is 16; it has to be something, but 16 is one of those funny numbers that looks like a deliberate choice, is this just a coincidence ?)
    I presume it must be coincidence. I have certainly not set db_file_multiblock_read_count as shown below
    NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
    -------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
    db_file_multiblock_read_count                      128                                                                    TRUE     FALSE      FALSE
    _db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
    _db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
    _db_file_optimizer_read_count                      8                                                                      TRUE     FALSE      FALSE
    I checked the manufacturer specs, but got confused with terminology and which should be used for which value in sys.aux_stats$

    The specs say :
    Transfer Rates
    
     Buffer to/from Media = 93MB/s max   
     SP to/from Buffer = 400MB/s max
    
    Access Times
     
     Average seek = 3.9ms Read  
                  = 4.2ms Write
    
    Rotational Latency = 3.00 ms
    Are these of use?
    >>
    Jonathan Lewis wrote:
    SQL> select * from DBA_RSRC_IO_CALIBRATE;
    
    START_TIME                   END_TIME                       MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
    ---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ------------------
    22-FEB-11 04.52.05.933751 PM 22-FEB-11 05.03.09.122433 PM       1605        156        158          9                  8
    The thing about this is that it suggests that your single block read time should be at least 9 ms (latency), combined with your max mb per sec and your current MBRC this would suggest sreadtim=9, mreadtim = 10.
    I understand how you got to the value for sreadtim, but how did you arrive at 10 for mreadtim?



    Again, many thanks Jonathan for taking the time to reply to my questions.
  • 9. Re: System Stats MBRC and MREADTIM both null
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    skulls wrote:
    >
    Jonathan Lewis wrote:
    SQL> select * from DBA_RSRC_IO_CALIBRATE;
    
    START_TIME                   END_TIME                       MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
    ---------------------------- ---------------------------- ---------- ---------- ---------- ---------- ------------------
    22-FEB-11 04.52.05.933751 PM 22-FEB-11 05.03.09.122433 PM       1605        156        158          9                  8
    The thing about this is that it suggests that your single block read time should be at least 9 ms (latency), combined with your max mb per sec and your current MBRC this would suggest sreadtim=9, mreadtim = 10.
    I understand how you got to the value for sreadtim, but how did you arrive at 10 for mreadtim?

    The maximum throughput is 158MB/s which equates to 158KB per millisecond.
    You have a workload MBRC of 16 which equates to 128KB (I assumed you were using the common 8KB block size) - so I decide to add the (approximately) 1 ms for the 158KB to the 9ms for the single block read to get an estimated time for the 128KB read.

    Regards
    Jonathan Lewis
  • 10. Re: System Stats MBRC and MREADTIM both null
    740885 Newbie
    Currently Being Moderated
    Thank you Jonathan.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points