Hey my folks and colleagues ,
For estimating the maximum concurrent connections on my oracle database server I do the some work as follows:
Acquiring Average Active Sessions & Logic CPUs in Real Time;
Retrieving the current concurrent connections on view "v$session" several times;
Via the above steps I'll get some data, please take a look at my operations below:
As you can see from the graph, aas value is 3.66 and logic cpus are 192. Next I've got the current concurrent connections (61) as below:
20:07:34 SQL> select count(*) from v$session where status = 'ACTIVE';
COUNT(*)
----------
59
20:07:52 SQL> /
COUNT(*)
----------
59
20:07:57 SQL> /
COUNT(*)
----------
59
20:07:58 SQL> /
COUNT(*)
----------
61
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
P.S: using the following SQL script to visualize the graph by the user custom report of Oracle SQL Developer 21.2.
-- Average Active Sessions & Logic CPUs in Real Time.
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
;