Find Historical Session SQL Details

Today my database suffered some very poor performance. Drilling down using OEM, I could see that a specific session was causing high shared pool latch contention. But what was it running to cause that? Using the DBA_HIST_ACTIVE_SESS table can be used to get this info out of the DBA.

--
-- List Session Details for a Given Time Period for Specific Session
--
-- start_time format = '01/JAN/2017 04:00:00' 
-- end_time format = '01/JAN/2017 05:00:00'  
-- inst_no = Instance Number for RAC.  Use 1 for non RAC
-- sid = session ID from OEM (optional value)
--
SET LONG 300
SET PAUSE ON
SET PAUSE 'Press Return To Continue'
SET HEADING ON
SET LINESIZE 250
SET PAGESIZE 60

COLUMN Sample_Time FOR A20
COLUMN username FOR A10
COLUMN sql_fulltext FOR A120
COLUMN module FOR A50

SELECT
   to_char(sample_time, 'DD/MON/YYYY HH24:MI:SS'),
   u.username,
   h.module,
   s.sql_fulltext
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   v$SQL s
WHERE  sample_time
BETWEEN '&start_time' and '&end_time'
AND
   INSTANCE_NUMBER=&inst_no
   AND h.session_id=nvl('&sid',h.session_id)
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
ORDER BY 1
/
/

Leave a Reply