Result caching

Dernièrement, j'ai testé cette fonctionnalité sur une instance 11.2.0.2. Il s'agit d'un cache supplémentaire de données situé dans le shared pool; il est partageable par toutes les sessions.

Lorsqu'une requête contient le hint result_cache ou depuis la 11.2, si on utilise l'instruction alter table nom_table  result_cache ( mode force ), le résultat des requêtes est placé dans ce cache.  Si une requête est bindée, il existe une entrée pour chaque n-uplet de bind variables. Pour la deuxième option ( alter table ), il peut être utile d'augmenter la taille initiale du cache: alter system set result_cache_max_size = x m scope=both.

A chaque fois qu'une table référencée dans une requête est modifiée, l'entrée correspondante dans le cache est invalidée et la requête s'exécute alors classiquement avant de créer une nouvelle entrée dans le cache avec les données mises à jour ( colonne build_time ). Afin d'optimiser le contenu du cache, la vue v$result_cache_objects permet de répérer les requêtes fréquemment invalidées ansi que celles bénéficiant d'un hit intéressant ( colonne scan_count ).

La vue v$result_cache_dependency permet d'identifier les tables utilisées par une entrée du cache. Puis, on peut trouver la dernière modification de type DML de la table via cette requête: select scn_to_timestamp(max(ora_rowscn) from nom_table. D'autres méthodes sont disponibles: utilisation de la fonction d'audit, alter table monitoring, v$sql, ...

Sur quelques exemples testés, le gain en performance est appréciable. Cependant, on peut regretter qu'il soit difficilement évaluable lorsqu'on décide d'associer une table au cache. En effet, la vue v$resultat_cache_objects stocke l'identifiant du cache ( colonne cache_id ) apparaissant dans le plan d'exécution et non l'identifiant de requête. Et la vue ne conserve que les 128 premiers caractères de chaque requête ...

La vue v$result_cache_statistics permet d'avoir une vue générale du fonctionnement du cache ( nombre d'entrées invalidées, nombre de hits, ... ) et de s'assurer que la taille du cache est correcte ( colonne Delete Count Valid ). On peut aussi obtenir un rapport de la mémoire du cache via la procédure dbms_result_cache.memory_report.

Pour finir, il existe le même type de fonctionnalité pour les résultats des fonctions PL/SQL.

Liste de liens

Si votre pool de connexion est gourmand en ressources côté serveur, paramétrer l'instance en mode partagé peut être une alternative ( http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1644 ).

Ne saturez pas votre listener ( http://milek.blogspot.com/2008/10/oracle-listener-tcpip-and-performance.html ). Au-delà du tip, un pool de connexion client évite ce type de contention.

Un guide pour construire un datawarehouse: http://structureddata.org/2009/12/14/the-core-performance-fundamentals-of-oracle-data-warehousing-introduction/

Une méthode pour optimiser les fonctions PL/SQL dans les requêtes: http://www.oracle-developer.net/display.php?id=426

RAC à la mano, by arkzoyd: http://blog.easyteam.fr/2010/11/25/creer-une-base-rac-a-la-main-en-ligne-de-commande/

Exadata storage indexes: http://richardfoote.wordpress.com/category/storage-indexes/

Oracle OLAP: http://oracleolap.blogspot.fr/
 

OCILIB

Après quelques tests, je vous incite à découvrir cette librairie. Elle dispose de nombreuses fonctionnalités, la mémoire est très bien gérée ( pas de fuite ) et  ses performances sont comparables à celles du PRO*C. 

En plus des exemples de code fournis sur le site officiel, voici quelques exemples supplémentaires afin de vous faire une première idée sur le sujet.

Pour information, lors du test d'un failover de type serveur sur un RAC 10g, le programme utilisant un pool de connexion ( test_pool.c ) a généré un coredump ( OCI-21500 ) lors de l'arrêt d'une instance ou d'un service. Il serait intéressant d'effectuer le même test sur une architecture 11g ( client + RAC ) pour voir si l'erreur perdure.

Pour un failover de type client, on n'obtient plus de coredump mais une erreur ORA-03113 ( end-of-file on communication channel ). On aurait préféré une erreur dans l'intervalle 25400-25425. Heureusement, le programme reprend après cette erreur ...
 

Etudes sur les IOTs

Martin Widlake, membre de la Oak Table, a regroupé dans ce message iots-by-the-oracle-indexing-expert deux études sur les IOT ( Index Organized Table ), la première menée par lui-même, la seconde par R.Foote.

OCILIB

Actuellement, je participe à un projet de réécriture d'une librairie C d'accès aux bases Oracle ( code peu maintenable, allocation mémoire trop importante, ... ). Pour ce faire, nous avons décidé d'utiliser la librairie ocilib, une encapsulation de la couche OCI. Dans notre cas de figure, on travaille sur une machine Solaris et on utilise un client Oracle 10g.

Notre cahier des charges est le suivant:
- Pool de connexion client;
- Appel à des procédures stockées utilisant des bind variables;
- Exécution de requêtes utilisant des bind variables;
- Utilisation du package dbms_output.

A mon avis, cette librairie open source offre les deux avantages suivants:
- Code plus concis et plus lisible qu'en PRO*C, d'où une meilleure maintenabilité;
- API moins complexe à mettre en oeuvre que le PRO*C; on se rapproche d'une API de type JDBC.

Par ailleurs, on a demandé à Vincent Rogier de nous accompagner pendant les développements, ce qu'il a accepté.

Pour le moment, les développements se passent bien.

Reste quelques points à vérifier ultérieurement:
- Gestion de la mémoire ( fuite );
- Performance attendue de l'ordre de la dizaine de ms.

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.