ORA-04031: KGLH0 grossit de manière excessive

Sur une instance Oracle 11.2.0.2, j'ai rencontré dernièrement des problèmes sur une partie du shared pool, KGLH0.

Dans un premier temps, on m'a rapporté des arrêts répétés de l'instance inexpliqués, un startup de l'instance permettant de contourner le problème ... jusqu'au prochain arrêt. Au bout d'un certain nombre d'arrêt/relance, j'ai été voir les incidents de l'instance via adrci ( show incident ). On pouvait alors constater quelques incidents générant des erreurs ORA-4031.  Puis, en consultant le fichier trace ( show incident -mode detail -p "incident_id=xxxx" ) , on pouvait observer une allocation mémoire excessive de la KGLH0.

En consultant le support Oracle, le bug ID 1351675.1 décrit bien le problème observé. Sur l'instance en question, en utilisant la requête : select name, bytes from v$sgastat  where pool= 'shared pool' and name = 'KGLH0' , on s'aperçoit qu'au démarrage de l'instance, cette zone est initialisée à environ 12  MB, mais elle est susceptible de monter à plus de 300 MB dans certains cas, puis si l'instance n'est pas arrêtée, elle tombe en émettant plusieurs erreurs ORA-4031.

Si comme dans notre cas de figure, vous n'avez pas la possibilité de patcher l'instance, il ne vous reste plus qu'à écrire une procédure stockée vidant le shared pool ( alter system flush shared_pool ), cette dernière étant lancée par un job oracle. Mais comme l'indique la note du support, cela n'a pas suffi, et dans certains cas, la zone ne se désalloue pas.

Pour éviter de perturber les tests effectués sur cette instance, j'ai fini par me résoudre à faire un arrêt/relance de l'instance chaque soir via la crontab.

Ajout:
Quelques semaines plus tard, l'instance est de nouveau tombée. J'ai dû me résigner à augmenter le shared pool via le paramètre MEMORY_TARGET. Pour le flush du shared pool, j'ai omis de dire qu'il ne se déclenche qu'à partir d'un seuil ( 5% de la MEMORY_TARGET ), choix effectué après avoir observé plusieurs pics KGLH0.

La table X$KSMSP ( à ne pas utiliser en production ) contient la liste des différentes parties ( chunks ) du shared pool. Si vous souhaitez avoir une vision fine du shared pool, je vous conseille le chapitre 7 du dernier livre de J.Lewis et plus particulièrement le paragraphe sur les structures du shared pool.

En utilisant cette table durant un test, on peut observer les choses suivantes:

SQL> r
  1* SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE", sysdate FROM X$KSMSP GROUP BY KSMCHCLS

CLASS           NUM            SIZ    AVG SIZE     SYSDATE
-------- ---------- ---------- ------------ ---------
R-freea           268           22048          .08k  18-JAN-13
freeabl     74600 265871184      3.48k  18-JAN-13
recr         22646   48520808      2.09k  18-JAN-13
R-free             133      49402056    362.74k  18-JAN-13
R-recr                1         3977192 3,883.98k  18-JAN-13
R-perm            15       38615608  2,514.04k  18-JAN-13
free              4092    165599832       39.52k  18-JAN-13
perm                67    111648480   1,627.34k  18-JAN-13

8 rows selected.

SQL> shutdown abort; => pas très propre, mais c'est un test !
ORACLE instance shut down.
SQL> startup force;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2226472 bytes
Variable Size             973080280 bytes
Database Buffers           88080384 bytes
Redo Buffers                5550080 bytes
Database mounted.
Database opened.
SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE", sysdate FROM X$KSMSP GROUP BY KSMCHCLS;

CLASS           NUM        SIZ AVG     SIZE     SYSDATE
-------- ---------- ---------- ------------ ---------
R-freea             110          5280          .05k   18-JAN-13
freeabl        6575 17252784      2.56k  18-JAN-13
recr            9755 16840120       1.69k 18-JAN-13
R-free                55    37689072    669.20k 18-JAN-13
R-recr                 1      3977192   3,883.98k 18-JAN-13
R-perm             17     41701472   2,395.53k 18-JAN-13
free                  75       9716312      126.51k 18-JAN-13
perm                80   103499648   1,263.42k 18-JAN-13

8 rows selected.

Un peu plus tard :

SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE", sysdate FROM X$KSMSP GROUP BY KSMCHCLS;

CLASS           NUM        SIZ AVG            SIZE     SYSDATE
-------- ---------- ---------- ------------ ---------
R-freea              184              8832            .05k   18-JAN-13
freeabl       38200  117867568        3.01k  18-JAN-13
recr           38144    67380752        1.73k  18-JAN-13
R-free                 92       45562968       483.64k  18-JAN-13
R-recr                  1          3977192    3,883.98k  18-JAN-13
R-perm              17        41701472     2,395.53k 18-JAN-13
free                 579          5869440             9.90k 18-JAN-13
perm                 80      103499648       1,263.42k 18-JAN-13

8 rows selected.

=> alter system flush shared_pool;

