Activity Session History (ASH)
By now you must understand how important and useful the Automatic Workload Repository (AWR) is.
As a recap, AWR captures workload-related performance data at the user and system levels, including performance statistics by different dimensions, metrics, OS statistics, and ASH data at regular predetermined intervals.
Activity Session History (ASH) represents the history of the activities of all recent active sessions captured efficiently through a circular buffer in memory and efficiently written to AWR to incur minimal overhead. T
he ASH data can be rolled up by different dimensions: TOP SQL, object, file, session, module, action, and so on.

The ASH report can be used to target the entire database or a particular session, SQL_ID, module, action, or a combination of these dimensions.

In most cases ASH is better than good enough to identify problems, it is always ON and there is no need to try to replicate a problem, we’ve already recorded it.It's very efficient with 0 degradation for the database.
Remember that ASH is licensed as part of the Diagnostic pack and it's ON by default.






ASH Samples ‘Active’ sessions every second, it's like doing "select * from v$session_wait" w/o SQL.

One way to access the ASH report is from the Database page. Choosing the Performance tab will generate a screen similar to the following


Note the button (inside the red oval) labeled "Run ASH Report." Clicking on it brings up the Active Session History report: 



This screen allows you to put the date and time of the start and finish times of the period in which you're interested. Enter the date and time as needed and press the "Generate Report" button on the upper right. By default the date and time shows a 5-minute interval.
After you click the button, you will see the ASH report on the screen for that period. If you look carefully, you will see that the report resembles the STASPACK report; but since it comes from AWR data, the metrics in them are much more useful. A small portion of the screen is shown below:


You can save the report to a file for later viewing by pressing the button "Save to File."

Note the links in the section "ASH Report." Here you can see the different types of available performance-related statistics and metrics in one glance. For instance, you can see Top Events during the period only by clicking on that link. If performance issues come up within the period, this information will help substantially. You can generally identify bottlenecks that caused the transient spikes by looking at skews along the various dimensions listed in the ASH report.

Remember, this report is pulled from data collected by AWR or from in-memory buffers as appropriate; hence, if you want to diagnose a performance issue that occurred earlier, you could simply fire up the ASH report for that period and see any issues that might have surfaced then.

The ASH report can also be run through command line, by running the Oracle supplied SQL scripts located in $ORACLE_HOME/rdbms/admin
Imagine you are a DBA on a production system and get an emergency call like "The Database is dead slow!". You are supposed to spot the cause as soon as possible. ASH kicks in here: We sample the Wait-Events of active sessions every second into the ASH-Buffer. It is accessed most comfortable with the Enterprise Manager GUI from the Performance Page (Button ASH Report there). Or with little effort from the command line like this:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql;
Specify what type of report you would like, text or html.
The report will then request a beginning time for the report


You can then review your ASH report:
The first section will look similar for all databases:
DB Name DB Id Instance Inst num Release RAC Host
FGTST1 2330122768 FGTST1 1 11.2.0.3.0 NO fgdb1t
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
8 2,039M (100%) 192M (9.4%) 960M (47.1%) 16.0M (0.8%)

Sample Time Data Source
Analysis Begin Time: 04-Oct-13 10:02:32 V$ACTIVE_SESSION_HISTORY
Analysis End Time: 04-Oct-13 11:02:32 V$ACTIVE_SESSION_HISTORY
Elapsed Time: 60.0 (mins)  
Sample Count: 4,415  
Average Active Sessions: 1.23  
Avg. Active Session per CPU: 0.15  
Report Target: None specified  


The Top User Events are a solid place to view first:

Top User Events

Event Event Class % Event Avg Active Sessions
CPU + Wait for CPU CPU 92.98 1.14
db file sequential read User I/O 1.59 0.02
log file sync Commit 1.13 0.01

Top Background Events

Event Event Class % Activity Avg Active Sessions
CPU + Wait for CPU CPU 1.49 0.02
log file parallel write System I/O 1.25 0.02

