9.2.0.4 System Stats: Which one is valid?

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 31 Jan 2005 15:16:51 -0800
Message-ID: <960A9B249C99994AA3C7FCA016A0DA890635E9@ussccem08.corp.hds.com>

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

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

Received on Mon Jan 31 2005 - 18:19:36 CST