Wednesday, September 28, 2011

Getting ASH Information from the Data Dictionary Views

There are several ways for getting Active Session History information, one way is using a built in script as discussed in my post

This post is related to getting the information using data dictionary views.

We have couple of views to get that information


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; 

EVENT                                                            TOTAL_WAIT
---------------------------------------------------------------- ----------
control file parallel write                                        29740
db file async I/O submit                                        2942

If you are interested in getting more session specific information that were using the most CPU in last 15 minutes, issue the following query

           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 '15' 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(*)
---------- ------------ ---------- ---------- --------------- ----------
BILAL           SQL Developer        112      61151               9dq17507m0ffv            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

           WHERE sql_id = '9dq17507m0ffv';

No comments:

Post a Comment