Open for Voting

Improve accuracy for DPA assigning wait times per events (Oracle)

Submitting on behalf of Hiren!

There are a couple of views in Oracle that may help the accuracy in assigning times to the wait events as shown in DPA (Ignite).
Here is a query that would access the v$session_event and v$sess_time_model views. 
This would be at the session level and may have to be joined to v$session for the quickpoll and other tables for the text, plan, and stats polls.
When running some tests of statspack and Ignite, there are discrepancies in how the wait times are being reported.

Exploring the opportunity to improve the accuracy in the methodology used.

    SELECT sysdate curr_time,
       b.username,
       b.sid,
       b.serial#,
       b.machine,
       b.program,
       b.module,
       b.action,
       a.event,
       a.total_waits,
       a.total_timeouts,
       a.time_waited/100 seconds_waited,
       a.average_wait/100 avg_Sec_wait,
       a.wait_class
     FROM
       (select sid, event, total_waits, total_timeouts, time_waited, average_wait, wait_class
        from v$session_event
        where sid=sys_context('userenv','sid')
        union all
        select sid, stat_name, null, null, trunc(value/10000,2), null, 'TIME MODEL'
        from V$SESS_TIME_MODEL
        where sid=sys_context('userenv','sid')
          and stat_name in('DB CPU')
       ) a,
       v$session b
    WHERE a.sid= b.sid
      and a.wait_class != 'Idle'
    ORDER BY seconds_waited desc;