Script to Find Most Active Session in Oracle Database for Last One Hour

advertisements

_____________________________________________________________________________________________________________________

These SQLs to identify most active sessions in the oracle database for last one hour. Two queries mentioned here for the background and foreground processes. 

SQL1. Foreground Process

SET PAGES 10000 LINES 222
COL SQL_ID FOR A15
col SQLTEXT for a60
SELECT sa.sql_id,sa.sql_text SQLTeXT,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history hist, Gv$sqlarea sa
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'FOREGROUND'
AND sa.sql_id=hist.sql_id
GROUP BY sa.sql_id,sa.sql_text
ORDER BY COUNT(*) DESC;

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

advertisements

 

SQL2. Background Process

SET PAGES 10000 LINES 222
COL SQL_ID FOR A15
col SQLTEXT for a60
SELECT sa.sql_id,sa.sql_text SQLTeXT,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history hist, Gv$sqlarea sa
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'BACKGROUND'
AND sa.sql_id=hist.sql_id
GROUP BY sa.sql_id,sa.sql_text
ORDER BY COUNT(*) DESC;
SQL_ID  SQLTEXT              COUNT(*)    PCTLOAD
--------------- ------------------------------------------------------------ ---------- ----------
5yv7yvjgjxugg select TIME_WAITED_MICRO from V$SYSTEM_EVENT  where event =       1        .25
  'Shared IO Pool Memory'

_____________________________________________________________________________________________________________________

Website Stats