Re: Login per sec by module

Hi Mark,

I'm no connection pool expert, but I have played with at least a couple of them

CMAN: Oracle Connection Manager. Looks good on paper, but does not work as documented.
That is, some features work, but others do not. Or at least, whatever documentation exists, it is incorrect in many cases.

SQL Relay: http://sqlrelay.sourceforge.net/

This is my reason for replying here. SQL Relay works well, and it works as advertised, at least as far as connection pooling is concerned.

It has other features, such as SQL transformation, which I have not tried. In any case, I found it impressive.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill

On Mon, Oct 18, 2021 at 5:26 AM 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