RE: DBMS_STATS.GATHER_SYSTEM_STATS - learned something new
Date: Fri, 12 Oct 2012 11:40:29 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88562532ABE_at_NADCWPMSGCMS10.hca.corpad.net>
Thanks Jonathan - I was looking for it and couldn't figure it out. I thought maybe it was an oversight :)
So - would you recommending gathering NOWORKLOAD for a system in general (with testing of course)?
I was reading Karen Morton's blog post about it which brought it to my attention. (Somehow I had never used that option so I was surprised to see how some of the values changed from the defaults)
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, October 12, 2012 11:33 AM
To: oracle-l_at_freelists.org
Subject: Re: DBMS_STATS.GATHER_SYSTEM_STATS - learned something new
Expected behaviour.
Oracle flags whether or not it can use the workload stats (mreadtim/sreadtim/cpuspeed); if not then it applies the cpuspeednw, iotfrrate and whatever the seek time one is called. If you gather workload stats (and they are self-consistent) then Oracle doesn't need to delete the noworkload stats - it's generally principle in Oracle, "don't clean up the mess if you don't need to, it's just more work". (Or as Cary says, the fastest way to do something is to not do it at all - though I doubt if he says that immediately after telling his children to tidy their rooms ;)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: <Christopher.Taylor2_at_parallon.net> To: <oracle-l_at_freelists.org> Sent: Friday, October 12, 2012 4:37 PM Subject: DBMS_STATS.GATHER_SYSTEM_STATS - learned something new
| Version 10.2.0.4
| I see that when you have _NOT_ executed
DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode=>'NOWORKLOAD') at least once,
and you run a regular
dbms_Stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>&some_amount_of_minutes),
that Oracle does not update some of the SYSTEM STATS such as IOTFRSPEED.
|
| IOTFRSPEED stayed at 4096 when using the regular gather. After using
NOWORKLOAD it went to 38883.222, and then regathering stats using interval,
it stays at 38883.222.
|
| Weird.
|
| Chris
|
| --
| http://www.freelists.org/webpage/oracle-l
|
|
|
|
| -----
| No virus found in this message.
| Checked by AVG - www.avg.com
| Version: 2012.0.2221 / Virus Database: 2441/5327 - Release Date: 10/12/12
|
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 12 2012 - 18:40:29 CEST