Average Active Sessions (AAS)

 

Top 10 queries from v$active_session_history

select * from (
	select
		 SQL_ID ,
		 sum(decode(session_state,'ON CPU',1,0)) as CPU,
		 sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
		 sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
		 sum(decode(session_state,'ON CPU',1,1)) as TOTAL
	from v$active_session_history
	where SQL_ID is not NULL
	group by sql_id
	order by sum(decode(session_state,'ON CPU',1,1))   desc
	)
where rownum <11

This query return top queries by resources consumed, you can easily return the top I/O, WAITS or CPU queries by changing the order by clause.

-- Active Sessions (in ASH) including BACKGROUND processes in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN sample_time FORMAT a19
COLUMN stat_name   FORMAT a15

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ash AS
(
  SELECT TRUNC(sample_time, 'mi') sample_time
         -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi') sample_time
       , 'Active Sessions' stat_name
       , ROUND(COUNT(*)/6e1, 2) stat_value
  FROM v$active_session_history
  WHERE session_type IN ('BACKGROUND', 'FOREGROUND')  -- including background processes
  GROUP BY TRUNC(sample_time, 'mi')
           -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi')
  ORDER BY sample_time
)
SELECT * FROM ash
WHERE sample_time >= SYSDATE - INTERVAL '60' MINUTE
;

-- Active Sessions (in ASH) including BACKGROUND processes in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN sample_time FORMAT a19
COLUMN stat_name   FORMAT a15

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ash AS
(
  SELECT TRUNC(sample_time, 'mi') sample_time
         -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi') sample_time
       , 'Active Sessions' stat_name
       , ROUND(COUNT(*)/6e1, 2) stat_value
  FROM v$active_session_history
  WHERE session_type IN ('BACKGROUND', 'FOREGROUND')  -- including background processes
  GROUP BY TRUNC(sample_time, 'mi')
           -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi')
  ORDER BY sample_time
)
SELECT * FROM ash
WHERE sample_time >= SYSDATE - INTERVAL '60' MINUTE
;

-- Active Sessions (in ASH) excluding BACKGROUND processes in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN sample_time FORMAT a19
COLUMN stat_name   FORMAT a15

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ash AS
(
  SELECT TRUNC(sample_time, 'mi') sample_time
         -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi') sample_time
       , 'Active Sessions' stat_name
       , ROUND(COUNT(*)/6e1, 2) stat_value
  FROM v$active_session_history
  WHERE session_type = 'FOREGROUND'  -- excluding background processes
  GROUP BY TRUNC(sample_time, 'mi')
           -- TO_CHAR(CAST(sample_time AS DATE), 'yyyy-mm-dd hh24:mi')
  ORDER BY sample_time
)
SELECT * FROM ash
WHERE sample_time >= SYSDATE - INTERVAL '60' MINUTE
;


SQL> select count(*) from v$session where status = 'ACTIVE';

  COUNT(*)
----------
        77

SQL> select count(*) from v$session where status = 'ACTIVE' and username is not null;

  COUNT(*)
----------
         1
SQL>  select inst_id,count(*) from gv$session where username is not null group by inst_id;

   INST_ID   COUNT(*)
---------- ----------
         1          5
         2          4
SQL>  select count(*) from v$session where status = 'ACTIVE' and type = 'USER';

  COUNT(*)
----------
         1


select 	(act.WAITING*100/act.TOTAL) as WAITING_PCT,
		(act.CPU*100/act.TOTAL) as CPU_PCT,
		(act.IO*100/act.TOTAL) AS IO_PCT
from
	(select
		 sum(decode(session_state,'WAITING',0,1)) "CPU",
		 sum(decode(session_state,'WAITING',1,0)) -  sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "WAITING" ,
		 sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "IO",
		 sum(decode(session_state,'WAITING',1,1)) "TOTAL"
	from v$active_session_history) act;


select * from (
select
     session_id,
	 session_serial#,
     program,
	 module,
	 action,
     sum(decode(session_state,'WAITING',0,1)) "CPU",
     sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "WAITING" ,
     sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0),0)) "IO" ,
     sum(decode(session_state,'WAITING',1,1)) "TOTAL"
from v$active_session_history 
where session_type='FOREGROUND'
group by session_id,session_serial#,module,action,program
order by sum(decode(session_state,'WAITING',1,1)) desc)
where rownum <11



Top 5 wait events from v$active_session_history
select * from (
	select
		 WAIT_CLASS ,
		 EVENT,
		 count(sample_time) as EST_SECS_IN_WAIT
	from v$active_session_history
	where sample_time between sysdate - interval '1' hour and sysdate
	group by WAIT_CLASS,EVENT
	order by count(sample_time) desc
	)
where rownum <6





How to release a blocking Session:
Ge the SID details:
select sid,SERIAL#,status,username from v$session where sid=<Blocking Session>;
select SID,MACHINE,TERMINAL,PROGRAM,MODULE from v$session where sid=<Blocking Session>;

Disconnecting the Session:
alter system disconnect session '<SID>,<Serial#>' IMMEDIATE;

Corrective Action:
For a blocking session only two corrective actions:
    1. Disconnect the blocking session
    2. Wait for completing the blocking session

Preventive Action:
Application has to be designed/corrected as no two or more sessions required the same data at the same time to be modified.