Oracle Database Connection pooling Strategies : Database Resident Connection Pooling (DRCP)

Connection pooling is very  less known  to dba community and hence it becomes tough to  troubleshoot connection  issues 

Connection pooling is generally the practice of a middle tier (application server) getting N connections to a database (say 20 connections).

These connections are stored in a pool in the middle tier, an "array" if you will. Each connection is set to "not in use"

When a user submits a web page to the application server, it runs a piece of your code, your code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to you.

We have many connection  poling strategies  for oracle database . 

1)  Database side Resident Connection Pooling ( database side ) 

2)  Jdbc connection pooling using Hikari pool and other 3rd party application 

3)  OCI Session Pooling : Active session Pooling   and Persistent Pooling

In this article we will  talk about DRCP . 

######################

Jdbc COnnection Pooling 

######################

Will document  on Jdbc / Hirakapool  pooling   in another  Blog  however we can get some insight in  below doc 

https://docs.oracle.com/cd/B28359_01/java.111/e10788/optimize.htm#CFHDDFCI

######################

Oci connection pool  details can be fetched from below link :

######################

The OCI8 extension provides three different functions for connecting to Oracle. 

The standard connection function is oci_connect(). This creates a connection to an Oracle database and returns a resource used by subsequent database calls.

The oci_pconnect() function uses a persistent cache of connections that can be re-used across different script requests.

 This means that the connection overhead will typically only occur once per PHP process (or Apache child).

The function oci_new_connect() always creates a new connection to the Oracle server, regardless of what other connections might already exist.

High traffic web applications should avoid using oci_new_connect(), especially in the busiest sections of the application.

References :

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnoci/session-and-connection-pooling.html#GUID-DFA21225-E83C-4177-A79A-B8BA29DC662C

https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#LNOCI091

######################

DRCP 

######################

DRCP is introduced in 11g version of Oracle. It is used for sharing connection to achieve scalability in multi process and multi threaded environment.

Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios in which an application acquires a database connection, works on it for a relatively short duration, and then releases it.

Database resident connection pooling pools “dedicated” servers process.

A pooled server is the equivalent of a server foreground process and a database session combined.

Having a pool of readily available servers has the additional benefit of reducing the cost of creating and closing client connections.

This feature is useful for applications that must maintain persistent connections to the database and optimize server resources (such as memory).

DRCP Pools are like dedicated it’s used to share the connection between multiple application process from different hosts.

Note: Database Resident Connection Pooling is consuming less memory as compared to other dedicated or Shared connection.

POOLED SERVER

Database server processes and sessions combination is known as a pooled server.

CONNECTION BROKER

A connection broker manages the pooled server in database instance. Client are connected and authenticated to the broker. The background process called is Connection Broker process(CMON).

Clients obtaining connections out of the database resident connection pool are persistently connected to a background process, the connection broker, instead of the dedicated servers. The connection broker implements the pool functionality and performs the multiplexing of inbound connections from the clients to a pool of dedicated servers with sessions.

When a client must perform database work, the connection broker picks up a dedicated server process from the pool and assigns it to the client. Subsequently, the client is directly connected to the dedicated server until the request is served. After the server finishes processing the client request, the server goes back into the pool and the connection from the client is restored to the connection broker.

Steps follow by Client for Connection

1. Client request for connection

2. Broker authenticated and pick the pooled server and hand-off client to that pooled server.

3. The client directly communicates with the pooled server for all its database activity.

4. The pooled server is handed back to the broker when the client releases it.

In 19.10 they have added two new initialization parameters to control number of processes to handle session authentication 

min_auth_servers       

max_auth_servers      

In 19.11 they have added DRCP_DEDICATED_OPT parameter to have the pool behave like a dedicated server (so as a one-to-one client/server) in case you are below the maximum number of pool connection. This is again a trade between performance and resources consumption (the V$AUTHPOOL_STATS view is NOT existing, probably a bug):

DRCP_DEDICATED_OPT

Configure the DRCP

1. Start or Enabled the Pool by connecting with sysdba

Following command start the broker and register with database listener. It must be started before client started request.

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS     MAXSIZE

------------------------------ ---------- ----------

SYS_DEFAULT_CONNECTION_POOL    INACTIVE   20

SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS   MAXSIZE

------------------------------ -------- -------

SYS_DEFAULT_CONNECTION_POOL    ACTIVE   20

2. Disable or Stop the DRCP

execute dbms_connection_pool.stop_pool();

3. For using the Pool you need to make some connection changes at TNS entry level or application level

Add POOLED keyword in TNS entry and application connection string

-- For JDBC connection modified :

host1.oracle.com:1521/orcl:POOLED

OR

-- For TNS connection

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=host1.oracle.com)

(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)

(SERVER=POOLED)))

4. Configure and ALTER DRCP with package DBMS_CONNECTION_POOL.

This is step needed if you want to change the default setting before configuring the DRCP pool.

Following example already set with default value:

execute dbms_connection_pool.configure_pool(

pool_name => 'SYS_DEFAULT_CONNECTION_POOL',

minsize => 4,

maxsize => 40,

incrsize => 2,

session_cached_cursors => 20,

inactivity_timeout => 300,

max_think_time => 600,

max_use_session => 500000,

max_lifetime_session => 86400);

execute dbms_connection_pool.alter_param(

pool_name => 'DRPC_POOL',

param_name => 'MAX_THINK_TIME',

param_value => '1200');

Parameter meaning as:

POOL_NAME: Name of the pool. Default is SYS_DEFAULT_CONNECTION_POOL

minsize: minimum number of pooled server in pool. (default 4)