CLASS           NUM        SIZ AVG          SIZE     SYSDATE
-------- ---------- ---------- ------------ ---------
R-freea              184             8832            .05k  18-JAN-13
freeabl       24286   76702584        3.08k 18-JAN-13
recr            20384   35864800        1.72k 18-JAN-13
R-free                   92     45562968       483.64k 18-JAN-13
R-recr                    1        3977192    3,883.98k 18-JAN-13
R-perm                17       41701472    2,395.53k 18-JAN-13
free                  1508      78550376         50.87k 18-JAN-13
perm                    80    103499648    1,263.42k 18-JAN-13

Par ailleurs, on peut effectuer un dump mémoire du shared pool ( oradebug dump heapdump 2, voir pdf de M.Modrakovic ) pour observer de nombreux chunks relatifs à KGLH0:

Chunk  700000036fde000 sz=     4096    freeable  "KGLH0^8df877b8 "  ds=70000007d124330
  Chunk  700000036fdf000 sz=     4096    freeable  "KGLH0^8df877b8 "  ds=70000007d124330
  Chunk  700000036fe0000 sz=     4096    freeable  "KGLH0^8df877b8 "  ds=70000007d124330
  Chunk  700000036fe1000 sz=     4096    freeable  "KGLH0^8df877b8 "  ds=70000007d124330
  Chunk  700000036fe2000 sz=     4096    freeable  "KGLH0^8df877b8 "  ds=70000007d124330
  Chunk  700000036fe3000 sz=     4096    freeable  "KGLH0^8df877b8 "  ds=70000007d124330
  Chunk  700000036fe4000 sz=     4096    recreate  "KGLH0^8df877b8 "  latch=0
     ds  70000007d124330 sz=    28672 ct=        7
         700000036fde000 sz=     4096
         700000036fdf000 sz=     4096
         700000036fe0000 sz=     4096
         700000036fe1000 sz=     4096
         700000036fe2000 sz=     4096
         700000036fe3000 sz=     4096

Sauvegarde et restauration d'une base

Cette tâche ne doit jamais être négligée. En effet, une erreur matérielle ou humaine est si vite arrivée ! Pour ce faire, vous pouvez utiliser l'outil fourni par Oracle, RMAN. Pour l'apprivoiser, je vous conseille la lecture de cet ouvrage: Recipes for Oracle Database 11g ( D.Kuhn, S.Alapati, A.Nanda ). Il vous aidera à configurer RMAN et à déterminer la méthode de restauration à employer suivant le crash rencontré .

La syntaxe des commandes RMAN est très verbeuse, d'où ce guide en cas d'oubli.

Il y a quelques années, arkzoyd a écrit un tutoriel intéressant sur le sujet.

Voici une configuration initiale pour une sauvegarde d'une base sur disque:
- configure controlfile autobackup on; -> sauvegarde du fichier du contrôle et du SPFILE pour un backup mais aussi en cas d'un changement de structure de la base, par exemple un ajout d'un tablespace.
- configure controlfile autobackup format for device type disk to '/apps/orafra/autobackup/controlfile_%F' -> DBID dans le nom du fichier de sauvegarde.
- configure device type disk backup type to compressed backupset parallelism n; -> compression des fichiers de sauvegarde fortement recommandée et parallélisation du traitement via l'ouverture de n canaux.
 
La commande show all permet de vérifier la configuration RMAN. Par défaut, elle se trouve dans le fichier de contrôle. Mais il est possible de la placer dans un catalogue.

Pour compléter cette configuration, on modifie les paramètres suivants:
- alter system set db_recovery_size = n G;
- alter system set db_recovery_dest = '/apps/orafra'; -> création de la flash recovery area.

La sauvegarde d'une base en mode NOARCHIVELOG ( bases de développement, de recette, ... ) se fait de la manière suivante:
run {
shutdown immediate; => au préalable, fermer toutes les sessions utilisatrices
startup mount;
backup full database tag = 'xxxxx';
alter database open;
}

Bien entendu, vous pouvez le scripter via une commande de ce type : rman cmdfile=xxx.rman log=yyy.log. Si vous avez un catalogue, vous pouvez créer une bibliothèque de scripts.

Pour la restauration, considérons cette erreur ORA : ORA-01103: database name 'A' in control file is not 'B'.

On peut la traiter de la manière suivante:
RMAN> startup nomount;
RMAN> set dbid DBID
RMAN> restore controlfile from autobackup maxdays 20;

Starting restore at 21-JAN-13
using channel ORA_DISK_1

recovery area destination: /apps/orafra/
database name (or database unique name) used for search: B
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130121
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130120
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130119
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130118
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130117
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130116
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130115
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130114
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130113
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130112
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130111
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130110
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130109
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130108
channel ORA_DISK_1: AUTOBACKUP found: c-DBID-20130108-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-DBID-00 => fichier déplacé dans le répertoire $ORACLE_HOME/dbs
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/apps/oradata01/B/onlinelog/ctl01.ctl
output file name=/apps/oradata02/B/onlinelog/ctl02.ctl
Finished restore at 21-JAN-13
RMAN>alter database mount;
RMAN>restore database;
RMAN>alter database open resetlogs;

On peut aussi avoir besoin de remplacer le SPFILE:
restore spfile to pfile '/xxx/initB.ora' from autobackup maxdays 20;

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.