Blog

le 15/09/2007
Thomas Bordet
Archives blog-arkzoyd @fr

Database Resident Connection Pooling (DRCP)

DRCP est la meilleure raison que j’ai trouvée jusqu’à présent pour migrer en 11g dès que possible. Bien sur, si vous utilisez une application qui établit beaucoup de connexions de courte durée, comme c’est généralement le cas avec une application web écrite en Perl ou PHP ; c’est redoutable pour ce problème !

Jusqu’en 10g, l’équation « beaucoup de connexions = beaucoup de problèmes » est souvent vrai et si vous avez mis en oeuvre Shared Server (aka MTS), vous savez de quoi on parle ici. 11g promet d’adresser nos préoccupations et les problèmes de connexions de nos applications Perl ou PHP, grâce (traduit littéralement) au pooling de connexions qui réside dans la base de données. Remarquez qu’on parle de base de données et pas d’instance ; ça doit être compatible avec RAC !

Étape 1 – Démarrer un Pool DRCP

Ça commence par une bonne nouvelle, c’est effectivement très simple à mettre en œuvre. Le package PL/SQL DBMS_CONNECTION_POOL a 5 procédures qui sont :

CONFIGURE_POOL
START_POOL
STOP_POOL
ALTER_PARAM
RESTORE_DEFAULTS

Je vous renvoie aux sections associées dans Oracle 11g Administrator’s Guide et Oracle 11g PL/SQL Packages and Types Reference et pour ce qui est de la signification et des paramètres de ces procédures. A utiliser, c’est assez simple surtout si vous vous appuyez sur le Pool par défaut qui s’appelle SYS_DEFAULT_CONNECTION_POOL. Connectez-vous SYS et démarrer ce pool par défaut

sqlplus / as sysdba
exec DBMS_CONNECTION_POOL.ALTER_PARAM ( -
'SYS_DEFAULT_CONNECTION_POOL','MINSIZE',1);
exec DBMS_CONNECTION_POOL.ALTER_PARAM ( -
'SYS_DEFAULT_CONNECTION_POOL','MAXSIZE',2);
exec DBMS_CONNECTION_POOL.ALTER_PARAM ( -
'SYS_DEFAULT_CONNECTION_POOL','INCRSIZE',1);
exec dbms_connection_pool.start_pool

Une fois le pool démarré, vous devez voir apparaître des process particulier (CMON) s’enregistrer dans le listener comme ci-dessous :

lsnrctl service
[...]
Service "ORCL" has 1 instance(s).
Instance "ORCL", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
  LOCAL SERVER
    "N000" established:0 refused:0 current:0 max:679 state:ready
       CMON 
       (ADDRESS=(PROTOCOL=tcp)(HOST=arkzoyd)(PORT=63118))

Étape 2- Se connecter via le pool

Pas beaucoup plus compliqué, ajoutez la section « (SERVER=POOLED) » dans la description de votre alias TNS. Par exemple, mon alias TNS version la base qui sert le service « orcl » est le suivant :

ORCL_POOLED=(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=localhost)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=orcl)
(SERVER=POOLED)
)
)

Vous pouvez également utiliser Network Manager (netmgr) pour configurer votre connexion, Sélectionnez « Pooled Server » comme type de connexion.

Pour vous connecter, rien ne change ce qui est plutôt une bonne nouvelle si vous voulez pouvoir en bénéficier simplement :

sqlplus scott@orcl_pooled

Remarque
Si vous tentez de vous connecter alors que le pool n’est pas démarrer, vous devez recevoir l’erreur suivante :

ORA-12520: TNS:listener could not find
 available handler for requested type of server

Étape 3 : « Démontrer » que le Pool fonctionne bien

Bien sur le test qui suit est très loin d’apporter une démonstration irréfutable mais c’est un début et sur mon portable, le résultat est un bon début; Commencez par créer un script qui lance 20 connexions les unes après les autres.

#!/bin/bash
i=$1
sqlplus /nolog <<EOF
!date
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
connect scott/tiger@$i
!date
exit;
EOF

