asked on

Oracle DB 12.1.0.2.0, v$active_session_history

Need a query to go against v$active _session_history to calculate the current active sessions, so that we can alert on it..
I have attached a screen shot of the time period between Feb 3rd 9pm to Feb 4th 12 am, which shows a spike of about 310 active sessions.
The first screen shot shows about 300 + active sessions and second screen shot shows that there are only 4 o5 active sessions.
I need a query that will show me that value of 300+ active sessions that occurred the day before..
Can you please help?
-3DFF9851DD9A1B32.bmp
-2E62494C4F96C6.bmp

Sean Stuber

SELECT sample_id, MAX(sample_time), COUNT(DISTINCT session_id)
FROM v$active_session_history
WHERE sample_time >= TRUNC(SYSDATE) - 1
AND sample_time < TRUNC(SYSDATE)
GROUP BY sample_id
HAVING COUNT(DISTINCT session_id) > 300
ORDER BY 2;

Rao_S

ASKER

I did not get any rows..
I ran this..
SELECT sample_id, max(sample_time), COUNT(DISTINCT session_id)
FROM v$active_session_history
GROUP BY sample_id
HAVING COUNT(DISTINCT session_id) > 9
order by 2
I got..
SAMPLE_ID      MAX(SAMPLE_TIME)        COUNT(DISTINCTSESSION_ID)
42,440,500      2/5/2016 11:24:02.715 AM        10
42,441,338      2/5/2016 11:38:03.896 AM        10

I probably need to find the correct sample time..

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

Thank you, sdstuber, that worked query worked great!