RE: DBMS_STATS.GATHER_SYSTEM_STATS - learned something new
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Oct 2012 11:40:29 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Friday, October 12, 2012 11:33 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
|
| --
| //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
|
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
