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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx> 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
