Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 9F12F100307704 for ; Sun, 31 Oct 2021 22:52:44 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 8647041492; Sun, 31 Oct 2021 21:52:43 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 767373F7B5; Sun, 31 Oct 2021 21:52:43 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1635717163; bh=ORbLo5lrpldbMLNnZaIutpqybWA6HwOHPOLL6DakHuM=; h=From:Sender:Sender:From; b=CaGMLwg8WLvVItqeotIR2qif7pECFcmx/iAHdzPXN3kR89Y7cGEBJygjbXmggcGxu ujgE2zJ0Y9DVlefPjdRCVJDhdORyG3bsNC9M1LItPMfIdjqVEVd+88TWfRTUFXHZyK O7xB3nr3OAN9jDepOuPmFVSeaKJOH5fMLulqEvWw= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HsT5kTsukGmN; Sun, 31 Oct 2021 21:52:43 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 101493F7B6; Sun, 31 Oct 2021 21:52:40 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1635717161; bh=ORbLo5lrpldbMLNnZaIutpqybWA6HwOHPOLL6DakHuM=; h=From:Sender:Sender:From; b=f1SbQ/Nqr7Vyf3jJuWw5ZpX1/4x4EjpuDaQM6ot1FRXK1dSDPgdjw4di0dx+ma5Yo tAbdparAVvBGoj8J1oM/yj+8OPV1fRiWsSbttg/zcj7891qMajtflk77L+VkpLCWVT p69j35SOKx/DCBiFUXQNqGkgA7dAhjQnud8Xy1HQ= Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 31 Oct 2021 21:52:38 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 4F4B73F7B4 for ; Sun, 31 Oct 2021 21:52:38 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=IC4qJBU/; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 4vMUsQECKORm for ; Sun, 31 Oct 2021 21:52:38 +0000 (UTC) Received: from mail-ua1-f44.google.com (mail-ua1-f44.google.com [209.85.222.44]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 3C50F3F50A for ; Sun, 31 Oct 2021 21:52:38 +0000 (UTC) Received: by mail-ua1-f44.google.com with SMTP id u9so28528129uac.8 for ; Sun, 31 Oct 2021 14:52:38 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=1mjTZEWXpgm4id5Sk6U6HwSCKgBNmaE0IJZNt45bLvc=; b=NgG8fGIvc8xK3SEJSQ4A8UkBd/H0kculEJKKkZr/n6AXyF8HnuHg4QDWdc2As5Ptrp fHKPd81kFenKL2Tkk1Koh/HB0Frct27TiMqVnDix1dUO5LQ+MTETEg9VOCRcMMBGJrKC 3KVFBpQRMxTm3bQKz1urq1L0whJZK3Q1jpxGUKQQzIVacGdgVBoULfNttGSdz5lu2QL9 wIRC3YKn1ZE7zF41xCAkM3sA2jPareYNsIBdCDjDLOk9MbCBPynfBKtqCcPw2yyqbplr 95jKPEqEc7EuMUBYFW97WhFgLppnV//4wdXPFXFVKAgYJ5nQGymVtKTcMHnP49DK8iUw EmMQ== X-Gm-Message-State: AOAM532w/zVloG+xUxDVbqQBxaRkkd9NCteW0hseooEWNrzEXWEZfAq3 D1586oUvg6YAaFCTBsh6n+7c4lCKeSQY7imWfZcmQaL+b7g= X-Google-Smtp-Source: ABdhPJz6sOstrdmfU22w2HwUBBuM/nwNz5WRIR2utCFeRnnv1pex3GB+6Lb32Z0w6xc7ADXzTXAupeJqyA6LRACp0No= X-Received: by 2002:ab0:7095:: with SMTP id m21mr7811875ual.82.1635717157810; Sun, 31 Oct 2021 14:52:37 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ls Cheng Date: Sun, 31 Oct 2021 22:52:26 +0100 Message-ID: Subject: Re: To estimate maximum active sessions on my oracle database is reasonable to the approach? To: quanwenzhao@gmail.com Cc: Mladen Gogala , ORACLE-L Content-Type: multipart/alternative; boundary="00000000000024f55905cfad14a6" X-archive-position: 81288 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: exriscer@gmail.com Precedence: normal Reply-To: exriscer@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --00000000000024f55905cfad14a6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi You want to estimate AAS or number of user sessions? Because on one hand you say "current concurrent connections", that is the number of user sessions no matter active or inactive but on the other hand you are using "v$session where status =3D 'ACTIVE'". Maximum concurrent connections is more limited by the server memory than CPU IMHO. AAS is more limited by the number of CPUs IMHO. Thanks On Sun, Oct 31, 2021 at 1:34 PM Quanwen Zhao wrote: > Yes, actually benchmark is absolutely able to get the maximum concurrent > connections my expected. You know, oracle production system has been onli= ne > and it's impossible to do it. Here I have to estimate it using my approac= h > previously mentioned. > > The max AAS value is *2.98* from ASH in last 1 hour and logic cpus is > *192*, and current concurrent connections is *60*, eventually I get the > maximum concurrent connections that is *3866* according to my formula. By > the way the attachment is the SQL script I use. > > Best Regards > Quanwen Zhao > > Mladen Gogala =E4=BA=8E2021=E5=B9=B410=E6=9C=88= 31=E6=97=A5=E5=91=A8=E6=97=A5 =E4=B8=8B=E5=8D=882:55=E5=86=99=E9=81=93=EF= =BC=9A > >> The number of affordable sessions primarily depends on what the sessions >> are doing and the amount of available resources. To rephrase it, it depe= nds >> on the meaning of the word "affordable". It also depends on the meaning = of >> the word "is", as one of the former US presidents is known to have said.= In >> addition to that there is a dependency on the database configuration. >> Pooled connections and shared servers can significantly increase the num= ber >> of concurrent sessions. In other words, reasonable answer can only be >> provided by some type of benchmark. >> >> On the other hand maximum usage since the instance startup means "the >> maximum concurrent sessions", since the instance was started up. For me, >> the phrase "since the last startup" usually means "in the last 3 months"= , >> since we install DBRU 2 weeks after they are published. That IS the numb= er >> you are looking for: the maximum simultaneous sessions that have been >> logged into the database, since the last startup. >> >> As for that monstrosity of the script, I would advise using sar, vmstat >> and iostat to measure CPU consumption, memory consumption and disk >> consumption. Your "affordable sessions" will only be affordable as long = as >> you don't start swapping and burning 80% of CPU. I tend to believe the O= S >> tools more than the values from the Oracle performance tables. I remembe= r >> more than one case of inconsistent values in the performance tables. >> >> >> On 10/31/21 02:21, Quanwen Zhao wrote: >> >> Hello MGogala =F0=9F=98=89, >> >> 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/ac= quire_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 i= s >> 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 =3D 'ACTIVE';* includes the BACKGROUND and >> FOREGROUND processes. Please waiting for a while ... >> >> Best Regards >> Quanwen Zhao >> >> Mladen Gogala =E4=BA=8E2021=E5=B9=B410=E6=9C= =8831=E6=97=A5=E5=91=A8=E6=97=A5 =E4=B8=8B=E5=8D=8812:35=E5=86=99=E9=81=93= =EF=BC=9A >> >>> >>> On 10/30/21 23:35, Quanwen Zhao wrote: >>> >>> Hey my folks and colleagues =F0=9F=98=89, >>> >>> 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 =3D 'ACTIVE';* >>> >>> Thus I am able to estimate the maximum concurrent connections using the >>> following formula: aas value / logic cpus =3D current concurrent >>> connections / maximum concurrent connections. That is to say, *3.66 / >>> 192 =3D 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 t= hat >>> they should only be hunted with your bare hands. The same applies to th= eir >>> black cousins. >>> >>> -- >>> Mladen Gogala >>> Database Consultant >>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com >>> >>> -- http://www.freelists.org/webpage/oracle-l >> >> -- >> Mladen Gogala >> Database Consultant >> Tel: (347) 321-1217https://dbwhisperer.wordpress.com >> >> --00000000000024f55905cfad14a6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

You want to estimate AAS or number o= f user sessions?

Because on one hand you say "= ;current concurrent connections", that is the number=C2=A0of user sess= ions no matter active or inactive=C2=A0but on the other hand you are using = "v$session where status =3D 'ACTIVE'".

Maximum concurrent connections is more limited by the server memory = than CPU IMHO.

AAS is more limited by the number o= f=C2=A0CPUs IMHO.

Thanks


On Su= n, Oct 31, 2021 at 1:34 PM Quanwen Zhao <quanwenzhao@gmail.com> wrote:
Yes, actually benchmark i= s absolutely able to get the maximum concurrent connections my expected. Yo= u know, oracle production system has been online and it's impossible to= do it. Here I have to estimate it using my approach previously mentioned.<= div>
The max AAS value is 2.98 from ASH in last 1 hour= and logic cpus is 192, and current concurrent connections is 60<= /b>, eventually I get the maximum concurrent connections that is 3866=C2=A0accord= ing to my formula. By the way the attachment is the SQL script I use.
=

Best Regards
Quanwen Zhao

Mladen Gogala = <gogala.mla= den@gmail.com> =E4=BA=8E2021=E5=B9=B410=E6=9C=8831=E6=97=A5=E5=91=A8= =E6=97=A5 =E4=B8=8B=E5=8D=882:55=E5=86=99=E9=81=93=EF=BC=9A
=20 =20 =20

The number of affordable sessions primarily depends on what the sessions are doing and the amount of available resources. To rephrase it, it depends on the meaning of the word "affordable&q= uot;. It also depends on the meaning of the word "is", as one of = the former US presidents is known to have said. In addition to that there is a dependency on the database configuration. Pooled connections and shared servers can significantly increase the number of concurrent sessions. In other words, reasonable answer can only be provided by some type of benchmark.

On the other hand maximum usage since the instance startup means "the maximum concurrent sessions", since the instance was s= tarted up. For me, the phrase "since the last startup" usually mea= ns "in the last 3 months", since we install DBRU 2 weeks after they are published. That IS the number you are looking for: the maximum simultaneous sessions that have been logged into the database, since the last startup.

As for that monstrosity of the script, I would advise using sar, vmstat and iostat to measure CPU consumption, memory consumption and disk consumption. Your "affordable sessions" will only = be affordable as long as you don't start swapping and burning 80% of CPU. I tend to believe the OS tools more than the values from the Oracle performance tables. I remember more than one case of inconsistent values in the performance tables.


On 10/31/21 02:21, Quanwen Zhao wrote:
=20
Hello MGogala=C2=A0=F0=9F=98=89,

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 -=C2=A0https://github.co= m/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_logic_cpus_unio= n_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=C2=A0select count(*) from v$sessio= n where status =3D 'ACTIVE';=C2=A0includes the BACKGR= OUND and FOREGROUND processes. Please waiting for a while ...

Best Regards
Quanwen Zhao

Mladen Gogala <gogala.mladen@gmail.com> =E4=BA=8E2021=E5=B9=B410=E6=9C=8831=E6=97=A5=E5=91=A8=E6=97=A5 = =E4=B8=8B=E5=8D=8812:35=E5=86=99=E9=81=93=EF=BC=9A


On 10/30/21 23:35, Quanwen Zhao wrote:
Hey my folks and colleagues=C2=A0=F0=9F=98=89,

For estimating the=C2=A0maximum concurrent connections=C2=A0on my oracle database server I did the some work as follows:
  • Acquiring=C2=A0Averag= e Active Sessions=C2=A0&=C2=A0Logic CPUs=C2=A0in Real Time;
  • Retrieving the=C2=A0c= urrent concurrent connections=C2=A0on view "v$= session";
Via the above two steps I've got some data, such as, aas value is=C2=A03.66=C2=A0and logic cpus are=C2= =A0192. Next I've got the current concurrent connections (= 61) by running this SQL statement:=C2=A0select count(*) fr= om v$session where status =3D 'ACTIVE';

Thus I am able to estimate the maximum concurrent connections using the following formula:=C2=A0aas value / logic cpus =3D current concurrent connections / maximum concurrent connections. That is to say,=C2=A03.66 / 192 =3D 61 / maximum concurrent connections, so which is=C2=A03200. 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/ora= cle-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.

--=20
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbw=
hisperer.wordpress.com
-- http://www.freelists.org/webpage/oracle-l 
--=20
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbw=
hisperer.wordpress.com
--00000000000024f55905cfad14a6-- -- http://www.freelists.org/webpage/oracle-l