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

Very appreciated for your nice detail on AAS and other factor such as Wait, IO, end user's experience and so on, Denis 😉 ! It seems like I have deeper understanding on AAS (it's a constant value) than before.

I am very sorry that my real intention is that I wanna estimate *MAXIMUM CONCURRENT CONNECTIONS* (it might make me confusion about the combination graph with AAS and Logic CPUs from EMCC, I suddenly realized that it's possible to have some relationship on AAS and CPUs due to the *RED LINE* of Logic CPUs but actually they have no any relationship.

Yes, I'm not sure about the maximum processes (affordable value for my oracle database server) in v$parameter thus I need to estimate *maximum concurrent connections*. Eventually it makes no sense that I know estimating the value of *MAXIMUM ACTIVE SESSIONS*.

By the way if CPU load (press *W* from my keyword on Linux then you'll find out it) increases to *192* at this very moment the current concurrent connections is the maximum concurrent connections (select count(*) from v$session) ?

Best Regards
Quanwen Zhao

denis.sun_at_yahoo.com <denis.sun_at_yahoo.com> 于2021年11月9日周二 上午12:34写道：

> hi, Quanwen,
>
> If I rephrase your original question as the following:
>
> Given the number of CPU cores or threads the database server has, can I
> estimate a reasonable maximum active session value my database can have?
>
> My answer to this particular question is that there is no relationship
> between AAS and # of CPU, if AAS is constantly/frequently greater than #
> CPU, that situation we need to pay attention to. It MAY indicate you have
> CPU bottleneck. Whether it is reasonable or not, we need to check wait
> event and end user's experience.
>
> Suppose you have 192 CPUs, then at AAS=50, you can still get complains
> that database is slow from your users. It is most likely not due to CPU
> bottleneck, but could be due to I/O, network, configuration, application
> lock etc; If you see AAS=500, does that mean it is bad? All depends. like
> how long it lasts? Are all 500 active sessions doing CPU-intensive
> workload, (on or wait on CPU)?, what are end users' expectations (SLO for
> such particular workload)? etc
>
> AAS + Wait event + end user experience/service level objective all
> matters.
>
>
> BTW, I certainly know the difference between "Reply' and "Reply ALL". I
> cannot include previous messages is due to I use my work email to receive
> Oracle-L but I am not allowed to send outside email using it (company
> policy, even I cannot open yahoo email with my work PC to copy/paste
> between work/personal email). you see I know how to "Rely All" with my
> yahoo email account :).
>
> Denis
>
>
>
> On Sunday, November 7, 2021, 10:27:01 PM EST, Quanwen Zhao <
> quanwenzhao_at_gmail.com> wrote:
>
>
> Thanks for your thread, Denis 😉. You just click "Reply All" instead of
> "Reply" then all of people subscribed "Oracle-L" will see our stuff from
> their emails respectively. not just Google email? I see your email is from
> yahoo.
>
> Sorry, I have to clarify that my oracle database server has *192* number
> of *logic CPUs* (in other words *96 cores* of physical CPUs rather than
> *382* you mentioned previously). By the way I just quoted
> *"because an AAS of 1 is equivalent to 100% of a CPU **core"* from
> *http://datavirtualizer.com/oracle-cpu-time/*
> <http://datavirtualizer.com/oracle-cpu-time/>, *here*
> <https://quanwenzhao.files.wordpress.com/2021/11/oracle_cpu_time.png> is
> the screenshot.
>
> At least why I cited that AAS (AAS by *per wait class* or *per
> session_state and wait_class*) has any relationship to *CPU cores or
> threads* because I see the below *3* graphs from EMCC.
>
> *https://quanwenzhao.files.wordpress.com/2021/11/aas_by_per_wait_class.png*
> <https://quanwenzhao.files.wordpress.com/2021/11/aas_by_per_wait_class.png>
>
>
> *https://quanwenzhao.files.wordpress.com/2021/11/aas_by_per_wait_class_2.png*
> <https://quanwenzhao.files.wordpress.com/2021/11/aas_by_per_wait_class_2.png>
>
> *https://quanwenzhao.files.wordpress.com/2021/11/active_sessions.png*
> <https://quanwenzhao.files.wordpress.com/2021/11/active_sessions.png>
>
> Best Regards
> Quanwen Zhao
>
> denis.sun_at_yahoo.com <dmarc-noreply_at_freelists.org> 于2021年11月8日周一 上午8:33写道：
>
> Hi, Quanwen Zhao
>
> >> because an AAS of 1 is equivalent to 100% of a CPU core
>
> I don't think AAS has any relationship to the number of CPU cores or the
> utilization of CPU core ( 100% or 0%).
>
> AAS = Average Active Sessions
> AAS = DB Time/Elapsed Time(wall clock)
> DB Time = CPU Time + non-idle wait time
>
> ie if wall clock is 15 min, accumulated DB time from all sessions during
> that 15 min time period is 150 min, then AAS= 10 , it does not matter how
> many CPU cores you have.
>
> When you have 8 CPU cores and you observe you have AAS=11 or count=11 (
> from select count(*) from v$session where state='ACTIVE', I imagine this
> count as AAS in 1 second wall clock time, instantaneous AAS ), From what
> you described, you seemed interpret this as you must have 8 sessions
> on CPU 100%. This is not true completely. It can be one active session
> hold a DML lock on a table row, all other 9 active sessions waiting for
> this lock. nothing to do with CPU count or utilization. ( This is what
> Jonathon Lewis's example tells us. sorry I cannot include
> Jonathon's reply in my reply because I use different emails to
> receive and send to oracle-l)
>
>
> If your intended purpose is to estimate what are the maximum or reasonable
> AAS your database can have. I think only empirical approach makes sense. if
> you have CPU=8 then monitor from AAS=2xCPU= 16; if your CPU=192, I don'
> think monitor from 382 makes sense, instead monitor from 20. Then
> correlating with application metrics.
>
> In one of our production db, we have 24 CPUs, and I receive average active
> session alert greater than 200 from time to time, the system does not scale
> well obviously but no one cares :) no complains, app team seems happy with
> what the database deliver to them.
>
> So I think AAS is a good metrics to monitor db but need to correlate with
> other metrics. ( Look at OEM performance page, AAS with wait event in the
> same graph, this is ideal)
>
> Best regards,
>
> Denis
>
>

--
http://www.freelists.org/webpage/oracle-l