DATABASE RESIDENT CONNECTION POOLING:
Points we will cover:
what is it?
when to use?
when should anything be pooled?
1. when something is expensive to create and tear down
2. when u don’t need the thing all the time
** memory and process space wasted
AND Dtabase connection satisfy all of the above criteria
1.Use non persistent connections??
high connection times
unnecessary connect/disconnect CPU load
2. use persistent connections??
too many connections hanging around
majority not doing any work
database connection resources:
1.Physical coonection creation
**listener spawns server process,initialization,socket handoff (it handoff socket from client to the DBA)
** capability and protocol negotiation
** consumes PGA(around 4MB)
** UGA(around 400KB)
DATABASE connection in middle tiers
1. connection to the same DB
2.login as same user/schema
3. fixed set of application
4. repeat queries
5. uniform technology(mainly java,php etc.)
6. business processing(being performed at client side) and database activity
7. SO LARGE SCOPE OF RESOURCE REUSE AND OPTIMIZATION
SO what are the OPTIONS we have
1. Appplication level connection pooling (applications can be multithreaded and each thread perform DB activity)
** OCI,JDBC/OCI, custom
** multiple threads share physical connections
** multiplex user sessions over fewer connections/servers
** stateful sessions, stateless connection **MOST IMPORTANT**
2. Application level session pooling
** multiple threads share connection+sessions, stateless
application could be single threaded and they run as N no of processes instead of threads and these application process could be running from different hosts as well
so all of these are actually going to make a connection to the database. these no of process could be very large so in this case client side connection pooling is not possible because they are not going to share the sessions among the threads and we don’t have infrastructure to make sharibility across different hosts.
application process can repeatedly connect/disconnect
application process can hold onto it’s connection
but we know the drawbacks with this approach.
SHARED SERVERS can be an option
** clients connect to dispatchers
** multiplex user sessions over few shared server process
** CAN’T REALLY SHARE SESSIONS **IMPORTANT**
DATABASE RESIDENT CONNECTION POOLING(DRCP)
“oracle database 11g feature
“pool of dedicated server+session on database machine
“pool shared across applicationprocess and middle tier nodes
“reduces memory, can improve performance
“co-exist with dedicated,shared server approach and even with RAC
“most of the drivers support it like OCI,JDBC,OCCI,opensource drivers
we have set of application server which are going to make use of DB server.From DRCP on the DB(it runs on the DB)
there is process called connection broker. and there are pool servers which are actual server process
that run the workload and if few of them are designated as AUTH server whose sole purpose is to authenticate the client connection
and then only clients to the DB. so what happens wheen DCRP is up and DB listener knows about it??
automatically DRCP registers itself with the listenerand when client asks for connection Listener redirects it to the connection broker.
since it’s a fresh connection and has not yet been authenticated, what the broker does is it hands it off to the auth server, and then authentication happens
b/w client and authentication server. once it gets successful client (socket) is now connected to broker AND THIS IS THE CONNECTION WE CAN SAY CAN BE CONNECTED PERSISTENTLY because its just a connection,it’s not occupying any kind of resources on the DB server in terms of server process or the session memory. so on this persistent connection clients makes a connection request. the broker in that case picks a free pool server which is satisfying the criteria and hands Off the
connection to one of the pool server. then the pool server is marked as BUSY.
SO HERE WHEN CLIENT PERFORM SUCH TRANSACTION IT CONNECTION TO THE SERVER PROCESS JUST LIKE AN DEDICATED SERVER PROCESS. THERE IS NO OTHER PROCESS COMING IN BETWEEN WHEN SQL TRAFFIC IS GOING ON.
and once when the application is done with DB activity it releases the connection.the connection is released back to the pool. THus connection(busy) is handed back to the broker.
the server process is freed up for reuse.
AND THE CYCLE GOES ON AND ON..ISN’T IS EASY??
ONE IMPORTANT THING HERE IS ONLY “CURRENT(ACTIVE CONNECTIONS) SERVER PROCESS MEMORY PLUS SESSION MEMORY” CONSUME MEMORY ON DB SIDE.
COMPARISON OF CRSP VS DEDICATED AND SHARED SERVER process.
Senario with multiple application servers
For example, in a middle-tier connection pool, if the minimum pool size is 100, then the connection pool has 100 connections to the server,
and the Database server has 100 server processes associated with these connections. If there are 30 middle tiers with a connection pool of minimum size 100,
then the server has 3000 (100 * 30) corresponding server processes running. Typically, on an average only 5% of the connections, and in turn, server processes
are in use at any given time. So, out of the 3,000 server processes, only 150 server processes are active at any given time. This leads to over 2,850 unused server
processes on the server. These unused processes are the wasted resources on the server.
Use the dbms_connection_pool.start_pool to start DRCP with default settings
connect / as sysdba
Use the dbms_connection_pool.stop_pool to stop DRCP
connect / as sysdba
Routing Client Connection Requests to the Connection Pool
RAC and DRCP
Database Resident Connection Pool is certified to work with Real Acppliaction Cluster. Each database instance has its own connection broker
and pool of servers. Each pool has the identical configuration. For example all pools will start with minsize server processes.
A single dbms_connection_pool command will alter the pool of each instance at the same time.
CLIENT CONNECT STRING
hostname/service:POOLED TNS MUST BE UPDATED THIS WAY
this way client is directed to DRCP.