9.2.0.4 System Stats: Which one is valid?
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Mon, 31 Jan 2005 15:16:51 -0800
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 **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
