Oracle Scratchpad

March 6, 2019

12c Snapshots

Filed under: 12c,Oracle,Partitioning,Performance — Jonathan Lewis @ 10:35 am BST Mar 6,2019

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.

 

4 Comments »

  1. 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 BST Mar 7,2019 | Reply

    • 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 BST Mar 7,2019 | Reply

  2. 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 BST Mar 8,2019 | Reply

  3. 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 BST Mar 8,2019 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.