Active Session History (ASH)

ASH Report
$ORACLE_HOME/rdbms/admin/ashrpt.sql



USER_ID. The numerical user ID (not the username) of the database user who created this session.

 SESSION_ID. The session ID (SID) of the session.

 SESSION_STATE. The state the session was in when Active Session History took the sample. It shows WAITING if the session was waiting for something; otherwise, it shows ON CPU to indicate that the session was doing productive work.

 EVENT. If the session was in a WAITING state (in the SESSION_STATE column), this column will show the wait event the session was waiting for.

 TIME_WAITED. If the session was in a WAITING state, this column will show how long it had been waiting when Active Session History took the sample.

 WAIT_TIME. If the session is doing productive work—not in a WAITING state—this column will show how long the session waited for the last wait event.

 SQL_ID. The ID of the SQL statement the session was executing at the time the sample was taken.

 SQL_CHILD_NUMBER. The child number of the cursor. If this is the only version of the cursor, the child number will be 0.

 v$active_session_history
-accumulated information

 vi v_active_session_history.sql
set pagesize 200
SELECT NVL(a.event, 'ON CPU') AS event,
       COUNT(*) AS total_wait_time
FROM   v$active_session_history a
WHERE  a.sample_time > SYSDATE - 5/(24*60) -- 5 mins
GROUP BY a.event
ORDER BY total_wait_time DESC;


 dba_hist_active_sess_history
vi dba_hist_active_sess_history.sql

 SELECT NVL(a.event, 'ON CPU') AS event,
       COUNT(*)*10 AS total_wait_time
FROM   dba_hist_active_sess_history a
WHERE  a.sample_time > SYSDATE - 1 
GROUP BY a.event

ORDER BY total_wait_time DESC;
To get specific row information

select
    owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_rowid.rowid_create (
        1,
        o.data_object_id,
        row_wait_file#,
        row_wait_block#,
        row_wait_row#
    ) row_id
from v$session s, dba_objects o
where sid = &sid
and o.data_object_id = s.row_wait_obj#

OBJ_NAME       ROW_ID
—————————————  ———————————————————
ARUP.TEST1:-   AAAdvSAAHAAABGPAAw



To List consumer groups 


select sample_time, session_state, event, consumer_group_id
from v$active_session_history
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.02 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('29-SEP-12 05.05.02 PM','dd-MON-yy hh:mi:ss PM')
and session_id = 44
order by 1;

                           SESSION
SAMPLE_TIME                _STATE   EVENT               CONSUMER_GROUP_ID
—————————————————————————  ———————  ——————————————————  —————————————————
29-SEP-12 04.55.34.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.35.419 PM  ON CPU                                   12166
29-SEP-12 04.55.36.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.37.419 PM  ON CPU                                   12166
29-SEP-12 04.55.38.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.39.419 PM  WAITING  resmgr:cpu quantum              12166
29-SEP-12 04.55.40.419 PM  ON CPU                                   12166
… output truncated …
29-SEP-12 04.55.37.419 PM  ON CPU                                   12162
29-SEP-12 04.55.38.419 PM  ON CPU                                   12166
29-SEP-12 04.55.39.419 PM  ON CPU                                   12162
29-SEP-12 04.55.40.419 PM  ON CPU                                   12162






To check consumer group id


select name
from v$rsrc_consumer_group
where id in (12166,12162);     

   ID  NAME
—————— ————————————
12166  OTHER_GROUPS
12162  APP_GROUP








To Get row lock information from the Active Session History archive

select sample_time, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_ROWID.ROWID_create (
        1,
        o.data_object_id,
        current_file#,
        current_block#,
        current_row#
    ) row_id
from dba_hist_active_sess_history s, dba_objects o
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.02 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('29-SEP-12 05.05.02 PM','dd-MON-yy hh:mi:ss PM')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1,2;
Posted by