Home » SQL & PL/SQL » SQL & PL/SQL » How to find the number of connections in oracle?
How to find the number of connections in oracle? [message #276499] Thu, 25 October 2007 06:05 Go to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
I have one schema (dbatest) which contains 20 tables..
There are many users accessing the same schema at the same time..
For each user Oracle creates one connection or every users has different connections?

How can i find those info?
In which table contains all those stuffs

Thanks
Re: How to find the number of connections in oracle? [message #276503 is a reply to message #276499] Thu, 25 October 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Study v$session and come back if you have more questions.

Regards
Michel
Re: How to find the number of connections in oracle? [message #276510 is a reply to message #276503] Thu, 25 October 2007 06:41 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Thanks Michel

Almost i know the columns in v$session..

I had tested in below way..

I am logged in a user of 'SELVA' in 3 times in my same PC, I am accessing ORACLE from server machine.

Now i use the below query

SQL>select program,status from v$session where username='SELVA';

PROGRAM STATUS

sqlplus.exe ACTIVE
PLSQLDev.exe INACTIVE
sqlplusw.exe INACTIVE


How can i interpret with this above info?
How can i identify number of connections?

Thanks
Re: How to find the number of connections in oracle? [message #276513 is a reply to message #276510] Thu, 25 October 2007 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
3 sessions, 3 connections, with programs listed.
What don't you understand?

Regards
Michel
Re: How to find the number of connections in oracle? [message #276522 is a reply to message #276513] Thu, 25 October 2007 07:06 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Thanks Michel

3 connections are created?..
So it will create one connection per one session..
How can i share the connections for all the sessions or atleast 10 sessions.
One connection can handle multiple session know?
How can i handle this..?

Thanks
Re: How to find the number of connections in oracle? [message #276524 is a reply to message #276522] Thu, 25 October 2007 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use OCI to program your own client.
SQL*Plus family products don't do it (at user level).

Regards
Michel
Re: How to find the number of connections in oracle? [message #276529 is a reply to message #276524] Thu, 25 October 2007 07:17 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Thanks Michel..

Is it possible to fix this in DB level itself?
Because i dont do any change in the front-end ..
This is my environment..
Re: How to find the number of connections in oracle? [message #276536 is a reply to message #276529] Thu, 25 October 2007 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, nothing at database level, it is the way the client is programmed.

Regards
Michel
Re: How to find the number of connections in oracle? [message #276546 is a reply to message #276536] Thu, 25 October 2007 08:15 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Michel..
Thanks for your updates..
I had gone thru Tom link..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671284058977

In this Tom aggred that one connection can handle multiple sessions..

Pls confirm that..

Thanks
Re: How to find the number of connections in oracle? [message #276556 is a reply to message #276546] Thu, 25 October 2007 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, but you can't access it at user level with SQL*Plus.
That is you can't create your own several sessions at the same time with only one SQL*Plus.

Regards
Michel
Re: How to find the number of connections in oracle? [message #276564 is a reply to message #276556] Thu, 25 October 2007 08:58 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Thanks Michel

I had tested the code..The program uses JDBC Thin Client to connect the database.
In the first step i am using login page authentication.
User1 launch the browser and gives his id and password, the same can be checked from DB.
The above process is same for User 2 also.

Now i am executing the below query

select program,status from v$session where username='SELVA';

PROGRAM STATUS
----------------------------------------------------------------
JDBC Thin Client INACTIVE
JDBC Thin Client INACTIVE
sqlplus.exe ACTIVE

It shows 3 rows selected..
So 3 connections are opened..

The above is my testing environment. Instead of launching the exe i can run from the browser..

Anything possible to minimize the connections in the above way?
I am not having acess to java code..

Thanks Michel
Re: How to find the number of connections in oracle? [message #276566 is a reply to message #276564] Thu, 25 October 2007 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here, you see 3 sessions (not connections).
The fact that they are using the same connection or not depends ONLY on the way this has been programmed in your application server.
Connections are determined by server process associated to the session. The sessions with the same server process use the same connection.

Regards
Michel


Re: How to find the number of connections in oracle? [message #276569 is a reply to message #276566] Thu, 25 October 2007 09:10 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Michel

Is it uses one connection only?
So 3 rows selected means 3 sessions..

How can i query from DB like number of connections =1..

They didn't programmed in apps server for connection pooling..

Thanks
Re: How to find the number of connections in oracle? [message #276595 is a reply to message #276569] Thu, 25 October 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

How can i query from DB like number of connections =1..


Quote:

sessions with the same server process use the same connection

If you don't want to join with v$process, just check (user) sessions with same paddr in v$session.

Regards
Michel
Re: How to find the number of connections in oracle? [message #276609 is a reply to message #276595] Thu, 25 October 2007 10:49 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Thanks Michel,

select a.PADDR,a.program,a.status,a.osuser from v$session a ,v$process b where a.osuser='selva' and a.PADDR=b.ADDR;

PADDR ADDR PROGRAM STATUS OSUSER---------------------------------- -------- ------------------------------
681E6D0C 681E6D0C sqlplus.exe ACTIVE selva
681E70CC 681E70CC JDBC Thin Client INACTIVE selva
681E748C 681E748C JDBC Thin Client INACTIVE selva

From this above can u find the number of connections?
Re: How to find the number of connections in oracle? [message #276611 is a reply to message #276609] Thu, 25 October 2007 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said 3 processes, 3 connections.

Regards
Michel
Re: How to find the number of connections in oracle? [message #276615 is a reply to message #276611] Thu, 25 October 2007 11:13 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Thanks

It is 3 connections?

when i remove AND condition is above query it returns 60 rows..
PADDR
681E70CC 681E43CC JDBC Thin INACTIVE kumaran
681E748C 681E43CC JDBC Thin INACTIVE kumaran
681E784C 681E43CC JDBC Thin INACTIVE kumaran
681E6D0C 681E43CC sqlplus.exe ACTIVE kumaran

681E70CC 681E478C JDBC Thin INACTIVE kumaran
681E748C 681E478C JDBC Thin INACTIVE kumaran
681E784C 681E478C JDBC Thin INACTIVE kumaran
681E6D0C 681E478C sqlolus.exe ACTIVE kumaran

For remaining rows ADDR is different and same for 4 rows (681E4B4C,681E4F0C,681E52CC,681E568C)..

Can you fond the connections..?
Thanks
Re: How to find the number of connections in oracle? [message #276628 is a reply to message #276615] Thu, 25 October 2007 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you remove the and you no more have a join, you have a cartesian product and this is meaningless (here).

Regards
Michel
Re: How to find the number of connections in oracle? [message #276683 is a reply to message #276615] Fri, 26 October 2007 00:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't you tell us why you are so obsessed with knowing how many connections/sessions there are?
Why is it a problem, why do you need to know? It may very well be that your problem can be solved in another way then monitoring the number of connections or sessions.
Re: How to find the number of connections in oracle? [message #276731 is a reply to message #276683] Fri, 26 October 2007 02:40 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Hi Frank

I want to find out the number of connections used my program while connectiong to database.

Unfortunately i am not having control in front-end.
I need to figure out from DB itself.
Thats why i asked..

If every browser acess it will create a new connection? or new session is created with existing connection?

This is my doubt..
Can any one give how to monitor connections and session info is very usefull..

Thanks
Re: How to find the number of connections in oracle? [message #276733 is a reply to message #276731] Fri, 26 October 2007 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Can any one give how to monitor connections and session info is very usefull..

Is this a question?
If so I think the whole thread answer it.

But Frank's question was why do you need/want it?

Regards
Michel

[Updated on: Fri, 26 October 2007 03:02]

Report message to a moderator

Re: How to find the number of connections in oracle? [message #276734 is a reply to message #276733] Fri, 26 October 2007 03:08 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Michel..

I need to find out number of connections..
If multiple sessions create multiple connection means i have to tell how it happens?
If single connections share multiple session means no probs.. Otherwise i have to give supporting documents to my client.
As i previously mentioned that i am not having control to client program..
I have to monitor from the DB itself..

Thanks
Re: How to find the number of connections in oracle? [message #276737 is a reply to message #276734] Fri, 26 October 2007 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I need to find out number of connections
$
Why?

Default behaviour is 1 session, 1 connection (and vice versa).

Regards
Michel
Re: How to find the number of connections in oracle? [message #276740 is a reply to message #276737] Fri, 26 October 2007 03:47 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Ok Michel..

Can i conclude from the below o/p as

PADDR ADDR PROGRAM STATUS OSUSER---------------------------------- -------- ------------------------------
681E6D0C 681E6D0C sqlplus.exe ACTIVE selva
681E70CC 681E70CC JDBC Thin Client INACTIVE selva
681E748C 681E748C JDBC Thin Client INACTIVE selva

Each connection has seperate process..
One process is created for one connection and
Each connection has seperate session..

Is my conclusion is correct?
Pls analyse my conclusion and give the result..

Thanks
Re: How to find the number of connections in oracle? [message #276743 is a reply to message #276740] Fri, 26 October 2007 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes

Regards
Michel
Re: How to find the number of connections in oracle? [message #276745 is a reply to message #276743] Fri, 26 October 2007 04:10 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
ichel

Thanks for ur support from the begining of my thread itself.

I will inform to my client what we discussed in the last reply (Conclusion).
Pls send me any URL which supports our points.

I will contact you any queries raised by client..

Thanks
Re: How to find the number of connections in oracle? [message #276813 is a reply to message #276745] Fri, 26 October 2007 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://tahiti.oracle.com
http://asktom.oracle.com

You have the keywords, you can make the search by yourself.

Regards
Michel
Re: How to find the number of connections in oracle? [message #276814 is a reply to message #276745] Fri, 26 October 2007 06:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
orasiva wrote on Fri, 26 October 2007 11:10

I will contact you any queries raised by client..


So what you are saying is the client should really be hiring and paying Michel? ./fa/917/0/


Re: How to find the number of connections in oracle? [message #276819 is a reply to message #276814] Fri, 26 October 2007 06:49 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Hmm..
Initially i confused with connections and sessions information.

As per the Tom's article (link) i thought that on connection can allow multiple sessions..

But Michel ssaid that as per my query result one process have one connection and one session..

The above said point is contradict to Toms view..
Thats y i use the word if any queries raised by Client i will contact you..

Re: How to find the number of connections in oracle? [message #276822 is a reply to message #276819] Fri, 26 October 2007 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no contradiction between Tom's view and mine, they are exactly the same ones.
You confuse MAY and MUST.

Regards
Michel
Re: How to find the number of connections in oracle? [message #276824 is a reply to message #276822] Fri, 26 October 2007 07:01 Go to previous messageGo to next message
orasiva
Messages: 15
Registered: October 2007
Junior Member
Ok..Michel

I didnt configure anything in my DB.
So,one connection can handle multiple sessions..
But it is not happening in my Db..

I am looking on how to set multiple sessions in one connections inorder to optimze the performance..

Thanks
Re: How to find the number of connections in oracle? [message #276827 is a reply to message #276824] Fri, 26 October 2007 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
orasiva:

I didnt configure anything in my DB.
So,one connection can handle multiple sessions..
But it is not happening in my Db..

Michel Cadot:

Default behaviour is 1 session, 1 connection (and vice versa).


orasiva:

I am looking on how to set multiple sessions in one connections

Michel Cadot:

Use OCI to program your own client.

There is nothing to set in DB, IT IS A MATTER OF CLIENT PROGRAMMATION AND NOTHING ELSE.

orasiva:

inorder to optimze the performance

Why do you think this will optimize performances?

Regards
Michel

[Updated on: Fri, 26 October 2007 07:15]

Report message to a moderator

Re: How to find the number of connections in oracle? [message #276831 is a reply to message #276827] Fri, 26 October 2007 07:20 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
...which brings us back to my question:
why do you want to know?
Previous Topic: query is taking 1 hour to execute
Next Topic: column name length Alias
Goto Forum:
  


Current Time: Sat Dec 03 10:11:30 CST 2016

Total time taken to generate the page: 0.08387 seconds