Top Event P1/P2/P3 Values
Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
db file sequential read 1.59 "5","1013701","1" 0.02 file# block# blocks
log file parallel write 1.25 "2","38","2" 0.14 files blocks requests
log file sync 1.13 "1439","1575570377","0" 0.02 buffer# sync scn NOT DEFINED

The query to inspect the object involved in the blocks would look like this:
select owner, segment_name, segment_type
from dba_extents
where file_id = &p1
and &p2 between block_id and block_id + blocks -&p3;

OWNER  SEGMENT_NAME SEGMENT_TYPE
------ ------------ ------------
USER1  PTBL1        TABLE

It will also tell us the types of commands utilizing the database most:

Top SQL Command Types

SQL Command Type Distinct SQLIDs % Activity Avg Active Sessions
UPDATE 3 88.24 1.08
SELECT 28 6.34 0.08


And the exact statements that were in the “TOP”, note that it will break it down by percentage of wait type per statement. If there is more than one that the statement falls under,
it will show show each wait with percentage:

Top SQL with Top Events

SQL ID Planhash Sampled # of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text
fb69fu8w7argp 240554356 2 88.11 CPU + Wait for CPU 88.11 HASH JOIN 87.70 UPDATE ACCOUNT SET ISCOMPLETE ...
3c0dfgruf2sdj 1317339463 1 2.76 CPU + Wait for CPU 2.51 SORT - AGGREGATE 1.52 /* SQL Analyze(2) */ select /*...
dqtbktv8s7g6k 3033207482 56 1.29 db file sequential read 1.18 INDEX - RANGE SCAN 1.13 SELECT (SELECT COUNT(1) FROM T...


Another valuable area is PLSQL Subprograms:

PLSQL Entry Subprogram                                            % Activity
----------------------------------------------------------------- ----------
PLSQL Current Subprogram                                           % Current
----------------------------------------------------------------- ----------
USER1.PROC_d                                                        5.61
USER1.PROC_h                                                        5.61
USER2.PROC_b                                                        3.39
USER1.TRIGGER_a                                                     1.69


Useful queries based on ASH
--------------------------------------------
-- Top 10 CPU consumers in last 5 minutes
--------------------------------------------
select * 
 from (select session_id, session_serial#, count(*)
         from v$active_session_history
         where session_state= 'ON CPU' 
           and sample_time > sysdate - interval '5' minute
         group by session_id, session_serial#
         order by count(*) desc
       )
where rownum <= 10;

SESSION_ID SESSION_SERIAL#   COUNT(*)
---------- --------------- ----------
         3               1          3


--------------------------------------------
-- Top 10 waiting sessions in last 5 minutes
--------------------------------------------
select * 
 from (select session_id, session_serial#,count(*)
         from v$active_session_history
         where session_state='WAITING'  
           and sample_time >  sysdate - interval '5' minute
         group by session_id, session_serial#
         order by count(*) desc
       )
where rownum <= 10;


These 2 queries should spot the most incriminating sessions of the last 5 minutes. But who is that and what SQL was running?
--------------------
-- Who is that SID?
--------------------
set lines 200
col username for a10
col osuser for a10
col machine for a10
col program for a10
col resource_consumer_group for a10
col client_info for a10

select serial#, username, osuser, machine, program, resource_consumer_group, client_info
from v$session 
where sid=&sid;

-------------------------
-- What did that SID do?
-------------------------
select distinct sql_id, session_serial# 
from v$active_session_history
where sample_time >  sysdate - interval '5' minute
  and session_id=&sid;

----------------------------------------------
-- Retrieve the SQL from the Library Cache:
----------------------------------------------
col sql_text for a80
SQL> select sql_text from v$sql where sql_id='&sqlid';


----------------------------------------------
-- ASH:Top SQL Over Last 5 Minutes
----------------------------------------------
select NVL(sql_id,'NULL') as sql_id
      ,count(*)           as DB_time
      ,ROUND(100*count(*) / SUM(count(*)) OVER (), 2)  as Pct_load
  from v$active_session_history 
 where sample_time > sysdate - 5/24/60
   and session_type <> 'BACKGROUND'
 group by sql_id
 order by count(*) desc;

SQL_ID        DB_TIME                PCT_LOAD               
------------- ---------------------- ---------------------- 
88v077cs94gak 136                    43.17                  
4xvts5kvsf1w8 89                     28.25                  
8pcw7z5vvhfj0 7                      2.22                   
dbm33sd7kv9s3 5                      1.59                   
572fbaj0fdw2b 5                      1.59                   



----------------------------------------------
-- ASH: Top *anything* SQL
----------------------------------------------
select ash.sql_id, 
       (select distinct decode( count(distinct sql_text) over (), 
                                1, substr(sql_text,1,80)||'...', 
                                'more than one sql matched' )
          from v$sql 
         where sql_id = ash.sql_id) sql, 
       count(*)
  from v$active_session_history ash, v$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; 


--------------------------------------------------------------------------------------------
-- ASH: SQL by total CPU plus wait time, broken down by CPU, IO wait and non-IO wait
--------------------------------------------------------------------------------------------
select sql_id,
      (select distinct decode( count(distinct sql_text) over (), 
              1, substr(sql_text,1,80)||'...', 'more than one sql matched' )
          from v$sql
         where sql_id = ash.sql_id) sql,
       cpu, non_io_wait, io_wait
  from (
select ash.sql_id,
       sum(case when ash.session_state = 'ON CPU' then 1 else 0 end ) cpu,
       sum(case when ash.session_state='WAITING' and ash.event#<>ev.user_io then 1 else 0 end) non_io_wait,
       sum(case when ash.session_state = 'WAITING' and ash.event# =  ev.user_io then 1 else 0 end ) io_wait
  from v$active_session_history ash,  
      (select event# user_io from v$event_name where wait_class = 'User I/O') ev
 group by ash.sql_id
       ) ash
 order by cpu+non_io_wait+io_wait desc;



--------------------------------------------------------------------------------------------
-- ASH: Look at the last hour, And for each event Find the min/max sample times 
--      Add up the wait time for that event, And report the largest waited for things first by Waits
--------------------------------------------------------------------------------------------
select ash.event,
       min(sample_time) start_time, 
       max(sample_time)-min(sample_time) duration,
       round(sum(ash.wait_time+ash.time_waited)/1000000,2) wait_secs
  from v$active_session_history ash
 where ash.sample_time between 
            systimestamp-numtodsinterval(1,'hour') and systimestamp
 group by ash.event
 order by wait_secs desc;
 

--------------------------------------------------------------------------------------------
-- ASH: Look at the last hour, And for each event Find the min/max sample times 
--      Add up the wait time for that event, And report the largest waited for things first by Session
--------------------------------------------------------------------------------------------
select ash.session_id,
       au.username,
       round(sum(ash.wait_time+ash.time_waited)/1000000,2) wait_secs
  from v$active_session_history ash,
       all_users au
 where ash.sample_time >= systimestamp-numtodsinterval(1,'hour')
   and ash.user_id = au.user_id
 group by ash.session_id, au.username
 order by wait_secs DESC;


--------------------------------------------------------------------------------------------
-- ASH: Look at the last hour, And for each event Find the min/max sample times 
--      Add up the wait time for that event, And report the largest waited For a given session, by sql statement used in that session
--------------------------------------------------------------------------------------------
select (select distinct decode( count(distinct sql_text) over (),
      1, substr(sql_text,1,80)||'...', 'more than one sql matched' )
         from v$sql
        where sql_id = ash.sql_id) sql,
       round(sum(ash.wait_time+ash.time_waited)/1000000,2) wait_secs
  from v$active_session_history ash
 where ash.session_id = 1065
 group by ash.sql_id
 order by wait_secs DESC