Une fois le script écrit, lancez-le successivement avec une connexion qui utilise le Pool et une connexion dédiée. Comparez :

chmod +x tst.sh
tst.sh orcl_pooled
txt.sh orcl

Ce test simple affiche des résultats sont du simple au double (3 secondes dans le cas du DRCP contre 6 secondes sans) alors imaginez avec une vrai charge et des dizaines de connexions qui arrivent simultanément…

Étape 4 : Superviser les Pools de connexions

La requête suivante vous donne la configuration de vos pools DRCP :

set head off
select '----------------------------------------------- ' BEGIN,
   'CONNECTION_POOL : '||CONNECTION_POOL CONNECTION_POOL,
   'STATUS                 : '||STATUS STATUS,
   'MINSIZE                : '||to_char(MINSIZE) MINSIZE,
   'MAXSIZE                : '||to_char(MAXSIZE) MAXSIZE,
   'INCRSIZE               : '||to_char(INCRSIZE) INCRSIZE,
   'SESSION_CACHED_CURSORS : '||
      to_char(SESSION_CACHED_CURSORS) SESSION_CACHED_CURSORS,
   'INACTIVITY_TIMEOUT     : '||
      to_char(INACTIVITY_TIMEOUT) INACTIVITY_TIMEOUT,
   'MAX_THINK_TIME         : '||
      to_char(MAX_THINK_TIME) MAX_THINK_TIME,
   'MAX_USE_SESSION        : '||
      to_char(MAX_USE_SESSION) MAX_USE_SESSION,
   'MAX_LIFETIME_SESSION   : '||
      to_char(MAX_LIFETIME_SESSION) MAX_LIFETIME_SESSION,
   '----------------------------------------------- ' END
from DBA_CPOOL_INFO;
set head on

-----------------------------------------------
CONNECTION_POOL : SYS_DEFAULT_CONNECTION_POOL
STATUS                 : ACTIVE
MINSIZE                : 1
MAXSIZE                : 2
INCRSIZE               : 1
SESSION_CACHED_CURSORS : 20
INACTIVITY_TIMEOUT     : 300
MAX_THINK_TIME         : 30
MAX_USE_SESSION        : 500000
MAX_LIFETIME_SESSION   : 86400
-----------------------------------------------

Celle-ci donne les statistiques associées :

set head off
select '
----------------------------------------------- ' BEGIN,
   'POOL_NAME : '||POOL_NAME POOL_NAME,
   'NUM_OPEN_SERVERS    : '||
      to_char(NUM_OPEN_SERVERS) NUM_OPEN_SERVERS,
   'NUM_BUSY_SERVERS    : '||
      to_char(NUM_BUSY_SERVERS) NUM_BUSY_SERVERS,
   'NUM_AUTH_SERVERS    : '||
      to_char(NUM_AUTH_SERVERS) NUM_AUTH_SERVERS,
   'NUM_HITS            : '||to_char(NUM_HITS) NUM_HITS,
   'NUM_MISSES          : '||to_char(NUM_MISSES) NUM_MISSES,
   'NUM_WAITS           : '||to_char(NUM_WAITS) NUM_WAITS,
   'WAIT_TIME           : '||to_char(WAIT_TIME) WAIT_TIME,
   'CLIENT_REQ_TIMEOUTS : '||
      to_char(CLIENT_REQ_TIMEOUTS) CLIENT_REQ_TIMEOUTS,
   'NUM_AUTHENTICATIONS : '||
      to_char(NUM_AUTHENTICATIONS) NUM_AUTHENTICATIONS,
   'NUM_PURGED          : '||to_char(NUM_PURGED) NUM_PURGED,
   'HISTORIC_MAX        : '||to_char(HISTORIC_MAX) HISTORIC_MAX,
   '----------------------------------------------- ' END
from V$CPOOL_STATS;
set head on

