Hi, We are using version 11.2.0.4 of Oracle. We have certain alerting mechanism in place since long back, for few parameters. E.g say CPU utilization crossing 90% and high I/O response etc. We just noticed few scenarios, we have got a query plan got changed(due to stats issue + binds) so the user session didn't finish and all incoming sessions gets queued up on same query consuming high CPU(100%+) thus affecting other queries/applications and we then fixed the old plan baseline to have a quick solution to avoid the end user impact. And later we fixed the stats issue. In another scenario, where sessions from one of the USER/SCHEMA increased significantly and we saw high CPU utilization+concurrency waits. After killing few of them things came back to normal.
So want to know from experts , as because all of these DB resource consumption can be measured by only one i.e. the "avg active session" yard stick in DB level and we are always seeing the "Avg active session" spikes on OEM top activity page during the issue period which is spot on,
1)so if its advisable to configure some alerts using "AVg Active sessions" based on the past activity trends for that user from dba_hist_active_sess_history/gv$active_session_history. Using query something as below for ~15minutes interval, so that we will get notified immediately(if it goes unusually high) and the turn around time can be minimized and things can be fixed before users complain so the user impact also be minimized? Or any other possible way is there to handle this?
2)Also thinking , if we will miss anything(specific sub-second query samples) if will opt for v$active_session_history? Then it will be a good idea to query v$session instead of v$active_session_history for this AAS alerting?
select trunc(sample_time,'hh24') + (trunc(to_char(sample_time,'mi')/15)*15)/24/60 , username , round(count(*)/(15*60)) AAS
from v$active_session_history a , dba_users b
where username = 'USER1' and a.user_id = b.user_id and sample_time > sysdate - 1/24
group by username , trunc(sample_time,'hh24') + (trunc(to_char(sample_time,'mi')/15)*15)/24/60
having round(count(*)/900) > 50 -- Avg active session >50
order by 1