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

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-1217
https://dbwhisperer.wordpress.com