| Database Resident Connection Pooling [message #487929] |
Mon, 03 January 2011 07:50  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Can anyone explain the architecture behind this? The Admin Guide [[http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/manproc002.htm#CHDGIJIA]] says that session memory is maintained in the PGA (unlike shared server) and then gives a calculation for memory usage that I don't understand: it suggests that instead of there being one PGA per session, there is one PGA per pooled server plus a small allocation of 35k per session, but this is not what I see.
It's easy to demonstrate. As sys,
exec dbms_connection_pool.start_pool
and then launch any number of session with
sqlplus scott/tiger@127.0.0.1:1521/orcl:POOLED
and you'll see a process called Lnnn launched for each session:
select pname from v$process where pname like 'L0%';
select count(*) from v$session where username='SCOTT';
This query shows that each session does have a significant PGA:
select value from v$sesstat where
sid in (select sid from v$session where username='SCOTT') and
statistic# = (select statistic# from v$statname where name='session pga memory');
The sessions get disconnected after 5 minutes idle time.
There is a mention of a process called the connection broker, but I can't see this in v$process.
There is more detail in the OCI developers guide [[http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10646/oci09adv.htm#sthref1527]] which goes beyond my knowledge of application development.
It sounds extraordinarily efficient, but I can't understand how (or if) the state of a session is preserved, and I can't reconcile the description of PGA management with what I observe.
Any ideas?
update: removed the mail tags that were inserted
[Updated on: Mon, 03 January 2011 07:56] Report message to a moderator |
|
|
|
| Re: Database Resident Connection Pooling [message #487962 is a reply to message #487929] |
Mon, 03 January 2011 20:42  |
 |
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
First of all thanks for the thread...I have learned one more new topic.Hope the following will give you a clue.
Quote:The sessions get disconnected after 5 minutes idle time.
Using PROCEDURE CONFIGURE_POOL ,ALTER_PARAMYou can set INACTIVITY_TIMEOUT for how long a session can be in Idle mode.
Quote:you'll see a process called Lnnn launched for each session.There is a mention of a process called the connection broker, but I can't see this in v$process
Lnnn(Pooled Server Process):-In Database Resident Connection Pooling, clients connect to a connection broker process. When a connection becomes active, the connection broker hands off the connection to a compatible pooled server process. The pooled server process performs network communication directly on the client connection and processes requests until the client releases the server. After being released, the connection is returned to the broker for monitoring, leaving the server free to handle other clients.
Nnnn(Connection Broker Process):-In Database Resident Connection Pooling, clients connect to a connection broker process. When a connection becomes active, the connection broker hands off the connection to a compatible pooled server process. The pooled server process performs network communication directly on the client connection and processes requests until the client releases the server. After being released, the connection is returned to the broker for monitoring, leaving the server free to handle other clients.
Let me know If I am wrong or if it is no where related to yoour question So that it will have one more chance to read something new 
Ref:-
http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/bgprocesses.htm
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/process.htm#CIHBIFCI
Thank you
Sriram
[Updated on: Mon, 03 January 2011 20:56] Report message to a moderator |
|
|
|
| Re: Database Resident Connection Pooling [message #488164 is a reply to message #487962] |
Wed, 05 January 2011 06:51 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Thanks for replying, Sriram.
I think I have a better understanding now. For example, start a connection pool of exactly four servers, then attempt to launch five sessions:
exec dbms_connection_pool.start_pool
exec dbms_connection_pool.alter_param('','minsize','4')
exec dbms_connection_pool.alter_param('','maxsize','4')
host sqlplus scott/tiger@127.0.0.1:1521/orcl:pooled
host sqlplus scott/tiger@127.0.0.1:1521/orcl:pooled
host sqlplus scott/tiger@127.0.0.1:1521/orcl:pooled
host sqlplus scott/tiger@127.0.0.1:1521/orcl:pooled
host sqlplus scott/tiger@127.0.0.1:1521/orcl:pooled
The fifth attempt will hang, until one of the others times out (default, 5 minutes.)
So it looks as though this is a technique for limiting concurrent sessions, by queueing connection requests and disconnecting idle sessions. Not as interesting as I thought, but perhaps an interesting alternative to the Resource Manager.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
