How often is v$sys_time_model updated? July 14, 2010

I think this posting might go down as one of my more pointless contributions to the Oracle knowledge sphere :-)

I was looking at V$SYS_TIME_MODEL and V$SESS_TIME_MODEL and I just happened to run “select * from V$SYS_TIME_MODEL” several times in very quick succession. And I noticed the values for the various counters stayed the same between a couple of the runs.

“Hmmm, interesting” I thought “The values are only flushed down to the view ‘periodically’. I wonder how periodically?”… and thus I wasted a lunch time.

I used the below sql*plus-PL/SQL script to investigate the refreshing of v$sess_time_model. Yes, I know the title says v$sys_time_model but the numbers are smaller and easier to check for the session version of the view and they are virtually the same, I can bet on the results being very similar. This is my script (and it is on 10.2.0.3 on linux):

--test_vstm_upd
-- how often is v$sessstat updated
set trims on
set term off
set serveroutput on size unli
spool test_vstm_upd
begin
for a in 1..1000 loop
for vrec in
(select rpad(rpad(to_char(a),4)||' '||to_char(systimestamp,'ss.ff')||' '|| stat_name||' ',45)||value rec
from v$sess_time_model
-- replace with your current session ID
where sid=1989
and stat_id in (3649082374,2748282437,2821698184,2643905994)
)
loop
dbms_output.put_line(vrec.rec);
end loop;
dbms_output.put_line(chr(9));
end loop;
end;
/
set term on
spool off

As you can see, it simply loops around selecting four of the values from v$sess_time_model, including the loop counter and current timestamp. Timetamp is evaluated afresh for each executed sql statement.

As you can see, the timetamp is increasing by 2/100s of a second or so per loop. Which is not as quick as I hoped but it is a test box. Note that the counters for DB Time, CPU time, SQL execute elapsed time and PL/SQL execution elapsed time are constant.

A few iterations later we see the v$sess_time_model counters increment:

The counters all increment between iteration 7 and 8 and then stay the same. I can’t tell how long the counters had been the same, I need to wait and see when they change again. How long until they increment again? Well, not very long, in fact just around 0.12 seconds:

It is dangerous to look at a little bit of data and draw a firm conclusion, as I nearly did

The data in v$sess_time_model is only maintained in near-time not real-time

The counters in v$sess_time_model increment together

The counters seem to increment in a slightly messy way over part of a second and then are stable for 3/4 of a second to a second or two

I wonder how many of you went “Oh dear” when I said I could derive what is true for v$sys_time_model from v$sess_time_model? Could I? well, here is the modified script for v$sys_time_model:

--test_vstm_upd2
-- how often is v$sysstat updated
set trims on
set term off
set serveroutput on size unli
spool test_vstm_upd2
begin
for a in 1..1000 loop
for vrec in
(select rpad(rpad(to_char(a),4)||' '||to_char(systimestamp,'ss.ff')||' '|| stat_name||' ',45)||value rec
from v$sys_time_model
--where sid=1989
where stat_id in (3649082374)
)
loop
dbms_output.put_line(vrec.rec);
end loop;
-- dbms_output.put_line(chr(9));
end loop;
end;
/
set term on
spool off

I am not sure where but I remember reading something related by Tanel Poder, either on his blog or as a forum question reply.
Querying these views could help a lot when you are trying to diagnose a performance problem where query does not complete execution in reasonable time (so DBMS_XPLAN>DISPLAY_CURSOR or TkProf options are not feasible)

These views are certainly one place to look, but I think more to get a feel for the overall workload of the system and session. You can quickly spot if most of the workload is PL/SQL, SQL or JAVA and also how the parsing time to execute time has been since the instance or session started.

However, I don’t think there is enough detail in these, I would be looking in V$SYSSTAT and V$SESSTAT if I wanted to look at performance issues. You can certainly look at V$SESSTAT for a session and see, by the changes in the values, WHAT the session is doing. You would then need to pull out the in-flight SQL to get a feel for WHY. If I remember right, that is what Tanel looks at also (but unlike me he probably understands all the counters :-) )