2 Replies Latest reply: Feb 7, 2013 12:48 PM by Karan RSS

    Unable to update System Stats (Noworkload)!

    user248849
      Hello Experts,

      I am unable to gather the system stats for a few of my 11gR2 DBs with the following command :

      dbms_stats.gather_system_stats(gathering_mode => 'noworkload')

      The IOSEEKTIM, IOTFRSPEED still stay unchanged at their default values. Here is what I am doing :-

      SQL> SELECT pname, pval1
      2 FROM sys.aux_stats$
      3 WHERE sname = 'SYSSTATS_MAIN';

      PNAME PVAL1
      ------------------------------ ----------
      CPUSPEEDNW 1564.63196
      IOSEEKTIM 10
      IOTFRSPEED 4096
      SREADTIM
      MREADTIM
      CPUSPEED
      MBRC
      MAXTHR
      SLAVETHR

      9 rows selected.

      SQL> EXEC dbms_stats.gather_system_stats(gathering_mode => 'noworkload');

      PL/SQL procedure successfully completed.

      SQL> SELECT pname, pval1
      2 FROM sys.aux_stats$
      3 WHERE sname = 'SYSSTATS_MAIN';

      PNAME PVAL1
      ------------------------------ ----------
      CPUSPEEDNW 1560
      IOSEEKTIM 10
      IOTFRSPEED 4096
      SREADTIM
      MREADTIM
      CPUSPEED
      MBRC
      MAXTHR
      SLAVETHR

      9 rows selected.

      I've tried it a few times - no luck.

      It is probably irrelevant but we are using ASM and there are 2 different storage systems underneath and SYSAUX is no a different storage system than the rest of the DB (or majority of it) but again, that shouldn't matter I'd have thought?

      DB version is 11.2.0.3 but I can update it on a different DB residing on a different storage system(unmixed). Another point is that on the system where it does the update, it takes about 2-3 mins (normal) but on this system it comes back straight away (first time I thought : wow so fast because its on SSDs only to find out its cheating me :-) )

      Thanks.

      Edited by: user11983147 on Feb 7, 2013 7:44 AM

      Edited by: user11983147 on Feb 7, 2013 7:51 AM