Re: How to check number of Concurrent session from AWR

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 30 Oct 2013 17:37:27 -0700
Message-ID: <CAORjz=Mp5dcB2tyZCDOh3MVUXTqgY=CB-kgUT0y11OF8eyHCCg_at_mail.gmail.com>

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

Received on Thu Oct 31 2013 - 01:37:27 CET