Pool de connexion

Ces derniers temps, j'ai travaillé sur les pools de connexion ( pool client, pool serveur de type MTS ) . En particulier, j'ai repris le prototype de pool client présenté dans un message précédent et vite touché ses limites. Lorsque je l'ai utilisé en lien avec un pool serveur de type MTS, j'ai rencontré des erreurs ORA-3113 ( perte de connexion ) lorsque j'augmentais la charge du client en terme de processus concurrents. Losque je l'employais plus classiquement en utilisant des sessions de type dedicated, les erreurs ORA-3113 cessaient, mais des erreurs ORA-1000 ( trop de curseurs ouverts pour une session, vue v$open_cursor ) survenaient malgré une gestion correcte du curseur.

Suite à l'instabilité de mon prototype, j'ai décidé de le faire évoluer en suivant les exemples de la documentation PRO*C sur la gestion des applications utilisant des threads ( chapitre 11 ).  J'ai donc remplacé les processus par des threads; le segment de mémoire partagé est devenu inutile, le contexte de session étant passé en paramètre de la fonction pthread_create. Par rapport au prototype initial, il manque l'acquisition d'une connexion dans le pool avant l'exécution de la requête . Pour ce faire, on peut définir le pool de sessions en tant que variable globale afin qu'il soit partagé par tous les threads et le protéger via des mutex, l'équivalent des sémaphores pour les threads.

Une fois ces modifications effectuées, j'ai pu monter en charge avec un pool serveur de type MTS ou sans pool serveur sans déclencher les erreurs ORA du précédent programme.

