On SSDs - Unable to update System Stats (Noworkload)!
- From: PD Malik <pdthedba@xxxxxxxxx>
- To: Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 8 Feb 2013 10:57:25 +0000
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 weve 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# )
Ive tried it repeatedly so that bit doesnt work. The only possible
explanation for this behavior that I can think of is that Oracles 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 isnt right and ignores the whole thing and
thats 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
