Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: ASH query - hour wise

    Hello all,

    I am using Oracle 11g r2 Enterprise edition , i want to generate a query to find total connections per hour per user/schema using ASH view v$active_session_history

    i have designed a query like:
    SELECT a.user_id,count(1),b.username FROM V$ACTIVE_SESSION_HISTORY A , dba_users b WHERE A.user_id = b.user_id group by a.user_id,b.username;

    but i am not able to extract hourly data. Please help in this concern.

    Thanks and regards
    sumit
    ssumit

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select to_char(sample_time, 'HH24'), count(*) from V$ACTIVE_SESSION_HISTORY group by to_char(sample_time, 'HH24');
    
    TO   COUNT(*)
    -- ----------
    17         93
    13        133
    18        142
    12        169
    11        241
    20        111
    19        136
    16        128
    15         45
    
    9 rows selected.
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    thanks anacedent , This will definitely help me.
    ssumit

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    V$ACTIVE_SESSION_HISTORY contains only sampled result, so may not reflect reality.

    Garbage In, Garbage Out (GIGO)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    as per oracle it sample session data per second, Can we assume that it may depict session per hour to some extent (close to actual).

    Is there any other way to estimate per user per hour sessions without any extra trigger or audit ?

    thanks in advance
    sumit
    ssumit

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does application utilize connection pooling (3-tier)?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •