Re: How to check number of Concurrent session from AWR
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: "jkstill@xxxxxxxxx" <jkstill@xxxxxxxxx>
- Date: Fri, 8 Nov 2013 11:52:35 -0800 (PST)
Sorry
My Id was locked and so finally resolve the Yahoo login. My question was to
create a report that can tell as what is the max concurrent session in any
snapshot so that daily report can be created at the end of the day to
understand as what was the maximum number of session in a day. Actually has new
application where we have session set to 3500 but saw at some point of the day,
it is reaching close to the max session. AWR is set for 2 month data and so
want to check as how many times in last 2 month of data we have reached closed
to max session limits.
I look to some suggestion and created a query which look like is goot to my
requirements
select a.snap_id AWR_SNAP
to_char(b.begin_interval_time,'dd-mon-yyyy hh24:mi:ss') ,
to_char(b.end_interval_time,'dd-mon-yyyy hh24:mi:ss') ,
a.resource_name,max_utilization
from sys.wrh$_resource_limit A, sys.wrm$_snapshot b
where a.resource_name like '%processes%'
and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and a.instance_number=1
and b.begin_interval_time > sysdate - 10;
Any comments are welcome.
Tx
Sanjay
On Wednesday, October 30, 2013 8:39 PM, Jared Still <jkstill@xxxxxxxxx> wrote:
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:
