Re: How to check number of Concurrent session from AWR

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_at_gmail.com> 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_at_yahoo.com>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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l