Re: Getting Oracle connected sessions in the past

From: Franck Pachot <franck_at_pachot.net>
Date: Wed, 27 Jul 2016 18:17:21 +0000
Message-ID: <CAK6ito3_rcWEwNt5mQ-F7fCODXM85yJD_cNxk_MTfLD5PsJZwA_at_mail.gmail.com>



Hi,
If you add the DB time (active sessions) and the SQL*Net message from client (inactive ones) and divide by elapsed time then you have an average of user sessions connected.
You can check the session logons statistic to see if they are roughly constant over the time window.
Regards,
Franck
Le mer. 27 juil. 2016 à 15:03, Ram Cheruvattath <ram.cheruvattath_at_gmail.com> a écrit :

> DBA_HIST_ACTIVE_SESS_HISTORY only captures information on sessions that
> are active, either on CPU or waiting for something. It does not capture
> idle sessions. So I am not sure you can get what you were originally
> looking for (a count of all connected sessions) using this view.
>
> auditing is the only way I know of to get such information.
>
> Ram
>
> *From:* Luis Santos <lsantos_at_pobox.com>
> *Sent:* Wednesday, July 27, 2016 8:32 AM
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> *Subject:* Re: Getting Oracle connected sessions in the past
>
> Craig, thanks a lot! That was a good match! I was only thinking that,
> as DBA_HIST_ACTIVE_SESS_HISTORY was a much more fine granule
> than DBA_HIST_SNAPSHOT (default 10s versus default 1 hour), maybe a version
> with a delta between sample_time from DBA_HIST_ACTIVE_SESS_HISTORY could
> give us a richer info.
>
> Anyway I added a lillle cosmetic to your nice script.
>
>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> *col period format a50break on period skip 1select
>> dhs.BEGIN_INTERVAL_TIME||' --> '|| dhs.END_INTERVAL_TIME period,
>> du.username, count(distinct(SESSION_ID||','||SESSION_SERIAL#) ) cntfrom
>> dba_hist_active_sess_history dhash, DBA_HIST_SNAPSHOT dhs, dba_users
>> duwhere dhash.user_id = du.user_idand dhash.snap_id = dhs.snap_idand
>> dhs.BEGIN_INTERVAL_TIME > trunc(sysdate)group by dhs.BEGIN_INTERVAL_TIME,
>> dhs.END_INTERVAL_TIME, du.usernameorder by 1,3/*
>
>
>
>
> *--*
> *Att*
>
>
> *Luis Santos*
>
>
> 2016-07-27 9:15 GMT-03:00 Craig Simpson <craig.simpson_at_velocitycloud.com>:
>
>> Assuming you are licensed for diagnostics...
>>
>> for a 2 minute job it may do some of what you want but I'm sure you can
>> adapt it for your own needs
>> eg change what times you want to report on, this does everything from
>> today.
>>
>>
>> select dhs.BEGIN_INTERVAL_TIME, dhs.END_INTERVAL_TIME, du.username,
>> count(distinct(SESSION_ID||','||SESSION_SERIAL#) ) cnt
>> from dba_hist_active_sess_history dhash, dba_hist_snapshot dhs, dba_users
>> du
>> where dhash.user_id = du.user_id
>> and dhash.snap_id = dhs.snap_id
>> and dhs.BEGIN_INTERVAL_TIME > trunc(sysdate)
>> group by dhs.BEGIN_INTERVAL_TIME, dhs.END_INTERVAL_TIME, du.username
>> order by 1,3
>>
>> BEGIN_INTERVAL_TIME
>> END_INTERVAL_TIME
>> USERNAME CNT
>> ---------------------------------------------------------------------------
>> ---------------------------------------------------------------------------
>> ------------------------------ ----------
>> 27-JUL-16 09.00.31.243
>> 27-JUL-16 10.00.12.012
>> OWF_MGR 2
>> 27-JUL-16 09.00.31.243
>> 27-JUL-16 10.00.12.012
>> PERFSTAT 1
>> 27-JUL-16 09.00.31.243
>> 27-JUL-16 10.00.12.012
>> XXXXXXX 3
>> 27-JUL-16 09.00.31.243
>> 27-JUL-16 10.00.12.012
>> SYS 36
>> 27-JUL-16 09.00.31.243
>> 27-JUL-16 10.00.12.012
>> SYSMAN 6
>> 27-JUL-16 10.00.12.012
>> 27-JUL-16 11.00.01.601
>> DBSNMP 6
>>
>>
>> On 27 July 2016 at 12:59, Luis Santos <lsantos_at_pobox.com> wrote:
>>
>>> I was thinking about a smart query on DBA_HIST_ACTIVE_SESS_HISTORY,
>>> seeking for distinct USER_ID column.
>>>
>>> I have to be frank: I don´t have the skills to design such a query, and
>>> I´m looking for a ready, already developed one.
>>>
>>>
>>>
>>>
>>> *--*
>>> *Att*
>>>
>>>
>>> *Luis Santos*
>>>
>>>
>>> 2016-07-27 8:54 GMT-03:00 Howard Latham <howard.latham_at_gmail.com>:
>>>
>>>> You can use. If turned on the audit records
>>>> On 27 Jul 2016 12:49, "Luis Santos" <lsantos_at_pobox.com> wrote:
>>>>
>>>> I was guessing if there´s not an awr table that contains such info. I´m
>>>> aware of DBA_HIST_RESOURCE_LIMIT, but this is consolidated info.
>>>>
>>>>
>>>> *--*
>>>> *Att*
>>>>
>>>>
>>>> *Luis Santos*
>>>>
>>>>
>>>> 2016-07-27 8:39 GMT-03:00 S3v3n11 <neisdsa_at_gmail.com>:
>>>>
>>>>> dba_audit_session?
>>>>>
>>>>> On Wed, Jul 27, 2016 at 6:32 AM, Luis Santos <lsantos_at_pobox.com>
>>>>> wrote:
>>>>>
>>>>>> This simple query shows connected users in the moment on an Oracle
>>>>>> instance:
>>>>>>
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> *select username, count(*) from v$session group by usernameorder by
>>>>>>> 2*
>>>>>>
>>>>>>
>>>>>> Is there a way to get this info on a specific time in the past? I
>>>>>> know flashback queries does not work for V$ views (and this is absolutely
>>>>>> reasonable)...
>>>>>>
>>>>>> *--*
>>>>>> *Att*
>>>>>>
>>>>>>
>>>>>> *Luis Santos*
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>>
>> --
>>
>> *Craig Simpson | **Velocity Technology Solutions, Ltd.*
>>
>> *Database Team*
>>
>> 39 Cadogan Street | 5th Floor | Glasgow, G2 7AB
>> <https://www.google.com/maps/preview#!q=39+Cadogan+Street+%7C+5th+Floor+%7C+Glasgow%2C+G2+7AB&data=!4m15!2m14!1m13!1s0x4888469d042c2e69%3A0xe50daa21f9160909!3m8!1m3!1d8547!2d-73.9703852!3d40.7568975!3m2!1i1254!2i543!4f13.1!4m2!3d55.8598357!4d-4.2625208>
>>
>> Office: 0141-202-6334 | eMail: *craig.simpson_at_velocity.cc* | Website
>> www.velocity.cc
>>
>>
>>
>>
>> STATEMENT OF CONFIDENTIALITY:
>>
>> This email may be confidential and/or protected by privilege. If you are
>> not the intended recipient, disclosure, copying, distribution and/or use
>> are prohibited; please notify us immediately at counsel_at_velocitycloud.com and
>> delete this copy from your system.
>>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 27 2016 - 20:17:21 CEST

Original text of this message