Le source et les objets associés ( makefile, sqlnet.ora, tnsnames.ora, variables d'environnement ) sont disponibles dans ce répertoire.

Ce type de pilote permet de mettre en évidence des latences sur les différentes parties d'une architecture client/serveur:
- Client ( = zone Solaris 10 ): capping cpu insuffisant, débit de la carte éthernet à augmenter;
- Réseau ( = TCP/IP ): latence réseau excessive, effet de congestion si réseau non dédié ( snoop ou sonde => OPNET );
- Serveur ( = partition AIX + instance Oracle 10.2 ): paramétrage du MTS à revoir ( dispatcher, shared server ), SDU à optimiser.

Si vous effectuez un truss sur ce type de client ( programme PRO*C + client oracle ), vous allez observer les appels systèmes des sockets TCP ( socket, connect, read/write, close ). En s'appuyant sur les exemples du livre TCP/IP Sockets in C: Practical Guide for Programmers, on peut écrire un client/serveur de type TCP et avoir ainsi une idée de la qualité du réseau en terme de performance.

Exemple d'un client TCP incluant un pool de socket et d'un serveur TCP de type écho.

Si vous recherchez une durée d'exécution constante des requêtes de l'ordre de 10 ms ou moins, il est alors nécessaire d'envisager une autre solution que celle proposée par un SGBD classique ( client, socket TCP, instance, baie de disques ) et vous orienter par exemple vers un système mémoire ( client, cache mémoire, socket TCP, instance, baie de disques ). Dans cet ordre d'idée, Timesten peut être une solution à tester.

Real-time sql monitoring

Ce nouvel outil de la 11g permet de synthétiser un ensemble de vues du catalogue dans une animation flash. Il est directement utilisable via sql developer ( Tools -> Monitor SQL ).

Il peut être utilisé pour étudier les requêtes de plus de 5 s et surtout pour les requêtes parallèles ( le modèle producteur-consommateur est plus compréhensible au niveau du plan d'exécution et un onglet décrit l'activité de chaque serveur parallèle ). De plus, il est visualisable dès le début de l'exécution de la requête et se complète au fur et à mesure du traitement de la requête.

Les nouvelles vues liées à cet outil sont v$sql_monitor et v$sql_plan_monitor.

Illustration: un exemple de requête parallèle.

Lors d'un access full à la table t1_parallel, on note des waits events de type direct path read et non des waits events de type db file scattered read, comportement usuel pour une requête en mode parallèle. Cette méthode est également plus fréquemment utilisée en 11g pour les requêtes en mode série: http://blog.easyteam.fr/2011/02/28/effet-cache-de-la-database-11g-direct-io/.

D'autres exemples, ainsi que le test case, sont disponibles ici.

Ce dernier s'inspire des deux articles écrits par arkzoyd sur le nouveau fonctionnement de la parallélisation des requêtes en 11.2. Il permet ainsi à une requête en mode parallèle de suspendre son traitement si elle ne dispose pas des ressources suffisantes plutôt que de s'exécuter en mode série. Une fois les serveurs parallèles de nouveau disponibles, la requête est lancée en mode parallèle.

ONS ( Oracle Notify Service ) & FCF ( Fast Connection Failover )

ONS est un service du noyau Oracle permettant de délivrer des messages, plus exactement des FAN ( Fast Application Notification ). Il est alimenté par le processus racgimon, ce dernier récupérant les informations dans la queue SYS$SERVICE_METRICS.

Cette fonctionnalité est présentée dans la note ID 759895.1. Pour plus de détails, il est utile de rechercher sous google avec les mots-clés suivants: rac workload management. En particulier, vous trouverez un pdf très détaillé sur le sujet écrit par Alex Gorbachev ( Pythian Group ).

Les FAN peuvent être récupérés en java de la manière suivante:

PATH=/apps/oracle/10.2.0.4/jdk/bin:$PATH

javac -classpath /apps/oracle/10.2.0.4/opmn/lib/ons.jar onc_subscriber.java

java -classpath /apps/oracle/10.2.0.4/opmn/lib/ons.jar:. onc_subscriber

/*

* Copyright (c) 2001, 2004 by Oracle. All Rights Reserved

* ONC Subscription client. This client listens for all events ONS receives

* Based on the event type decisions are made on how and whether to print the

* event body

*/

import oracle.ons.*;

import java.util.*;

import java.io.*;

import java.nio.*;

public class onc_subscriber

{

public static void main(String args[])

{

boolean debug = false;

// Set ONC-required System property for oracle.ons.oraclehome:

System.setProperty("oracle.ons.oraclehome", "/apps/oracle/crs");

Subscriber s = new Subscriber("", ""); // subscribe to all events

Notification e;

System.out.println("ONC subscriber starting");

boolean shutdown = false;

while (!shutdown)

{

e = s.receive(true); // blocking wait for notification receive

System.out.println( "** HA event received -- Printing header:" );

e.print();

System.out.println( "** Body length = " + e.body().length);

System.out.println( "** Event type = " + e.type());

if (e.type().startsWith("database")) {

if (debug) { System.out.println( "New print out"); }

/*evtPrint myEvtPrint = new evtPrint(e);*/

} else if (e.type().startsWith("javaAPI")){

System.out.println( "javaAPI");

/*oncPrint myPrint = new oncPrint(e);*/

} else {

System.out.println("Unknown event type. Not displaying body");

}

try

{

if (e.type().equals("onc/shutdown")) {

System.out.println("Shutdown event received.");

shutdown = true;

}

else {

java.lang.Thread.currentThread().sleep(100);

System.out.println("Sleep and retry.");

}

}

catch (Exception te)

{

te.printStackTrace();

}

}

s.close();

System.out.println(" ONC subscriber exiting!");

}

}

On peut ainsi être informé du démarrage d'un service:

** HA event received -- Printing header:

Notification Type:         database/event/service

Affected Components:       null

Affected Nodes:            null

Delivery Time:             1310720203166

Generating Component:      database/rac/service

Generating Node:        node   

Generating Process:   node:725120

Notification ID:           node:725120010826

Notification Creation Time:1310720203

Cluster ID:                databaseClusterId

Cluster Name:              databaseClusterName

Instance ID:               databaseInstanceId

Instance Name:             databaseInstanceName

Local Only Flag:           FALSE

Cluster Only Flag:         FALSE

Body:                      [B@4dd0567f

** Body length = 112

** Event type = database/event/service

VERSION=1.0 service=SERVICE_TEST instance=instance database=database host=node status=up card=2 reason=user

Sleep and retry.

Ou de la répartition de charge au niveau des instances ( pré-requis: activation de LBA au niveau du service ):

* HA event received -- Printing header:

Notification Type:         database/event/servicemetrics/SERVICE_TEST

Affected Components:       null

Affected Nodes:            null

Delivery Time:             1310720807564

Generating Component:      database/rac/service

Generating Node:         node

Generating Process:     node:1089766

Notification ID:            node:1089766079356

Notification Creation Time:1310720807

Cluster ID:                databaseClusterId

Cluster Name:              databaseClusterName

Instance ID:               databaseInstanceId

Instance Name:             databaseInstanceName

Local Only Flag:           FALSE

Cluster Only Flag:         FALSE

Body:                      [B@44c617a0

** Body length = 149

** Event type = database/event/servicemetrics/SERVICE_TEST

VERSION=1.0 database=database { {instance=instance 1 percent=50 flag=UNKNOWN}{instance= instance 2 percent=50 flag=UNKNOWN} } timestamp=2011-07-15 11:06:47

Sleep and retry.

FCF est un exemple d'utilisation d'ONS pour la gestion du failover. Il peut être mis en oeuvre par un client java s'appuyant sur un driver JDBC thin. Un exemple très complet est disponible à cette adresse: http://www.idevelopment.info/data/Programming/java/jdbc/High_Availability/FastConnectionFailoverExampleThin.java.

On peut le compiler et l'exécuter de cette manière:

PATH=/apps/oracle/10.2.0.4/jdk/bin:$PATH

export CLASSPATH=.:/apps/oracle/10.2.0.4/jdbc/lib/ojdbc14.jar:/apps/oracle/10.2.0.4/opmn/lib/ons.jar

javac FastConnectionFailoverExampleThin.java

java -Doracle.ons.oraclehome=/apps/oracle/crs FastConnectionFailoverExampleThin

Sur un noeud du RAC ( 10.2.0.4 ), je n'ai pas réussi à intercepter les notifications de type servicemetrics. Je me suis alors heurté à un problème de contention au niveau des AQ ( Advanced Queues ) similaire à celui décrit dans ce message: http://oraclemva.wordpress.com/2010/02/26/content-problems-with-aq/ 

Services: load-balancing et failover

Un service est un objet de type réseau permettant de qualifier la communication entre un client et une ou plusieurs instances. En particulier, il permet de préciser les caractéristiques du load-balancing et du failover.

Jeremy Schneider a écrit un document très intéressant sur le sujet, Unleashing Oracle Services: A Comprehensive Review of "Services" in Oracle Databases.

Si on considère un RAC 10g à deux noeuds, on peut créer un service avec OEM ou en utilisant srvctl: srvctl add service -d base -s service -r instance1,instance2 -P BASIC.
Dans cet exemple, on a défini un service utilisant les deux instances et on lui a associé un failover de type TAF ( Transparent Application Failover ).

Remarque:  non public bug #6886239 "DBMS_SERVICE parameters are not added using srvctl add service. It affects 10g and 11gR1 databases, this is fixed in release 11.2 onwards. In Oracle RAC after setting up TAF although it does not fail and everything looks good, it does not configure correctly. When you check the service configuration you find no values for failover method, type and retries, those values are needed for TAF to happen."

Puis, on peut démarrer la ressource service sur les deux noeuds du RAC: srvctl start service -d base -s service.

Le statut du service se vérifie de la manière suivante: srvctl config service -d base -s service -a.

On peut aussi supprimer un service inutilisé: srvctl remove service -d base -s service -f.

Après avoir arrêté un service, on peut rencontrer le problème suivant lors du redémarrage:
PRKP-1030 : Failed to start the [service] .

CRS-1006: No more members to consider

CRS-0215: Could not start resource 'ora.[database].[service].cs'.

Dans ce cas, il faut arrêter de nouveau le service sur chaque instance en utilisant la commande dbms_services.stop_service(service, instance).

Le détail de la commande srvctl est disponible dans la doc Oracle.

Pour gérer le load-balancing et le failover de type TAF côté instance, on utilise la méthode modify_services du package dbms_services:
dbms_service.modify_service(
          service_name=>'service',
          goal=>dbms_service.goal_none,
          dtp=>FALSE,
          aq_ha_notifications=>FALSE,
          failover_method=>'BASIC',
          failover_type =>'SELECT',
          failover_retries =>10,
          failover_delay =>5,
          clb_goal=>dbms_service.clb_goal_long);
Dans cet exemple, le service n'utilise pas LBA ( Load Balancing Advisory ) et le load-balancing choisi répartit les connexions entre les deux instances en fonction de leur nombre et non pas de la charge CPU. Le failover sélectionné permet de reprendre la lecture d'un curseur dans une nouvelle session en cas de défaillance d'un noeud.

La vue dba_services permet de vérifier la configuration d'un service. La commande lsnrctl services permet de contrôler l'enregistrement du service pour un listener. La vue v$session ( ou gv$session ) permet de vérifier si le failover est bien configuré pour un client. Des vues comme gv$servicemetric ou gv$servicemetric_history délivrent des informations sur l'activité d'un service.

Le failover de type TAF est lié à la couche OCI. Il est décrit dans le white paper intitulé Transparent Application Failover.

Pour tester le load-balancing du côté serveur avec sqlplus, on peut utiliser cette chaîne de connexion:

ALIAS =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = NODE1-VIP)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = NAME)

      ))

