9.2.0.4 System Stats: Which one is valid?

Hi all,

Running _exactly_ the same set of SQL queries against the DD in between a
START and STOP of DBMS_STATS.GATHER_SYSTEM_STATS, at various times on a
not-so-active system with a 9204 database, I see the following *wide*
variation. This is on a 4 CPU E450 Solaris 8 box using a SAN.

execute dbms_stats.gather_system_stats(gathering_mode=>'START');
select owner, object_type, count(*)
from dba_objects group by owner, object_type;
-- Optionally introduce a !sleep <n> secs
select user_name, count(*) from <Large_schema>.<Large_table>
group by user_name;
-- Optionally introduce a !sleep <n> secs
select segment_type, sum(bytes)
from dba_extents group by segment_type;
-- Optionally introduce a !sleep <n> secs
execute dbms_stats.gather_system_stats(gathering_mode=>'STOP');

SNAME                   PNAME           Test1   Test2   Test3   Test4
Test5   Test6 Test7
================= =========== ===== ===== ===== ===== ===== ===== =====
SYSSTATS_MAIN   SREADTIM        0.782   4.475   0.186   0.057   0.104
0.071   0.089
SYSSTATS_MAIN   MREADTIM        31.21   9.507   12.02   7.945   6.387
10.59   12.539
SYSSTATS_MAIN   CPUSPEED        214     214     214     214     214     214
214
SYSSTATS_MAIN   MBRC            102     34      48      36      30      47
47
-------------
MREADTIM/SREADTIM                       39.91   2.12    64.65   139.3
61.41   149.1   140.89

Querying AUX_STATS$ to see the stats the collection, I can see that it
performed a reasonably large number of SBLKRDS and MBLKRDS, so I know that I
did not lack for _number_ of IOPs. I even introduced varying levels of waits
between bursts of I/O as seen above to smoothen (or not) any load peaks.

SNAME                   PNAME           PVAL1   
================= =========== ========
SYSSTATS_TEMP   SBLKRDS 90931517
SYSSTATS_TEMP   SBLKRDTIM       7570000
SYSSTATS_TEMP   MBLKRDS 680080
SYSSTATS_TEMP   MBLKRDTIM       8031240
SYSSTATS_TEMP   CPUCYCLES       105632772
SYSSTATS_TEMP   CPUTIM  493285077

The questions are many, but the main ones are: 

* Which set of Stats do I use? Using the calculation for COST (P 9-22 9i
perf tuning guide), it seems that the cost can vary widely if you look at
the spread of values above.
* What is a reasonable collection period (i.e. time between START/STOP for
gathering_mode)? From my (probably flawed) deduction, I conclude that a
short collection period may miss peaks, while a larger period may smoothen
out the same peaks.
* What should the periodicity of System stats collection be? Should one
collect once a day/once a week... Varying sets of values, I am sure, will
make Costs swing all over the place, while with a constant set of values,
you are "fixed" to a cost plan that may be invalid for _that_ period.
* Should we "fix" the stats, considering the average expected performance
for SREADTIM/MREADTIM from the Disk/SAN manufacturer?
* If the stats are "fixed" should one assume/set the worst values, best
values or the middle ground?

I am looking for both experiences, as well as advice, with System Stats.
(And I have read JL's excellent article at OTN, but am not able to deduce
these answers therein.) An earlier email didn't make it to the list. Hope
this one does!

Thanks,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l

Other related posts: