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 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 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.
Recent comments
2 years 51 weeks ago
3 years 11 weeks ago
3 years 16 weeks ago
3 years 16 weeks ago
3 years 21 weeks ago
3 years 42 weeks ago
4 years 10 weeks ago
4 years 40 weeks ago
5 years 24 weeks ago
5 years 25 weeks ago