Pour tester le failover de type TAF côté serveur avec sqlplus, on peut utiliser ces chaînes de connexion:

ALIAS =

  (DESCRIPTION =

    (LOAD_BALANCE = on)

    (ADDRESS = (PROTOCOL = TCP)(HOST = NODE1-VIP)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = NODE2-VIP)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = NAME)

       (FAILOVER_MODE = (METHOD=PRECONNECT)(BACKUP=ALIAS))

      ))

=> vérification de la prédominance du TAF côté serveur sur le TAF côté client ( vue v$session ).

ALIAS =

  (DESCRIPTION =

    (LOAD_BALANCE = on)

    (ADDRESS = (PROTOCOL = TCP)(HOST = NODE1-VIP)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = NODE2-VIP)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = NAME)

      ))

Pour simuler un failover TAF de type SELECT, on peut utiliser la commande suivante:
alter system disconnect session 'sid,serial#' post_transaction;

Wait event latch free

Avant d'exposer le problème rencontré il y a quelques jours, je vous encourage à lire ce lien qui explique ce qu'est un latch et permet de trouver l'origine de ce type de contention: http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting.

En testant le produit DB Change Manager d'Embarcadero, ce dernier permettant principalement de gérer les DDL, je me suis heurté à un fort ralentissement d'une instance Oracle 9.2. Après avoir identifié la session à l'origine du problème ( vue v$session ), j'ai commencé par regarder les wait events: select * from v$session_wait  where sid = 20. On pouvait alors observer des wait events de type latch free. En exécutant la requête suivante: select * from v$latchname  where latch# = 98, j'ai identifié le nom du latch, cache buffers chains.

