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
Ls Cheng <exriscer@xxxxxxxxx> 于2021年11月1日周一 上午5:52写道：
Hi
You want to estimate AAS or number of user sessions?
Because on one hand you say "current concurrent connections", that is the
number of user sessions no matter active or inactive but on the other hand
you are using "v$session where status = 'ACTIVE'".
Maximum concurrent connections is more limited by the server memory than
CPU IMHO.
AAS is more limited by the number of CPUs IMHO.
Thanks
On Sun, Oct 31, 2021 at 1:34 PM Quanwen Zhao <quanwenzhao@xxxxxxxxx>
wrote:
Yes, actually benchmark is absolutely able to get the maximum concurrent
connections my expected. You know, oracle production system has been online
and it's impossible to do it. Here I have to estimate it using my approach
previously mentioned.
The max AAS value is *2.98* from ASH in last 1 hour and logic cpus is
*192*, and current concurrent connections is *60*, eventually I get the
maximum concurrent connections that is *3866* according to my formula.
By the way the attachment is the SQL script I use.
Best Regards
Quanwen Zhao
Mladen Gogala <gogala.mladen@xxxxxxxxx> 于2021年10月31日周日 下午2:55写道：
The number of affordable sessions primarily depends on what the sessions
are doing and the amount of available resources. To rephrase it, it depends
on the meaning of the word "affordable". It also depends on the meaning of
the word "is", as one of the former US presidents is known to have said. In
addition to that there is a dependency on the database configuration.
Pooled connections and shared servers can significantly increase the number
of concurrent sessions. In other words, reasonable answer can only be
provided by some type of benchmark.
On the other hand maximum usage since the instance startup means "the
maximum concurrent sessions", since the instance was started up. For me,
the phrase "since the last startup" usually means "in the last 3 months",
since we install DBRU 2 weeks after they are published. That IS the number
you are looking for: the maximum simultaneous sessions that have been
logged into the database, since the last startup.
As for that monstrosity of the script, I would advise using sar, vmstat
and iostat to measure CPU consumption, memory consumption and disk
consumption. Your "affordable sessions" will only be affordable as long as
you don't start swapping and burning 80% of CPU. I tend to believe the OS
tools more than the values from the Oracle performance tables. I remember
more than one case of inconsistent values in the performance tables.
On 10/31/21 02:21, Quanwen Zhao wrote:
Hello MGogala 😉,
I don't think that the column MAX_UTILIZATION in the view
V$RESOURCE_LIMIT represents the maximum usage as you can see its
description as follows:
*Maximum consumption of this resource since the last instance start-up*.
In other words the key information is "since the last instance start-up"
instead of the *AFFORDABLE* maximum value on oracle database, it just
indicates that oracle once increased to the value that is greatest than
ever before (perhaps the value is beyond the affordable scope of oracle
database). Now I need to find out the *CRITICAL* value (eg, the maximum
concurrent connections in my case).
BTW I use this SQL script to test AAS and Logic CPUs -
*https://github.com/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_logic_cpus_union_aas.sql*
<https://github.com/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_logic_cpus_union_aas.sql>,
you can see the section with *"in Real Time"*.
At this moment I realized my approach need to be rectified because AAS
is related to DB Time but DB Time just contains the time spending on
*FOREGROUND* processes, unfortunately the SQL query *select count(*)
from v$session where status = 'ACTIVE';* includes the BACKGROUND and
FOREGROUND processes. Please waiting for a while ...
Best Regards
Quanwen Zhao
Mladen Gogala <gogala.mladen@xxxxxxxxx> 于2021年10月31日周日 下午12:35写道：
On 10/30/21 23:35, Quanwen Zhao wrote:
Hey my folks and colleagues 😉,
For estimating the *maximum concurrent connections* on my oracle
database server I did the some work as follows:
- Acquiring *Average Active Sessions* & *Logic CPUs* in Real Time;
- Retrieving the *current concurrent connections* on view
*"v$session"*;
Via the above two steps I've got some data, such as, aas value is
*3.66* and logic cpus are *192*. Next I've got the current concurrent
connections (*61*) by running this SQL statement: *select count(*)
from v$session where status = 'ACTIVE';*
Thus I am able to estimate the maximum concurrent connections using the
following formula: aas value / logic cpus = current concurrent
connections / maximum concurrent connections. That is to say, *3.66 /
192 = 61 / maximum concurrent connections*, so which is *3200*. Is it
reasonable to this approach?
Best Regards
Quanwen Zhao
That is approximately as efficient as hunting grizzly bears with a
sewing needle. There is a table named V$RESOURCE_LIMIT which has
MAX_UTILIZATION column. The table is described here:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-RESOURCE_LIMIT.html#GUID-AC182B2E-8D26-43D7-A356-BE1F15BDF152
Thanks for reminding to switch my documentation bookmark from 12.2 to
19c, I'll do that tomorrow. As for the grizzly bears, everybody knows that
they should only be hunted with your bare hands. The same applies to their
black cousins.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
-- //www.freelists.org/webpage/oracle-l
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
