Oracle V$OSSTAT

Question

The Oracle view V$OSSTAT holds a few operating statistics, including:

• IDLE_TICKS Number of hundredths of a second that a processor has been idle, totalled over all processors
• BUSY_TICKS Number of hundredths of a second that a processor has been busy executing user or kernel code, totalled over all processors

The documentation I've read has not been clear as to whether these are ever reset. Does anyone know?

Another question I have is that I'd like to work out the average CPU load the system is experiencing. To do so I expect I have to go:

busy_ticks / (idle_ticks + busy_ticks)

Is this correct?

Update Nov 08

Oracle 10g r2 includes a stat called LOAD in this table. It provides the current load of the machine as at the time the value is read. This is much better than using the other information as the *_ticks data is "since instance start" not as of the current point in time.

Answer 1

You'll need to include 'IOWAIT_TICKS` if they are available.

IDLE_TICKS - Number of hundredths of a second that a processor has been idle, totaled over all processors

BUSY_TICKS - Number of hundredths of a second that a processor has been busy executing user or kernel code, totaled over all processors

IOWAIT_TICKS - Number of hundredths of a second that a processor has been waiting for I/O to complete, total led over all processors

Here is a query.

SELECT (select value from v$osstat where stat_name = 'BUSY_TICKS') /
(
   NVL((select value from v$osstat where stat_name = 'IDLE_TICKS'),0) +
   NVL((select value from v$osstat where stat_name = 'BUSY_TICKS'),0) +
   NVL((select value from v$osstat where stat_name = 'IOWAIT_TICKS'),0)
)
FROM DUAL;

On 10.2 and later the names _TICKS have been changed to _TIME.

Cumulative values in dynamic views are reset when the database instance is shutdown.

See Automatic Performance Statistics and v$OSStat for more info.

Answer 2

I am not convinced I need to include USER_TICKS and SYS_TICKS.

The documentation for BUSY_TICKS states:

"...been busy executing user or kernel code, totalled over all processors"

which suggests that BUSY_TICKS already includes USER_TICKS and SYS_TICKS.

Same for NICE_TICKS - which is still user time (just lower priority).

Including IOWAIT_TICKS seems likely to be necessary though.

Answer 3

You are correct about the Busy value. I checked one of my systems and Busy is equal to User + Sys.

I also confirmed that Busy and Idle account for all the time on my system (my system doesn't have IOWAIT).

Answer 4

Come to think of it, If my purpose is to get a sense of overall machine load, I'd probably be better including IOWAIT_TICKS in the numerator & denominator, to get a sense of the amount of CPU time not available for my work.

SELECT (
(select value from v$osstat where stat_name = 'BUSY_TICKS') +
(select value from v$osstat where stat_name = 'IOWAIT_TICKS'))
/
(
   NVL((select value from v$osstat where stat_name = 'IDLE_TICKS'),0) +
   NVL((select value from v$osstat where stat_name = 'BUSY_TICKS'),0) +
   NVL((select value from v$osstat where stat_name = 'IOWAIT_TICKS'),0)
)
FROM DUAL;

This would ensure that if the machine had a high rate of IO contention or waiting, I wouldn't be mislead and would add to the problem by running more jobs.

Answer 5

Depends on your perspective. Knowing what you are after, I think you have it right.