Re: Login per sec by module

Hi Mark,

Thanks for your detailed email !!

Definitely Applicaiton team is using incorrect logic for connection management , this is they were working for long and now sudden changes in behvaoiur is troubling them .

_at_Emer : Its Application Module , which i was looking for .

Also , it will be great help , if we get data from historical connection management , so i am looking for query if anyone in forum can help me please .(separate mail was already sent in this forum for same where i tried to get data but query result goes to toss after some adjustment ) .

Regards,
Krishna

On Mon, 18 Oct 2021 at 17:56, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Others, particularly Ilmar, already answered your diagnostics question.
>
>
>
> JL gave you Oracle’s blanket technique to put a band-aid on poor design
> and login management choices. That’s about the best you can do without some
> actual work.
>
>
>
> IF you want to actually fix the situation to consume dramatically less
> resources and accommodate high transactions and/or query rates, building a
> pool (1 or more) of service daemons has been well understood since at least
> 1989.
>
>
>
> The service daemon stays connected, like the Oracle listener, and has
> either a single service or a set of services. It may have a sophisticated
> service queueing buffer to handle fifo and denial of service attacks.
> Security is important, but since a properly written daemon can only do
> specific things, possibly requiring security handshake, the danger is
> contained. Logging requests and results of service requests can be built as
> an editable, re-playable, ordered set of transactions (original
> implementations included “guard tables” so that noise broken transactions
> could be identified and repaired without figuring out which transactions
> succeeded so that an entire log could be re-played with no duplication.)
> Submissions to the daemon can be interactive or batch only or both as fits
> your need. Daemons can be active or inactive in accordance with your work
> shifts. You need to decide whether a request during an inactive is queued
> for later ordered execution or not, and the application callers need to
> decide whether a queued transaction is allowed (since you cannot confirm
> commit in real time). And daemons either answer with a status or they
> don’t, but you need to work out those implications yourself. Under denial
> of service attacks that are severe, you may need to have the daemon be able
> to switch to “just record requests” mode, in which case point of origin
> information may be useful to record and require as part of the service
> request.
>
>
>
> All the service daemons I am familiar with were written in FORTRAN, PL/I
> or C. I don’t know whether more “modern” languages on which operating
> systems work usefully with daemons. They worked with DTSS (long dead
> original timesharing aka cloud) and UNIX (most flavors). I suspect they
> work with the plagiarized popular reimplementation of UNIX, but I don’t
> really know.
>
>
>
> Prior to Andrew Laursen’s surgery on the login module preparing for Oracle
> 7.1 (if memory serves) an individual login request was a much longer code
> path than at least between 3 and 7.0 and computer capabilities were much
> less, so if you were doing something serious service daemons were pretty
> important. I haven’t seen new ones in a long time. I don’t even know
> whether the “listener like” daemon to which you could attach your own
> programs is even available from Oracle anymore. We never used it, but only
> because we had already built our own infrastructure.
>
>
>
> Anyway, good luck. IF you engage someone to build service daemons for you,
> specify that the code should be simple and clear rather than as short as
> possible (length of source code doesn’t really matter unless it is
> interpretive and your daemons shouldn’t be interpretive.)
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Monday, October 18, 2021 6:11 AM
> *Cc:* Oracle-L Freelists
> *Subject:* Re: Login per sec by module
>
>
>
>
>
> Is your workaround "database resident connection pools". This can reduce
> significantly the contention on logon
>
>
>
> Why does a batch need to have a large number of login/logout - or is it
> simply that the batch runs while the high rate of login/logout are going on?
>
> Does the technology you're using requirement a lot of login/logout, or is
> it a feature of the way the application is written.
>
>
>
> Ilmar Kerm's comment is most important - what, exactly, do you mean by
> module? If it's not already done, and if it's an "application module"
> would it be possible to set up several services, one per module and make
> sure that each module connects to its designated service.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
> On Mon, 18 Oct 2021 at 10:45, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> wrote:
>
> Hi All ,
>
>
>
> Thanks for your suggestion ,
>
>
>
> In our environment , listener log is disabled , since it has huge
> connection coming in , on average login per sec for per instance is ~150.
>
>
>
> Now situation is CPU is used almost 100% during batch run ,and these batch
> run has huge login and log out .
>
>
>
> we are trying to achieve two things :
>
> 1. get connection management historical data, since we feel it is due to
> huge login , system is reaching to 100 % so trend of it would help us to
> relate
>
> 2. Implementing a workaround which helped , but we need to figure out
> which modules need to add in it ,hence needed login per sec module wise
>
>
>
>
>
> Regards,
>
> Krishna
>
>
>
>
>
>
>
>
>
> On Mon, 18 Oct 2021 at 14:20, Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:
>
> If by "module" you mean "service", then:
>
> select * from gv$service_stats where stat_name='logons cumulative';
>
> I sample it regularly and then calculate logons per time unit from there.
>
>
>
> On Mon, Oct 18, 2021 at 8:07 AM Krishnaprasad Yadav <
> chrishna0007_at_gmail.com> wrote:
>
> Hi Experts ,
>
>
>
> Is there any way to find out login per sec of specific modules from the
> database .
>
>
>
> It will be great if someone share some lights on this
>
>
>
> Regards,
>
> Krishna
>
>
>
>
>
>
>
>
> --
>
> Ilmar Kerm
>
>

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