RE: Impact of an incorrect CPUSPEEDNW
- From: "Teehan, Mark" <mark.teehan@xxxxxxxxxxxxxxxxx>
- To: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 18 Jan 2010 16:36:30 +0800
Thanks Ric.
The gathered values for cpuspeednw do appear to vary; accross several
identical servers I see values varying by +/- 100 or so.
Research by some of my colleagues concludes that cpuspeednw is not
particularly important because its influence on the overall cost
calculated by the optimizer is very small. Its variance accross
different types of CPUs (say 400 to 2000 or so, depending on the type
and age of the box) is not enough to be a significant cost factor. The
single block read times are generally accurate, as the SAN throughput
has not changed much over the last few years. So mreadtim, sreadtim
stats gathered several years ago are still ok - this is the critical
number (from system statistics) when calculating cost; not
cpuspeednw/cpuspeed. We will test this by regathering on a
non-production environment and replay lots of SQL to confirm that the
execution plans dont change.
Mark
________________________________
From: Ric Van Dyke [mailto:ric.van.dyke@xxxxxxxxxx]
Sent: 16 January 2010 00:43
To: Teehan, Mark; oracle-l@xxxxxxxxxxxxx
Subject: RE: Impact of an incorrect CPUSPEEDNW
I've been running some tests here on my laptop and the CpuSpeedNw value
is not constant as I run the gather stats at different times with
different loads going on. It's not fluctuating by much, about 2% from
the lowest value to the highest value. The fact that it fluctuates is a
strong indicator that Oracle is "doing something" to calculate the
CpuSpeedNw value and is not just asking the CPUs how fast they are.
Testing done on a Dell Latitude with T2500 dual core CPU 2GHz, Oracle
11.1.0.7 EE
Lowest value - 1009.655
Highest Value - 1031.383
-----------------------
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium
March 7 - 11, 2010
Be there.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Teehan, Mark
Sent: Thursday, January 14, 2010 11:22 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Impact of an incorrect CPUSPEEDNW
I am reviewing system statistics for a number of database servers
running various 10g releases. Most are using default NoWorkload
settings; but I've noticed something odd: some databases have
unexpectedly low values for CpuSpeedNw even though they are running on
identical servers. This is because they were migrated from older servers
and sys stats were not regathered for the new servers.
The database will not automatically recollect system stats unless they
are explicitly deleted. This seems an oversight: given that some values
like CPU_COUNT are rechecked every time the database starts; I wonder if
CpuSpeedNw should also be rechecked automatically.
In some cases the numbers are about 30% what they should be; which means
that the optimizer calculations are incorrect. I wonder if regathering
them - I don't want to do this unless I can confirm that it will make a
significant difference - after all the database has been subsequently
tuned to run with a lower CpUSpeedNw setting.
Something else that I'm checking- if you do an RMAN clone, or an import
FULL=Y, does it pull over the system stats too? I also posted this to
forums.oracle.com.
Any ideas?
Mark
========================================================================
======
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
========================================================================
======
===============================================================================
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
===============================================================================
Other related posts:
