max connections to the database
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: max connections to the database

  1. #1
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT sessions_highwatter FROM v$license;

    Returns the highest number of concuranet sessions since the instance was started.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2001
    Posts
    314
    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

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  5. #5
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    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.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    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.

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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
    -------------------------------------------

  9. #9
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    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

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width