On SSDs - Unable to update System Stats (Noworkload)!

From: PD Malik <pdthedba_at_gmail.com>
Date: Fri, 8 Feb 2013 10:57:25 +0000
Message-ID: <CAHgaR1AxKq-SJY5p+0y7CJW8QaVcPBH4BzRpF9ogaRuZ8Gh9wg_at_mail.gmail.com>



Hello Experts,
Just wondering if anyone has ever come across this behavior please.

We are unable to gather the NOWORKLOAD System stats on our SSD storage
(Violin + IBM V7000) based Oracle systems or more precisely, its completing
successfully but the stat number remain unchanged at their default values.

This is what we are doing :-

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.

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.

As we know the 10 and 4096 are default values for IOSEEKTIM and IOTFRSPEED, respectively, come seeded with any new DBs and DBAs need to run the stats gather procedure to update them and this is what we are trying to do but its not updating. But Oracle recognizes that it we’ve gathered the system stats because the times get updated:

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

PNAME                               PVAL1 PVAL2
------------------------------ ---------- --------------------
STATUS                                    COMPLETED
DSTART                                    02-08-2013 09:47
DSTOP                                     02-08-2013 09:47
FLAGS                                   1

Now, looking at Oracle documentation, this is what it says :-

“In some cases, the value of noworkload statistics may remain its default value. In such cases, repeat the statistics gathering process or set the value manually to values that the I/O system has according to its specifications by using the DBMS_STATS.SET_SYSTEM_STATS procedure.”
(Link Reference :

http://docs.oracle.com/cd/E18283_01/server.112/e16638/stats.htm# )

I’ve tried it repeatedly so that bit doesn’t work. The only possible explanation for this behavior that I can think of is that Oracle’s default unit for IOSEEKTIM is in ms (mili secs) and it probably has some validation built in into it that if the read times are less than lets say one mil sec or so then it thinks something isn’t right and ignores the whole thing and that’s why the gather_system_stats procedure also completes within a few secs because on the system where it collects these stats (non SSD but same Oracle version) it normally takes 2-3 mins.

Now I am sure I probably wont be the first DBA on the planet to face this so just wondering if someone who has come across this can guide me please, what should ideally be done in this case. How did you tackle this?

For completion, we are not keen on Workload stats so not much bothered about that. Moreover, there is not enough load on the system (project still in early development) so we cant collect that currently anyway.

Thanks.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 08 2013 - 11:57:25 CET

Original text of this message