RE: maxthr system statistic

Brandon

> 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_at_trantor:~/ [A1020] nm $ORACLE_HOME/lib/libserver10.a | grep kdap.*thr
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 information
- the system, while doing I/Os, stores in the variable(s) the maximum throughput
- 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.

HTH
Chris

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