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 220.127.116.11.
Thanks in advance.
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).
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.
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).
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).
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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.
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
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.