RE: maxthr system statistic

  • From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
  • To: <Brandon.Allen@xxxxxxxxxxx>
  • Date: Fri, 19 Jan 2007 09:23:02 +0100


> Does anybody have a good understanding of how the maxthr
> metric is computed or how it's used by the CBO?

A couple of years ago I spent some time investigating how system stats
are computed. 

It was not a problem to find out how MBRC, SREADTIM and MREADTIM are
computed. In fact they are simply based on values available in x$kcfio
and v$sysstat.

For CPUSPEED, MAXTHR and SLAVETHR it's a different story. The problem is
that callouts to a C library (DBMS_STATS_LIB) are performed to get them.
For example for the throughput it seams they have two functions:

oracle@trantor:~/ [A1020] nm $ORACLE_HOME/lib/libserver10.a | grep
00012004 t kdapgetthr
00012034 t kdapresetthr

I stopped my research at this point. So, no idea what they really do...

My guess is the following:
- when the gathering of system stats is started the function
kdapresetthr is called to reset the variable(s) storing the throughput
- the system, while doing I/Os, stores in the variable(s) the maximum
- when the gathering of system stats is stopped the function kdapgetthr
is called to get the throughput information

> I'm just curious because I've gathered system stats 5 times
> in the past few days and keep getting wildly different values
> for this metric while the rest are relatively constant:

That match what I observed on systems where system stats are gathered. 

For this reason I presently advice my customers to gather system stats
not in one simple call, but to gather them in a user-defined table at
regular intervals (e.g. 1h) for a week or so. Then plot a nice chart
with Excel and find out what "good" values are. 


Other related posts: