Re: To estimate maximum active sessions on my oracle database is reasonable to the approach?

>> In your case I guess that AAS is *8* (CPUs) and background processes
are *3* according to your description because your query count(*) is *11*.

No. For that period the AAS is 11 because there are 11 samples in every sampling interval.

Denis gave you this definition:

AAS = db time / elapsed (wall clock) time, where db time = Database CPU time + Database wait time.

This is the "Analogue" version of AAS, but v$active_session_history is the "digital" model of the database activity with a granularity of 1 second; and the model is based on the assumption that if a session is active at the end of a sampling interval then it has spent the entire interval doing whatever it reported at that moment. For a sufficiently large number of the samples the digital ASS is a reasonable model of the analogue AAS.

So for any period of time the digital AAS = (total number of samples) / (count of sampling intervals).

As far as the distinction between background and foreground is concerned, sessions appear in v$active_session_history if their v$session.status = 'ACTIVE' and their v$session.wait_class != 'Idle', and background session are almost always in wait_class "Idle". which is why they rarely get into v$active_session_history even thought their status is 'ACTIVE'/

When you say "maximum active sessions" - does that mean since instance startup, or over a regular time interval, or for any randomly selected interval you might select, or something else ?

Have I mentioned that you might find what you want by looking at v$sysmetric_summary and v$sysmetric_history ?

Regards
Jonathan Lewis

On Sun, 7 Nov 2021 at 10:28, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:

> Thanks for your reply, Jonathan 😊.
>
> Yes, AAS can be equivalent to *sessions* from v$parameter. At this moment
> I think the value of AAS is probably equal to *CPU_COUNT* from
> v$parameter because sessions are the affordable value for oracle database
> currently. In your case I guess that AAS is *8* (CPUs) and background
> processes are *3* according to your description because your query
> count(*) is *11*.
>
> In other words I should go to estimate maximum concurrent connections
> (processes from v$parameter), here I assume that my oracle database server
> is *DEDICATED* server thus one session represents one foreground process.
> Concurrent conections should include ACTIVE and INACTIVE sessions by
> session status (or FOREGROUND and BACKGROUND processes by session type).
> Active sessions should only contain those who are running *ON CPU* by
> session_tate from v$active_session_history. Hence it makes no sense that we
> are discussing active sessions, am I right to the prior understanding?
>
> Another kind of thought if I am able to estimate one CPU is allowed to run
> the maximum processes then I'll know all cores of CPUs' maximum processes
> (maximum concurrent connections on my oracle database server).
>
> Best Regards
> Quanwen Zhao
>
> Jonathan Lewis <jlewisoracle_at_gmail.com> 于2021年11月6日周六 下午5:48写道：
>
>>
>> AAS can be as large as the sessions parameter (minus some value for the
>> background processes). Here's a query and result from an instance running
>> on a machine with 8 CPUs.
>>
>> SQL> select sample_time, count(*) from V$active_session_history where
>> sample_time > sysdate - 10/(24*60*60) group by sample_time order by 1;
>>
>> SAMPLE_TIME COUNT(*)
>> -------------------------------- ----------
>> 06-NOV-21 09.42.23.785 AM 11
>> 06-NOV-21 09.42.24.809 AM 11
>> 06-NOV-21 09.42.25.814 AM 11
>> 06-NOV-21 09.42.26.817 AM 11
>> 06-NOV-21 09.42.27.819 AM 11
>> 06-NOV-21 09.42.28.827 AM 11
>> 06-NOV-21 09.42.29.830 AM 11
>> 06-NOV-21 09.42.30.847 AM 11
>> 06-NOV-21 09.42.31.860 AM 11
>> 06-NOV-21 09.42.32.869 AM 11
>>
>> 10 rows selected.
>>
>>
>> A session doesn't have to be "doing" anything to be active, it only has
>> to be in the middle of a database call. In this case all the sessions are
>> waiting on an attempt to "lock table X in exclusive mode" when the table is
>> already locked.
>>
>> Regards
>> Jonathan Lewis.
>>
>>
>> P.S. In case no-one answered: "IMHO" means "in my humble opinion".
>>
>>
>>
>>
>> On Mon, 1 Nov 2021 at 01:30, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:
>>
>>> Concurrent is not matter ACTIVE?
>>>
>>> The logic cpus is 192 on my oracle database server, so AAS is allowed to
>>> increase to 192 because an AAS of 1 is equivalent to 100% of a CPU core.
>>> Since AAS has a maximum value I am also able to estimate the maximum
>>> concurrent connections using the current concurrent connections by the
>>> proportion with current AAS and logic cpus.
>>>
>>> By the way what's IMHO?
>>>
>>> Best Regards
>>> Quanwen Zhao
>>>
>>>
>>>

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