Home » RDBMS Server » Server Administration » How can I count the connections exist on DB? (Ora11gR2, Linux)
How can I count the connections exist on DB? [message #514735] Wed, 06 July 2011 03:09 Go to next message
ntt85
Messages: 34
Registered: July 2011
Location: vietnam
Member

Hi everybody,

I am a beginer DBA, and now I wanna know the way to count connections exist on Databse, and the max connections db accept.
Many thanks, Confused
Re: How can I count the connections exist on DB? [message #514737 is a reply to message #514735] Wed, 06 July 2011 03:25 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
execute query on v$session
Re: How can I count the connections exist on DB? [message #514739 is a reply to message #514737] Wed, 06 July 2011 03:30 Go to previous messageGo to next message
ntt85
Messages: 34
Registered: July 2011
Location: vietnam
Member

Thanks for reply me. And the number of max connections is sessions parameter in spfile?
Re: How can I count the connections exist on DB? [message #514740 is a reply to message #514739] Wed, 06 July 2011 03:33 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
sessions, yes, but also processes. Read up on both: they work together.
Re: How can I count the connections exist on DB? [message #514743 is a reply to message #514740] Wed, 06 July 2011 03:40 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes they work together and there is a difference in their relation in 10g and 11g.Smile
Quote:

SESSIONS specifies the maximum number of sessions that can be created in the
system

(1.5*processes)+22 in 11g
(1.1*processes)+5 in 10g.


http://download.oracle.com/docs/cd/E11882_01/server.112/e17110.pdf

sriram

Edit:typo

[Updated on: Wed, 06 July 2011 03:47]

Report message to a moderator

Re: How can I count the connections exist on DB? [message #514744 is a reply to message #514743] Wed, 06 July 2011 03:42 Go to previous messageGo to next message
ntt85
Messages: 34
Registered: July 2011
Location: vietnam
Member

Thank you very much.
Re: How can I count the connections exist on DB? [message #514750 is a reply to message #514744] Wed, 06 July 2011 04:37 Go to previous messageGo to next message
ntt85
Messages: 34
Registered: July 2011
Location: vietnam
Member

I have a problem when I set sessions parameter on Oracle 11.2.0.1.0: when I set processes=1000, the session auto set to 1536, I can't change it.
And I have another question: how can I know the queries a user executing on DB?
Re: How can I count the connections exist on DB? [message #514751 is a reply to message #514750] Wed, 06 July 2011 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join v$sql and v$session on sql_id.

Regards
Michel

[Updated on: Wed, 06 July 2011 04:42]

Report message to a moderator

Re: How can I count the connections exist on DB? [message #514752 is a reply to message #514751] Wed, 06 July 2011 04:58 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Interesting!
orcl> select name,value from v$parameter where name in ('processes','sessions');

NAME                 VALUE
-------------------- --------------------
processes            150
sessions             248

orcl> alter system set processes=1000 scope=spfile;

System altered.

orcl> alter system set sessions=1100 scope=spfile;

System altered.

orcl> startup force
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1384664 bytes
Variable Size             402657064 bytes
Database Buffers          113246208 bytes
Redo Buffers                5820416 bytes
Database mounted.
Database opened.
orcl> select name,value from v$parameter where name in ('processes','sessions');

NAME                 VALUE
-------------------- --------------------
processes            1000
sessions             1522

orcl> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

orcl>

looks as though you can't have sessions on less than default.
Re: How can I count the connections exist on DB? [message #514753 is a reply to message #514752] Wed, 06 July 2011 05:18 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
It's in the docs!Quote:
Oracle uses the default value of this parameter as its minimum. Values between 1 and
the default do not trigger errors, but Oracle ignores them and uses the default instead
Re: How can I count the connections exist on DB? [message #514852 is a reply to message #514753] Wed, 06 July 2011 22:32 Go to previous messageGo to next message
ntt85
Messages: 34
Registered: July 2011
Location: vietnam
Member

Thanks all.
I have another problem: how can I determine amount processes enough, have any formulas or standard? And I have an application using Oracle connection pool, but at the starting time, the connection speed is too low, so app server consider it's timeout status, how can I improve connection speed.
Re: How can I count the connections exist on DB? [message #514853 is a reply to message #514852] Wed, 06 July 2011 22:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have another problem: how can I determine amount processes enough, have any formulas or standard?
No formula. Use empirical evidence. post results from following OS command (uptime) while normal processing is ocurring.

>And I have an application using Oracle connection pool,
>but at the starting time, the connection speed is too low,
>so app server consider it's timeout status,
Consider starting the same number of session, but spread over a longer period of elapsed time.
>how can I improve connection speed.
use SQL_TRACE during application startup period to see where time is being spent.
review STATSPACK/AWR report convering application startup time span
Re: How can I count the connections exist on DB? [message #514855 is a reply to message #514853] Wed, 06 July 2011 23:23 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
First of all read the Oracle Documents.
Quote:
I am a beginer DBA,
Consult with your senior DBA (If any).
come back here with the Problems.

Sriram

Re: How can I count the connections exist on DB? [message #514863 is a reply to message #514852] Thu, 07 July 2011 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All parameters are described in SQL Reference, for instance for 10g PROCESSES:

Quote:
PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.


So add all these parameters and the number of your concurrent sessions you estimate.

Regards
Michel
Re: How can I count the connections exist on DB? [message #514934 is a reply to message #514863] Thu, 07 July 2011 05:51 Go to previous message
ntt85
Messages: 34
Registered: July 2011
Location: vietnam
Member

Thanksssssssssss
Previous Topic: how to set mgmt_p1-mgmt_p8 using create_plan_directive
Next Topic: Is there any way to organize or group schemas inside an instance?(2 Merged)
Goto Forum:
  


Current Time: Wed May 01 22:29:08 CDT 2024