This discussion is archived
2 Replies Latest reply: Nov 19, 2013 1:32 PM by tychos RSS

Exadata and System Statistics

marksmithusa Journeyer
Currently Being Moderated

Hi, there,

 

This might be a dumb question – but is it necessary to gather system statistics on Exadata machines?

 

I (fairly) recently migrated my Production EDW from a V2 quarter-rack to an X3-2 quarter-rack. On a “normal” system, if I migrated the database to a different (faster) server, I would look at regathering the system statistcs.

 

Is this something that’s sensible or worthwhile with Exadata?

 

Mark

  • 1. Re: Exadata and System Statistics
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    yes you need to do that in order to make the optimizer aware of the I/O processing speed that Exadata offers. Otherwise smart scans will be underutilized.

     

    Keep in mind that you'll need to gather systems with 'Exadata' option and you need to be on the right patchlevel to be able to do that.

     

    Best regards,

    Nikolay

  • 2. Re: Exadata and System Statistics
    tychos Expert
    Currently Being Moderated

    Hi Mark,

    Before you gather system stats you can run the following sql to get your current values.

    ..

    SET SERVEROUTPUT ON

    DECLARE

      STATUS VARCHAR2(20);

      DSTART DATE;

      DSTOP DATE;

      PVALUE NUMBER;

      PNAME VARCHAR2(30);

    BEGIN

       PNAME := 'CPUSPEEDNW';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('cpuspeednw                  : '||pvalue);

       PNAME := 'IOSEEKTIM';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('ioseektime in ms            : '||pvalue);

       PNAME := 'IOTFRSPEED';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('iotfrspeef                  : '||pvalue);

       PNAME := 'SREADTIM';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('single block readtime in ms : '||pvalue);

       PNAME := 'MREADTIM';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('multi block readtime in ms  : '||pvalue);

       PNAME := 'CPUSPEED';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('cpuspeed                    : '||pvalue);

       PNAME := 'MBRC';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('multiblock read count       : '||pvalue);

       PNAME := 'MAXTHR';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('max threads                 : '||pvalue);

       PNAME := 'SLAVETHR';

       DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);

       DBMS_OUTPUT.PUT_LINE('slave threads               : '||pvalue);

      END;

    /

    Best advice I can give would be to check Doc ID 1274318.1 and search for dbms_stats.

    Regards,

    Tycho

Legend

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