I published a note a few years ago about using the 12c “with function” mechanism for writing simple SQL statements to takes deltas of dynamic performance views. The example I supplied was for v$event_histogram but I’ve just been prompted by a question on ODC to supply a couple more – v$session_event and v$sesstat (joined to v$statname) so that you can use one session to get an idea of the work done and time spent by another session – the first script reports wait time:
rem rem Program: 12c_with_function_2.sql rem Dated: July 2013 rem Author: J.P.Lewis rem rem See also rem 12c_with_function.sql rem https://jonathanlewis.wordpress.com/2013/06/30/12c-fun/ rem rem Notes: rem Reports session WAIT time rem Modify the list of SIDs of interest rem Set the time in seconds rem define m_snap_time = 60 define m_sid_list = '3, 4, 121, 127' set timing on set sqlterminator off set linesize 180 break on sid skip 1 with function wait_row ( i_secs number, i_return number ) return number is begin dbms_lock.sleep(i_secs); return i_return; end; select sid, sum(total_waits), sum(total_timeouts), sum(time_waited), event from ( select sid, event_id, -total_waits total_waits, -total_timeouts total_timeouts, -time_waited time_waited, -time_waited_micro time_waited_micro, event from v$session_event where sid in ( &m_sid_list ) union all select null, null, null, null, null, wait_row(&m_snap_time, 0), null from dual union all select sid, event_id, total_waits, total_timeouts, time_waited, time_waited_micro, event from v$session_event where sid in ( &m_sid_list ) ) where time_waited_micro != 0 group by sid, event_id, event having sum(time_waited) != 0 order by sid, sum(time_waited) desc /
And this one reports session activity:
rem rem Program: 12c_with_function_3.sql rem Dated: July 2013 rem Author: J.P.Lewis rem rem See also rem 12c_with_function.sql rem https://jonathanlewis.wordpress.com/2013/06/30/12c-fun/ rem rem Notes: rem Reports session stats rem Modify the list of SIDs of interest rem Set the time in seconds rem define m_snap_time = 60 define m_sid_list = '3, 4, 13, 357' set timing on set sqlterminator off set linesize 180 break on sid skip 1 column name format a64 with function wait_row ( i_secs number, i_return number ) return number is begin dbms_lock.sleep(i_secs); return i_return; end; select sid, name, sum(value) from ( select ss.sid, ss.statistic#, sn.name, -ss.value value from v$sesstat ss, v$statname sn where ss.sid in ( &m_sid_list ) and sn.statistic# = ss.statistic# union all select null, null, null, wait_row(&m_snap_time, 0) from dual union all select ss.sid, ss.statistic#, sn.name, ss.value value from v$sesstat ss, v$statname sn where ss.sid in ( &m_sid_list ) and sn.statistic# = ss.statistic# ) where value != 0 group by sid, statistic#, name having sum(value) != 0 order by sid, statistic# /
You’ll notice that I’ve used dbms_lock.sleep() in my wait function – and the session running the SQL can be granted the execute privilege on the package through a role to make this work – but if you’re running Oracle 18 then you’ve probably noticed that the sleep() function and procedure have been copied to the dbms_session package.
How can this work? Isn’t a select always “as of” the starting SCN? Or is this not preserved for these v$-tables (because they go straight to memory)?
Comment by Racer I. — March 7, 2019 @ 7:46 am GMT Mar 7,2019 |
Racer I.
You closing comment is correct. Read consistency doesn’t apply to the dynamic performance views (there’s no undo for Oracle’s internal memory structures)
Comment by Jonathan Lewis — March 7, 2019 @ 4:50 pm GMT Mar 7,2019 |
Thanks. Useful new info. Could this in extreme cases lead to reading stats that don’t seem to match themselves? I.e. first read a total gets stat und further down (and therefore slightly later) the individual break down values and end up with the sums not matching? Or a statistic comes in occurances, duration and amount-values that don’t quite fit together? Or maybe at least each individual subselect will get a consistent snapshot copy of all stats or take out a lock during its duration? Nah, that would slow down the db, can’t have that.
Comment by Racer I. — March 8, 2019 @ 7:19 am GMT Mar 8,2019 |
Racer I.
Good question about consistency.
Without know exactly how Oracle updates the internal arrays for session stats and waits we can’t really know. It seems most unlikely that a session would acquire a latch to update its own stats or waits summary, and I would hope that no-one would acquire a latch on a session’s stats/waits to read them so there probably is some scope for some parts of a session’s data changing as others are being read. The resulting errors are likely to be fairly small, though, given the speed with which the query (probably) reads the arrays.
In passing, there’s a huge chunk of memory in the middle of the x$ksuse record structure (x$ksuse is the fixed array that maps v$session) that isn’t mapped to anything according to the x$kqfco listing – it’s quite possible that that’s where the session event, stats, and time model information is kept for each session.
Comment by Jonathan Lewis — March 8, 2019 @ 12:16 pm GMT Mar 8,2019 |