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 http://bash-dba.blogspot.com/2011/09/manually-getting-active-session.html

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

We 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; 



EVENT                                                            TOTAL_WAIT
---------------------------------------------------------------- ----------
                                                                            6003216
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

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 '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

SQL> SELECT SQL_TEXT FROM V$SQL
           WHERE sql_id = '9dq17507m0ffv';
     

No comments:

Post a Comment