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.
>

Got it, thanks, Jonathan! By the way I'm not sure if you have seen the followed up emails between Denis and me.

Usually I use the following SQL statements to check AAS in Last 1 Hour.

• Average Active Sessions & Logic CPUs in Last 1 Hour.
  > SET LINESIZE 200
  > SET PAGESIZE 200
  > COLUMN snap_date_time FORMAT a19
  > COLUMN stat_name FORMAT a25
  > COLUMN stat_value FORMAT 999,999.99
  > ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
  > WITH aas AS
  > (
  > SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time
  > , metric_name
  > , ROUND(value, 2) aas
  > FROM v$sysmetric_history
  > WHERE metric_name = 'Average Active Sessions'
  > AND group_id = 2
  > ),
  > oscpu AS
  > (
  > SELECT stat_name
  > , value
  > FROM v$osstat
  > WHERE stat_name = 'NUM_CPUS'
  > )
  > SELECT s.snap_date_time -- the group column
  > , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name -- the series
  > column
  > , u.value stat_value -- the value column
  > FROM oscpu u -- "oscpu" has only a row, so using "oscpu" and "aas" to
  > join each other to acquire the column "snap_date_time" of "aas".
  > , aas s
  > UNION ALL
  > SELECT snap_date_time -- the group column
  > , metric_name stat_name -- the series column
  > , aas stat_value -- the value column
  > FROM aas
  > ORDER BY stat_name DESC
  > , snap_date_time
  > ;
  >
  >
  Ok, it makes no sense that I mentioned "maximum active sessions" which covers various situations you described in the previous thread. Actually I wanna estimate MAXIMUM CONCURRENT CONNECTIONS that is PROCESSES from v$parameter, because the App teams always ask me if your oracle database server has an affordable maximum concurrent connections (cpu cores is 96, and logic cpus is 192). I think that value is related to CPU cores but I'm not sure how many I should set although oracle doesn't do an obvious limit.

Best Regards
Quanwen Zhao

Jonathan Lewis <jlewisoracle_at_gmail.com> 于2021年11月8日周一 下午5:24写道：

>
> >> 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