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@xxxxxxxxx> 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/, here 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_2.png
https://quanwenzhao.files.wordpress.com/2021/11/active_sessions.png
Best RegardsQuanwen Zhao
denis.sun@xxxxxxxxx <dmarc-noreply@xxxxxxxxxxxxx> 于2021年11月8日周一 上午8:33写道:
Hi, Quanwen Zhao
because an AAS of 1 is equivalent to 100% of a CPU core