ASH Reports- Scripts - Performance Tuning

Monitor sessions performance over last hour
                                            

• Info for sessions running a module over last hour

SELECT DECODE (session_state, 'WAITING', event, NULL) event, session_state, COUNT(*), 
SUM (time_waited) time_waited 
FROM v$active_session_history 
WHERE sample_time > SYSDATE - 1/24 
GROUP BY DECODE (session_state, 'WAITING', event, NULL), 
session_state ORDER BY time_waited DESC;

• Investigate one session running that module

SELECT DECODE (session_state, 'WAITING', event, NULL)   event,  session_state, COUNT(*) counter , 
SUM (time_waited) time_waited 
FROM v$active_session_history 
WHERE module = 'ARXENV' 
AND sample_time > SYSDATE - 1/24  
AND session_id = 276 
GROUP BY DECODE (session_state, 'WAITING', event, NULL), session_state   ORDER BY time_waited DESC;

• Join with v$sqlarea and include sql_stms for that session

SELECT  b.sql_text, DECODE (a.session_state, 'WAITING', a.event, NULL) "EVENT_NAME", a.session_state, COUNT(*) counter , 
SUM (a.time_waited) time_waited 
FROM v$active_session_history a  , V$SQLAREA b 
WHERE a.sample_time > SYSDATE - 1/24  
AND a.SQL_ID = b.SQL_ID 
AND a.module = 'ARXENV' 
AND  a.session_id = 276 
GROUP BY b.sql_text, 
DECODE (a.session_state, 'WAITING', a.event, NULL), a.session_state 
ORDER BY time_waited DESC;

• What SQL is currently using the most resources? 

SELECT active_session_history.user_id, 
dba_users.username, sqlarea.sql_text, 
SUM(active_session_history.wait_time + active_session_history.time_waited) total_wait_time 
FROM v$active_session_history active_session_history, 
v$sqlarea sqlarea, dba_users 
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60/2880 AND SYSDATE 
AND active_session_history.sql_id = sqlarea.sql_id 
AND active_session_history.user_id = dba_users.user_id 
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username 
ORDER BY 4;

• What object is currently causing the highest resource waits?

SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event, SUM(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history, dba_objects 
WHERE active_session_history.sample_time BETWEEN SYSDATE - 60/2880 AND SYSDATE 
AND active_session_history.current_obj# = dba_objects.object_id 
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event 
ORDER BY ttl_wait_time DESC;