How to find the number of connections in oracle? [message #276499] |
Thu, 25 October 2007 06:05  |
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 #276510 is a reply to message #276503] |
Thu, 25 October 2007 06:41   |
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 #276564 is a reply to message #276556] |
Thu, 25 October 2007 08:58   |
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 #276609 is a reply to message #276595] |
Thu, 25 October 2007 10:49   |
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 #276615 is a reply to message #276611] |
Thu, 25 October 2007 11:13   |
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 #276683 is a reply to message #276615] |
Fri, 26 October 2007 00:01   |
Frank
Messages: 7901 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   |
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 #276734 is a reply to message #276733] |
Fri, 26 October 2007 03:08   |
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 #276740 is a reply to message #276737] |
Fri, 26 October 2007 03:47   |
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 #276819 is a reply to message #276814] |
Fri, 26 October 2007 06:49   |
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 #276827 is a reply to message #276824] |
Fri, 26 October 2007 07:13   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|