Puis, j'ai utilisé les deux requêtes suivantes:
- select count(*) from v$latch_children where name = 'cache buffers chains' => 1024 ( seule la session posant problème était active ).
- select name, gets, misses, sleeps  from v$latch_children where name = 'cache buffers chains' => 0 <= misses <= quelques centaines.

Ensuite, j'ai utilisé l'outil latchprof de T.Poder  pour identifier les requêtes à l'origine de ces latchs ( exemple: @latchprof sid,name,sqlhash % 2E6982FC 100000 , addr = 2E6982FC ). Après quelques lancements, on pouvait remarquer qu'une requête ( vue v$sql ) revenait systématiquement:
SELECTB.OWNER,B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE,B.OWNER,B.TABLE_NAME,A.OWNER,A.CONSTRAINT_NAME,A.OWNER,A.TABLE_NAME FROM SYS.DBA_CONSTRAINTS A,SYS.DBA_CONSTRAINTS B, SYS.DBA_OBJECTS O
WHERE A.CONSTRAINT_TYPE='R' AND A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.R_OWNER=B.OWNER  AND O.OWNER=A.OWNER AND O.OBJECT_NAME=A.TABLE_NAME AND O.OBJECT_TYPE='VIEW'  AND B.OWNER='XXXXX' AND
A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','CTXSYS','ORDSYS','OUTLN','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','WKSYS','WMSYS','XDB') AND NOT (B.TABLE_NAME LIKE 'BIN$%')
ORDER BY B.OWNER, B.TABLE_NAME

Un rapide coup d'oeil sur la vue v$sql_plan a permis de voir une multitude de nested loop et le non calcul des statistiques sur le dictionnaire.

Après avoir consulté la note 245051.1 sur le support d'Oracle ( anciennement Metalink ), j'ai lancé les statistiques sur le dictionnaire: execute dbms_stats.gather_schema_stats('SYS', cascade=>TRUE); pour résoudre le problème initial.

Pour aller plus loin: Latch, mutex and beyond

Dump d'un bloc d'index

Dans son blog, R.Foote détaille la structure interne d'un entête de bloc:
- http://richardfoote.wordpress.com/2010/07/20/index-block-dump-block-header-part-i-editions-of-you/ 
- http://richardfoote.wordpress.com/2010/07/28/index-block-dump-block-header-part-ii-and-read-consistency-i-cant-read/

Puis, il décrit un bloc d'index:
- http://richardfoote.wordpress.com/2010/08/10/index-block-dump-index-only-section-part-i-tvc-15/
- http://richardfoote.wordpress.com/2010/10/07/index-block-dump-index-only-section-part-ii-station-to-station/
- http://richardfoote.wordpress.com/2010/11/04/index-block-dumps-final-demo-come-together/

Event trace 10053

L'event trace 10053 permet de comprendre comment l'optimiseur a créé le plan d'exécution d'une requête. A chaque noeud du plan, on peut voir comment l'optimiseur a pris une décision, par exemple le choix d'un index range scan plutôt qu'un table full scan.

Pour étudier plus facilement ce type de trace, un viewer compatible 10g et 11g est disponible à cette adresse:
http://jonathanlewis.wordpress.com/2010/04/30/10053-viewer/.

Pour tenter de déchiffrer l'event trace 10053, vous pouvez consulter:
- le chapitre 14 du livre Cost Based Oracle Fundamentals écrit par J.Lewis;
- la note Metalink 338137.1: Analyzing 10053 Trace Files;
- la présentation de W.Breitling “A Look under the Hood of CBO – the 10053 Event”.

En 11g, on peut capturer une trace 10053 pour une requête appartenant à un package PL/SQL.