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
>47
>-------------
>MREADTIM/SREADTIM                      39.91   2.12    64.65   139.3
>61.41  149.1   140.89

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

Other related posts: