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

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
This 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
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

SQL2. Background

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'

_____________________________________________________________________________________________________________________

0 comments:

Post a comment

 

acehints.com Copyright 2011-20 All Rights Reserved | Site Map | Contact | Disclaimer