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

In Memory His Opinions are well reasoned and usually Optimal within the budget allowed.  

In context, humble depends: If JL were to write he’s in the top 42 Oracle performance analysts on the planet, that would be extremely humble, IMHO.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Quanwen Zhao Sent: Sunday, November 07, 2021 11:34 PM To: Jonathan Lewis
Cc: Ls Cheng; Mladen Gogala; ORACLE-L
Subject: Re: To estimate maximum active sessions on my oracle database is reasonable to the approach?  

Yes, Jonathan! I searched IMHO on Google which is "in my humble option", initially I thought IMHO is In-Memory and xxxxxx.  

It's the same as ASAP (as soon as possible) - the abbreviation English words, I am very happy to learn something new from you.  

Best Regards

Quanwen Zhao  

Quanwen Zhao <quanwenzhao_at_gmail.com> 于2021年11月7日周日 下午6:28写道：

Thanks for your reply, Jonathan 😊.  

Yes, AAS can be equivalent to sessions from v$parameter. At this moment I think the value of AAS is probably equal to CPU_COUNT from v$parameter because sessions are the affordable value for oracle database currently. In your case I guess that AAS is 8 (CPUs) and background processes are 3 according to your description because your query count(*) is 11.  

In other words I should go to estimate maximum concurrent connections (processes from v$parameter), here I assume that my oracle database server is DEDICATED server thus one session represents one foreground process. Concurrent conections should include ACTIVE and INACTIVE sessions by session status (or FOREGROUND and BACKGROUND processes by session type). Active sessions should only contain those who are running ON CPU by session_tate from v$active_session_history. Hence it makes no sense that we are discussing active sessions, am I right to the prior understanding?  

Another kind of thought if I am able to estimate one CPU is allowed to run the maximum processes then I'll know all cores of CPUs' maximum processes (maximum concurrent connections on my oracle database server).  

Best Regards

Quanwen Zhao  

Jonathan Lewis <jlewisoracle_at_gmail.com> 于2021年11月6日周六 下午5:48写道：  

AAS can be as large as the sessions parameter (minus some value for the background processes). Here's a query and result from an instance running on a machine with 8 CPUs.  

SQL> select sample_time, count(*) from V$active_session_history where sample_time > sysdate - 10/(24*60*60) group by sample_time order by 1;

SAMPLE_TIME                        COUNT(*)


-------------------------------- ----------


06-NOV-21 09.42.23.785 AM                11
06-NOV-21 09.42.24.809 AM                11
06-NOV-21 09.42.25.814 AM                11
06-NOV-21 09.42.26.817 AM                11
06-NOV-21 09.42.27.819 AM                11
06-NOV-21 09.42.28.827 AM                11
06-NOV-21 09.42.29.830 AM                11
06-NOV-21 09.42.30.847 AM                11
06-NOV-21 09.42.31.860 AM                11
06-NOV-21 09.42.32.869 AM                11

10 rows selected.    

A session doesn't have to be "doing" anything to be active, it only has to be in the middle of a database call. In this case all the sessions are waiting on an attempt to "lock table X in exclusive mode" when the table is already locked.  

Regards

Jonathan Lewis.    

P.S. In case no-one answered: "IMHO" means "in my humble opinion".        

On Mon, 1 Nov 2021 at 01:30, Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:

Concurrent is not matter ACTIVE?  

The logic cpus is 192 on my oracle database server, so AAS is allowed to increase to 192 because an AAS of 1 is equivalent to 100% of a CPU core. Since AAS has a maximum value I am also able to estimate the maximum concurrent connections using the current concurrent connections by the proportion with current AAS and logic cpus.  

By the way what's IMHO?  

Best Regards

Quanwen Zhao    

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 09 2021 - 14:16:12 CET