CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Active Session History (ASH)
Version 11.1
Note: Active session history is snapped once each second in gv_$active_session_history, held for approximately 30 minutes and then stored in dba_hist_active_sess_history. It is designed for an hour of online storage, based on 2MB per CPU, but may fill and flush sooner.  when written to disk it is further sampled (1 out of 10)

Thank you, Job Miller at Oracle, for the additional information found at this site: Click Here
Dependent Objects
dba_hist_active_sess_history wrh$_active_session_history
gv_$active_session_history wrm$_snapshot
ASH Buffers SELECT *
FROM gv$sgastat
WHERE name = 'ASH buffers';
Most Active SQL in the previous hour desc gv$active_session_history

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'BACKGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'FOREGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
Most Active I/O SELECT DISTINCT wait_class
FROM gv$event_name
ORDER BY 1;

SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display_cursor('gpv3kb4n2f2q1'));
 
ASH Demo
Demo preparation as the active user conn uwclass/uwclass

SELECT t.tablespace_name
FROM all_tables t, all_indexes i
WHERE t.tablespace_name = i.tablespace_name;

-- Note: do not close session during the balance of this demo
Demo preparation as the DBA conn / as sysdba

SELECT sid, serial#
FROM gv$session
WHERE username = 'UWCLASS';
To find out the wait events for which this session SELECT sample_time, event, wait_time
FROM gv$active_session_history
WHERE session_id = 147
AND session_serial# = 1715;
To find recent sample times SELECT sample_time
from gv$active_session_history
WHERE session_id = 147
AND sample_time > SYSDATE-10/1440
ORDER BY 1;
Find SQL statement identified above SELECT sql_text, application_wait_time
FROM gv$sql
WHERE sql_id IN (
  SELECT sql_id
  FROM gv$active_session_history
  WHERE TO_CHAR(sample_time) = '04-DEC-07 08.36.09.094 AM'
  AND session_id = 147
  AND session_serial# = 1715);
 
Related Topics
Automated Workload Repository Report
DBMS_WORKLOAD_REPOSITORY
DBMS_XPLAN
ORADEBUG
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [80 users online]    © 2010 psoug.org