ASH Information from the Data Dictionary Views

Oracle  have couple of views to get that information

1. V$ACTIVE_SESSION_HISTORY
2. DBA_HIST_ACTIVE_SESS_HISTORY

The later is used to store older historical information and not the scope of this post

So if you are interested in looking at waits caused in the database for let say in past 15 minutes, run the script below


SQL> SELECT a.event, sum(a.wait_time + a.time_waited) total_waits
           FROM v$active_session_history a
           WHERE a.sample_time between sysdate-1/24/4 AND sysdate
           GROUP BY a.event
           ORDER BY 2 desc;   2    3    4    5

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
log file switch (private strand flush incomplete)  1.6297E+10
row cache lock  5340549172
 2955957596
buffer busy waits   912116327
log file sync   592089888
db file sequential read   336323512
free buffer waits   183243839
buffer exterminate   174974945
read by other session    94306477
log file parallel write    68019177
db file scattered read    65003116

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
log buffer space    58490925
log file switch completion    13633690
control file parallel write     4929383
log file sequential read     4043063
enq: CF - contention     3171815
enq: TX - index contention      631448
log file single write      170984
cursor: pin S wait on X      136428
latch: cache buffers chains 707
SQL*Net message to client   4
null event   0

Issue the following query to get more session specific information that were using the most CPU in last 30 minutes

SQL> SELECT * FROM
           (
           SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)
           FROM v$active_session_history h, v$session s
           WHERE h.session_id = s.sid
           AND   h.session_serial# = s.serial#
           AND   session_state= 'ON CPU' AND
           sample_time > sysdate - interval '30' minute
           GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id
           ORDER BY count(*) desc
            )
           where rownum <= 5;

USERNAME       MODULE       SID    SERIAL# SQL_ID      COUNT(*)
------------------------------ ------------------------------------------------ ---------- ---------- ------------- ----------
XXX       javaw.exe       872 44013 0ncs9wftnk030   637
XXX       javaw.exe       679 25937 27rsanhkvzbyv   504
         1

Now to find out the SQL text of the query causing the high CPU by providing SQL_ID from above, issue the following statement

SQL> SELECT SQL_TEXT FROM V$SQL
           WHERE sql_id = '0ncs9wftnk030';
 
  SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE image  SET count = count+ :1  WHERE CC_id = :2 AND Image_Id = :3
UPDATE image  SET count = count+ :1  WHERE CC_id = :2 AND Image_Id = :3