Re: To estimate maximum active sessions on my oracle database is reasonable to the approach?
Date: Sun, 31 Oct 2021 14:21:11 +0800
Message-ID: <CABpiuuQXnhe=O86J2ubegVHKuozJMHkoPff_=wJ4K=0TZxphgg_at_mail.gmail.com>
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
Mladen Gogala <gogala.mladen_at_gmail.com> δΊ2021εΉ΄10ζ31ζ₯ε¨ζ₯ δΈε12:35ειοΌ
>
Quanwen Zhao
> 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
>
> -- http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Oct 31 2021 - 07:21:11 CET