maxsize: maximum number of pooled server in pool. (default 40)

incrsize: increased number of pooled server is not available if pool is not max limit( default 2)

session_cached_cursors: SESSION_CACHED_CURSORS for all connections in the pool(default 20)

inactivity_timeout: time to remain an idle server in the pool. If a server remains idle upto time limit, it is killed. (default 300 seconds)

max_think_time: Maximum time of inactivity the PHP script is allowed after connecting. (default 120 seconds)

max_use_session: Maximum number of times a server can be taken and released to the pool before it is flagged for restarting. (default 500000)

max_lifetime_session: Time to live for a pooled server before it is restarted. (default 86400 seconds)

num_cbrok: The number of connection brokers that are created to handle connection (default 1)

maxconn_cbrok: The maximum number of connections that each connection broker can handle.(default 40000)

6. Monitor the DRCP pooling from following views:

select connection_pool, status, maxsize from dba_cpool_info;

select num_requests, num_hits, num_misses, num_waits from v$cpool_stats;

select cclass_name, num_requests, num_hits, num_misses from v$cpool_cc_stats;

At Linux level it will create background processes, called connection broker to handle client request and free server processes:

ps -ef | grep ora_l00 | grep -v grep

oracle   10316     1  0 15:24 ?        00:00:00 ora_l000_orcl

oracle   13522     1  0 15:24 ?        00:00:00 ora_l001_orcl

Views : 

DBA_CPOOL_INFO

V$CPOOL_CC_INFO

V$CPOOL_CC_STATS

V$CPOOL_STATS

 set lines 200

 col username for a15

 col cclass_name for a15

 select username, cclass_name, process_id, program, machine from v$cpool_conn_info;

Reference : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/database-resident-connection-pooling.html#GUID-D4F9DBD7-7DC6-4233-B831-933809173E39

https://docs.oracle.com/javase/8/docs/technotes/guides/net/http-keepalive.html

Oracle Exadata Cell offloading/ smart scan Not working for sqlid

 

Recently  user reported  that cell offloading was not working for  certain  sql id  . To explore further  on this  planned to  write handy Blog  with some handily information 

Below are parameters controlling offloading : 

cell_offload_compaction: Cell packet compaction strategy.

cell_offload_decryption: Enable SQL processing offload of encrypted data to cells.

cell_offload_parameters: Additional cell offload parameters.

cell_offload_plan_display: Cell offload explain plan display.

cell_offload_processing: Enable SQL processing offload to cells. 

What operations benefit from Smart Scan

Full scan of a heap table.

Fast full scan of a B-Tree or bitmap index.

What operations do not benefit from Smart Scan

Scans of IOTs or clustered tables.

Index range scans.

Access to a compressed index.

Access to a reverse key index.

Secure Enterprise Search.

CREATE INDEX with NOSORT

LONG or LOB column

Cache clause queries

Query more than 255 column

Clustered table

Below checks  were  done to check if   cell offloading is working and to force offloading

--> Check if   cell offloading enabled on database 

show parameter cell 

--> Check if   sqlid is  eligible for  Cell offloading 

select  sql_id , sum(IO_OFFLOAD_ELIG_BYTES_TOTAL) eligible from dba_hist_sqlstat   where  sqlid='99jjh86790j'   group by sql_id ;

--> Check if  offloading is working fine at database level  for other sql 

select stat_name ,  sum(value)  from  dba_hist_sysstat  where stat_name like 'cell physical IO%'  group by stat_name ; 

select stat_name ,  sum(value)  from  dba_hist_sysstat  where stat_name  in ('cell physical IO bytes saved by storage index',

'cell physical IO interconnect bytes returned by smart scan')  group by   STAT_NAME ; 

--> Check if Cell offloading working for sql id 

column  sql_test format a10 

select sql_id , IO_CELL_OFFLOAD_ELIGIBLE_BYTES , IO_INTERCONNECT_BYTES, 100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)/IO_CELL_OFFLOAD_ELIGIBLE_BYTES "IO_SAVED_%" , SQL_text  from v$sql where  sql_id='cbhjyp1iio'; 

try generating sql monitoring report 

set pagesize 999

set lines 190

col sql_text format a40 trunc

col child format 99999 heading CHILD

col execs format 9,999,999

col avg_etime format 99,999.99

col avg_cpu  format 9,999,999.99

col avg_lio format 999,999,999

col avg_pio format 999,999,999

col "IO_SAVED_%" format 999.99

col avg_px format 999

col offload for a7

select sql_id, child_number child,

decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,

decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,

100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)

/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_                                      SAVED_%",

(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,

--decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,buffer_gets/decode(nvl(executions,0),0                                      ,1,executions),null) avg_lio,

--decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,disk_reads/decode(nvl(executions,0),0,                                      1,executions),null) avg_pio,

sql_text

from v$sql s

where upper(sql_text) like upper(nvl('&sql_text',sql_text))

and sql_text not like 'BEGIN :sql_text := %'

and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'

and sql_id like nvl('&sql_id',sql_id)

order by 1, 2, 3

/

-->  Ensure Database should not be in DST upgrade state. 

Reference  Exadata: Database Performance Degrades when Database is in Timezone Upgrade Mode (Doc ID 1583297.1)

select name, value$ from sys.props$ where name like '%DST%';

Ways to  force cell offloading 

using parallel hint 

"_serial_direct_read"=TRUE 

 /*+ OPT_PARAM('cell_offload_processing' 'true') */

alter session set "_simple_view_merging"=TRUE;

alter session set "_with_subquery"=materialize;

Making index invisible 

Use full hint . Eg   /*+ FULL(emp) */