max connections to the database

Hi Gurus,
How to find the max connections to the database so far..(Max connections reached).?.
All the time may not be =processes in init.ora file right?.
Any view to find the so far max connections..to the database.
V$session gives only currently connected ...
our server is sunOs...5.8
oracle v 8.1.6.3.
Thanks in advance.
Thanigaivasan.

SELECT sessions_highwatter FROM v$license;

Returns the highest number of concuranet sessions since the instance was started.

Quote:

---

Originally posted by jmodic
SELECT sessions_highwatter FROM v$license;

Returns the highest number of concuranet sessions since the instance was started.

---

which never matches with the figure returned by the "session" row in the V$RESOURCE_LIMIT view, although I my view they are supposed to return the same figure (taking into considerations the number of background processes).

Any ideas Jurij?

-amar

In basis max number of connection deped from:
-- type of oracle server configuration: shared mode or dedicated (or may be mixed)
-- in solaris from system kernel parameter (/etc/system file) --> SEMMNS = xxx
because # of processes = xx in init.ora directly depend from SEMMNS.

In dedicated mode of oracle server:
max connections = #SEMMNS - 10 - # semaphores, that use OS and anoter applications (usually on my boxes ~ 20-50)

In MTS :
max connections = (#SEMMNS - (10 + # OS/APP semaphores)) * 100~250
(this is # of connections per 1 MTS dispatcher and depend from OS type and version).
U can limit #of dispatchers using max_dispatchers = xx.

In mixed mode max connections = (#SEMMNS - (avg_dedicated_connections + 10 + # OS/APP semaphores)) * 100~250 + avg_dedicated_connections.

Hi Gurus,
thanks.I am very happy with the answers.But...
1.From V$resource_limit and v$license I get max connections from the instance started...
2.From SEMMNS we get the max allowed connections to the database....(Max limit to that server)
How can I find out the max no.of connections abtained since the database was created...Is there any way?.
Ours is multi threaded server having set SEMMNS 10250..
SEMMSL =256...There are 7 databases sitting in this server..
having processes defined in init.ora ranging from 50 to max 256(50,50,100,100,100,200,256).
Thanks.
Thanigaivasan.

Quote:

---

Originally posted by amar
Any ideas Jurij?

---

Nope. Except that obviously one should not rely on V$RESOURCE_LIMIT regarding sessions highwattermark. On my freshly started database, v$session shows 12 sessions: 7 for background processes, 4 for SNP processes (job queues) and one user session (the one I'm currently using for querying). However V$RESOURCE_LIMIT for SESSIONS shows CURRENT_UTILIZATION=13 and MAX_UTILIZATION=18 (!!!?? - note that there has not been any disconnected session since the startup yet, so where comes this difference from?). However V$LICENSE correctly shows SESSION_HIGHWATTER=5 (note that it excludes sessions for background processes).

You gurus may point out SEMMSL and processes set in one database is same...Yes We do get errors because of that at the time of shut down.This is one of the ways I am finding to trouble shoot in all the databases by controlling the no of processes....based on max processes obtained from the day database was created.
I do agree We have to set SEMMSL atleast (max processes+4).We increased the processes based on the request from the application people without increasing SEMMSL vaue.
Thanks and any input is greatly appriciated.
Thanigaivasan.

One my database has from 400 to 1500 connections in same time.
DB works im mixed mode:
-- all DBA use dedicated server connections
-- all "usual" users works thru application server and use MTS connections.

Oracle version : 8.1.7, OS solaris 8
SEMMSL = 4096
avg semaphores utilization around 60~70 %
-------------------------------------------
I can estimate max # of connections in this DB as 1000/0.6 ~= 1650
where 1000 -> avg # of connections, 0.6 semaphores utilization
-------------------------------------------

Well shestakov,
In your case you can have processes set up to (4096 -4) in parameter file but...What is your max dedicated connections
to that database...so far from the day database created...how we can find that...say 3 months ago there were 2000 connections,today may be only 200...one month ago might have 1200...but to day if you check the view??????(any views?)it should give 2000 with that date or no date...Any thing like that.
Please help...
Thanks.
Thanigaivasan

What do you understand under the term "connection to the database"? Do you want maximum cumulative number of sessions or maximum cumulative number of processes that hose sessions were connected to?

What is your server configuration? Multithreaded or dedicated?

Anyway, you were allready told how to get the highest number of sessions ever established during the lifetime of your instance....

[Edited by jmodic on 04-15-2002 at 05:37 PM]

I mean maximum Cumulative no of processes...
Thanks.
Thanigaivasan.

select * from v$sysstat where name like '%logon%';

from last instance start.

Hi Gurus,see I am getting this value...

SQL> select * from v$sysstat where name like '%LOGON%';

no rows selected
but........for
SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
0 0 233 376 0

SQL>
Thanks.
Thanigaivasan

Quote:

---

Originally posted by thanigai

SQL> select * from v$sysstat where name like '%LOGON%';
no rows selected

---

but : select * from v$sysstat where name like '%logon%';
STATISTIC# NAME CLASS VALUE
--- ------------------- ----- ------
0 logons cumulative 1 5127282
1 logons current 1 20

Yes Sir...I got it...
SQL> select * from v$sysstat where name like '%logon%';

STATISTIC# NAME
---------- ----------------------------------------------------------------
CLASS VALUE
---------- ----------
0 logons cumulative
1 21266

1 logons current
1 88
Thanks.I was typing LOGON instead of logon.
Thanigaivasan.

Note however that those logon values in V$SYSSTAT are totaly meaningless! They are not incremented by 1 with each logon to the database as you might have expected. Sometimes with each new connection they are incremented by 1, sometimes by 9, somertimes by XYZ, and sometimes they get incremented even without any new connection taken place on the database!

Simply don't use this values for any of your reports or statistical decisions about your system, because they simply do not report the implied by their names....

No no..It is not for the reports ...
Thanks.
Thanigaivasan.

Quote:

---

Originally posted by jmodic
Sometimes with each new connection they are incremented by 1, sometimes by 9, somertimes by XYZ, and sometimes they get incremented even without any new connection taken place on the database!

---

U can calculate how many connections will be in next time.
# on connections = old_#_of_logon + current_#_1 + #_new_commections - #of_disconnect.

where current_#_1 --> is current logons in point when listener UPDATE SERVICES

--------

TO_CHAR( STATISTIC# NAME CLASS VALUE
-------- ---------- ----------------------------- ---------- ----------
15:50:27 0 logons cumulative 1 5155442
15:50:27 1 logons current 1 20
------------------------------------------------
15:50:31 0 logons cumulative 1 5155462
15:50:31 1 logons current 1 20
------------------------------------------------
15:50:36 0 logons cumulative 1 5155462
15:50:36 1 logons current 1 20


=======================================================================
record from listener.log file:

17-APR-2002 15:50:30 * service_update * lab * 0

This is time when listener update services.

Shestakov,

Unfortunately I'm affraid you are on the wrong track. I think this incrementing thing has nothing to do with listener at all. I have stopped my listener and observed the number of "logons cumulative" in V$SYSSTAT and it keeps rising. So it is not connected to listener at all.

But I'm suspecting one other thing that might be causing this - Oracle's job queue processes. The number of cumulative logons in V$SYSSTAT increases by twice the number of JOB_QUEUE_PROCESSES every JOB_QUEUE_INTERVAL seconds even when there is no new connections to the database. When I started my database with JOB_QUEUE_PROCESSES = 0 the number of cumulative logons remains steady.

Jury.
In first thanks for information.
Then: in my database, and i saw this problem not only now, every time when
listener updated services in v$sysstat "cummlulative connections" increased too.
I didn't check JOB_QUEUE parameters and i can't say about it nothing.
May be this is "next source" for cummlulative connections.
But in this case oracle has "new" knowlege about mathimatics.

[Edited by Shestakov on 04-17-2002 at 05:22 PM]