Re: How to check number of Concurrent session from AWR
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Sanjay Mishra <smishra_97@xxxxxxxxx>
- Date: Wed, 30 Oct 2013 17:37:27 -0700
I had reason to play with this today.
The following script attempts to determine the AAS for each period of N
minutes from v$active_session_history
It will also work on DBA_HIST_ACTIVE_SESS_HISTORY, but may take a long time
on system with many sessions.
Not exactly what you asked for, but you can't easily get *exactly* what you
asked for.
Corrections and improvements welcomed
==================================================================
set line 200
col sample_id format 999999999
col sample_time format a35
col prev_sample_time format a30
col sample_id_diff format 9999 head 'SMPLL|ID|DIFF'
col aas format 99999.9
def n_interval_minutes=5
--set pause on
with ashdata as (
select distinct
-- 15 minute interval
ash.sample_id
-- number of sessions in sample
, count(sample_id) over (partition by sample_id order by sample_id)
session_count
, trunc(ash.sample_time,'DD') sample_time
, ( extract (hour from ash.sample_time - trunc(ash.sample_time,'DD')
) * 60 * 60)
+ (
( extract (minute from ash.sample_time -
trunc(ash.sample_time,'hh24') ) * 60 )
- mod(extract (minute from ash.sample_time -
trunc(ash.sample_time,'hh24') ) * 60, (&&n_interval_minutes*60))
) seconds
from V$ACTIVE_SESSION_HISTORY ash
-- may take a long time on active system
--from DBA_HIST_ACTIVE_SESS_HISTORY ash
),
-- correct the date - add the seconds
ashdc as (
select
a.sample_id
, a.session_count
, a.sample_time + ( decode(a.seconds,0,1,a.seconds) / (24*60*60))
sample_time
from ashdata a
),
interval_aas as (
select distinct
a.sample_time
, sum(a.session_count) over (partition by sample_time order by
sample_time) sessions
, count(a.sample_id) over (partition by sample_time order by
sample_time) sample_count
from ashdc a
)
select
sample_time
, sessions / sample_count aas
from interval_aas
order by sample_time
/
===============================================================================
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
On Mon, Oct 28, 2013 at 12:48 PM, Sanjay Mishra <smishra_97@xxxxxxxxx>wrote:
> Hi
> What is best way to check max number of concurrent session reported in a
> specific AWR snapshot. Need to check number of concurrent session at some
> outage time and so need to compare if the number of concurrent session
> reported in any snapshot are drammatically high then other
>
> Sanjay
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