---------------------------------------
POOL_NAME : SYS_DEFAULT_CONNECTION_POOL
NUM_OPEN_SERVERS    : 1
NUM_BUSY_SERVERS    : 0
NUM_AUTH_SERVERS    : 1
NUM_HITS            : 0
NUM_MISSES          : 2
NUM_WAITS           : 1
WAIT_TIME           : 0
CLIENT_REQ_TIMEOUTS : 0
NUM_AUTHENTICATIONS : 3
NUM_PURGED          : 0
HISTORIC_MAX        : 1
---------------------------------------

La requête qui suit décompose l’utilisation du Pool de connections par classe d’utilisation :

set head off
select '----------------------------------------------- ' BEGIN,
    'CCLASS_NAME : '||CCLASS_NAME CCLASS_NAME,
    'NUM_REQUESTS        : '||to_char(NUM_REQUESTS) NUM_REQUESTS,
    'NUM_HITS            : '||to_char(NUM_HITS)     NUM_HITS,
    'NUM_MISSES          : '||to_char(NUM_MISSES)   NUM_MISSES,
    'NUM_WAITS           : '||to_char(NUM_WAITS)    NUM_WAITS,
    'WAIT_TIME           : '||to_char(WAIT_TIME)    WAIT_TIME,
    'CLIENT_REQ_TIMEOUTS : '||
      to_char(CLIENT_REQ_TIMEOUTS) CLIENT_REQ_TIMEOUTS,
    'NUM_AUTHENTICATIONS : '||
      to_char(NUM_AUTHENTICATIONS) NUM_AUTHENTICATIONS,
    '----------------------------------------------- ' END
from V$CPOOL_CC_STATS;
set head on

-----------------------------------------------
CCLASS_NAME : SCOTT.SHARED
NUM_REQUESTS        : 2
NUM_HITS            : 0
NUM_MISSES          : 2
NUM_WAITS           : 1
WAIT_TIME           : 0
CLIENT_REQ_TIMEOUTS : 0
NUM_AUTHENTICATIONS : 3
-----------------------------------------------

Étape 5 : Aller plus loin avec un second test

Un test supplémentaire permet de mettre en évidence de manière différente le fonctionnement de DRCP. Ouvrez deux sessions

>> Session 1
sqlplus scott/tiger@orcl_pooled
select * from dual

>> Session 2
sqlplus / as sysdba
select s.sid,
     s.serial#,
     s.audsid,
     s.server,
     p.spid,
     p.pid
from v$session s, v$process p
where s.username='SCOTT'
 and s.spid=s.process;

SID SERIAL# AUDSID SERVER    SPID PID
--- ------- ------ --------- ---- ---
133     259 190128 DEDICATED 2868  27

!ps -ef |grep 2868

oracle 2868 1 0 22:42 ? 00:00:00 ora_l001_ORCL

Vous constaterez donc que la session est marquée « DEDICATED » mais que le process n’est pas un process serveur classique, c’est un process lnnn. Si vous déconnectez la session SCOTT, le process reste présent (le minimum est 1 dans mon cas). Si vous reconnectez une nouvelle session au DRCP, voici un exemple de comment V$SESSION évolue :

select s.sid,
     s.serial#,
     s.audsid,
     s.server,
     p.spid,
     p.pid
from v$session s, v$process p
where s.username='SCOTT'
 and s.spid=s.process;

SID SERIAL# AUDSID SERVER    SPID PID
--- ------- ------ --------- ---- ---
133     263 190130 DEDICATED 2868  27

Étape 6 : Allez beaucoup plus loin

L’étape d’après, c’est de trouver les « events » associées à cette nouvelle fonctionnalité et de décortiquer les internes mais (1) ce n’est pas pour tout de suite et (2) ce n’est même pas pour moi mais pour les experts. Vous avez donc du travail messieurs et mesdames ! N’hésitez pas à commenter ce post avec vos retours d’expérience !

Nota Bene :
Si vos connexions restent « Idle » trop longtemps, elles seront déconnectée et avec SQL*Plus, il est très probable que vous expérimentiez des ORA-3113. Regardez le paramètre « inactivity_timeout » de votre pool.

Thomas Bordet

Voir tous ses articles

Blog

Database Resident Connection Pooling (DRCP)

Articles récents