RE: 9.2.0.4 System Stats: Which one is valid?

Hi John

>execute dbms_stats.gather_system_stats(gathering_mode=3D>'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=3D>'STOP');

IMHO using the DD for such a test is a bad choice. Not only the "load" = is very different (you have lot of clusters and the physical parameters = are probably very different from a "standard" tablespace) but you have = too few data as well. Therefore you should select some GB of real = data...=20

>SNAME PNAME Test1 Test2 Test3 Test4
>Test5 Test6 Test7
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D =3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D =3D=3D=3D=3D=3D =3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D


>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

So fast single block reads are probably due to the OS cache.

>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
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D
>SYSSTATS_TEMP SBLKRDS 90931517
>SYSSTATS_TEMP SBLKRDTIM 7570000
>SYSSTATS_TEMP MBLKRDS 680080
>SYSSTATS_TEMP MBLKRDTIM 8031240
>SYSSTATS_TEMP CPUCYCLES 105632772
>SYSSTATS_TEMP CPUTIM 493285077

First off all the SYSSTATS_TEMP statistics are the statistics when you =
start the gathering. Thus, from them, you cannot say if you did few or =
many I/Os. Second the waits should have no impact on system statistics.=20

>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.

Since only few values should give an overview of the system, IMHO, only = average statistics make sense.

>* 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.

When I do it on production I usually gather them over 1 hour. Of course = it is important to choose a period when the system is normally loaded.

>* 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.

Usually I suggest collecting them only once. But it's strongly dependent = on the system... you could also have different sets and load each set = for a given period.

>* Should we "fix" the stats, considering the average expected =
performance
>for SREADTIM/MREADTIM from the Disk/SAN manufacturer?

No. In fact expected performance is often very different from real = performance!

>* If the stats are "fixed" should one assume/set the worst values, best
>values or the middle ground?

As written before, average.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l