Hi guys,
It's Oracle 12.2 on Linux x86-64.
To best of my knowledge, DB time is the sum of DB CPU time plus the non-idle waiting time of all sessions.
I used the following code to measure the change in DB time and DB CPU in the last minute. The output shows that the DB CPU time was more than DB time.
Could you clarify please?
set serveroutput on
DECLARE
N1 V$SYS_TIME_MODEL.VALUE%TYPE;
N1D V$SYS_TIME_MODEL.VALUE%TYPE;
N2 V$SYS_TIME_MODEL.VALUE%TYPE;
N2D V$SYS_TIME_MODEL.VALUE%TYPE;
BEGIN
SELECT VALUE INTO N1 FROM V$SYS_TIME_MODEL WHERE STAT_NAME IN ('DB time');
SELECT VALUE INTO N2 FROM V$SYS_TIME_MODEL WHERE STAT_NAME IN ('DB CPU');
DBMS_LOCK.SLEEP(60);
SELECT VALUE INTO N1D FROM V$SYS_TIME_MODEL WHERE STAT_NAME IN ('DB time');
SELECT VALUE INTO N2D FROM V$SYS_TIME_MODEL WHERE STAT_NAME IN ('DB CPU');
N1D := N1D - N1;
N2D := N2D - N2;
-- n1d and n2d are in microseconds.
-- convert them into centi seconds
N1D := N1D / 10000;
N2D := N2D / 10000;
DBMS_OUTPUT.PUT_LINE('DB time increment in the last minute: ' || TO_CHAR(N1D,'999,999.999') || ' centi seconds') ;
DBMS_OUTPUT.PUT_LINE('DB CPU increment in the last minute: '|| TO_CHAR(N2D,'999,999.999') || ' centi seconds');
END;
/
DB time increment in the last minute: .821 centi seconds
DB CPU increment in the last minute: .900 centi seconds