When is a table not a table?

When something's happening. Not the funniest punchline in the world I'll admit, but it does have the merit of being true.

We are updating an application quite significantly so will need to do full regression testing. This particular installation doesn't currently use system stats, so I thought this an appropriate opportunity on the development system to gather some. I used the following

BEGIN DBMS_STATS.GATHER_SYSTEM_STATS( GATHERING_MODE => 'INTERVAL', INTERVAL => 60); END; 

whilst the rather intensive upgrade was happening.

I then went to lunch and queried the SYS.AUX_STATS$ table to see what we had got. In my head, and quite possibly yours, I know the structure of this table, it holds the name and value of a number of system stats - as well as some other information - some at least expressed in units we don't quite understand.   

This is what happened.

SYS @ system_name >select pname,pval1 from aux_stats$;

PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   0
SBLKRDS                           3788066
SBLKRDTIM                         3200830
MBLKRDS                            225689
MBLKRDTIM                          496520
CPUCYCLES                           83664
CPUTIM                             213512
JOB                                    64
MBRTOTAL                          5243932

12 rows selected.

SYS @ system_name >/

PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   0
SREADTIM                             .829
MREADTIM                             2.13
CPUSPEED                              395
MBRC                                    6
MAXTHR                            1435648
SLAVETHR                               -1

10 rows selected.

I've edited the system name but nothing else. These two queries were only a few minutes apart - enough time for me to look up the structure of AUX_STATS$ in the doc and confirm it should look like the second output and not the first. What had happened? Nothing, my interval based collection hadn't stopped yet.

It looks fairly clear then that, as one might expect, the single block read time and the multi block read time are obtained by dividing the time spent on all reads by the number of reads. The CPU figure at first sight doesn't seem so simple, but in fact the same logic is most probably happening here. This system has 8 cpus. So based on the  figures above we would calculate a cpuspeed of

(213512*1000) /8 /83664 = 320Mhz.

So be careful when querying AUX_STATS$ especially if you are doing so when gathering stats - those shops that gather system stats on a schedule might be affected - what you get out may not be what you think it is.