Les 7 principaux événements d'attente Oracle expliqués

Les événements d'attente Oracle sont des statistiques qu'un processus serveur ou un thread incrémente lorsqu'il attend la fin d'une opération pour poursuivre son traitement.

Voici quelques-uns des événements d'attente les plus courants d'Oracle, expliqués.

Attentes d'occupation de la mémoire tampon

Cet événement se produit lorsqu'une session souhaite accéder à un bloc de base de données dans la mémoire tampon, mais qu'elle ne peut le faire parce que la mémoire tampon est occupée.

En termes simples : Imaginez que vous possédiez une console de jeux vidéo spéciale qu'une seule personne peut utiliser à la fois.

Si vous voulez jouer et que quelqu'un d'autre est déjà en train de l'utiliser, vous devez attendre qu'il ait terminé.

Dans Oracle, la "mémoire tampon" est comme la console de jeux vidéo : elle conserve les données importantes en mémoire.

Lorsqu'une tâche souhaite utiliser une partie de ces données (un bloc de base de données) mais que celle-ci est déjà utilisée par quelqu'un d'autre, la tâche doit attendre.

Cette attente est appelée "attente occupée par la mémoire tampon".

Merci, c'est beaucoup plus clair. Concentrons-nous plus particulièrement sur buffer busy waits et comment les résoudre.

🔍 Qu'est-ce que buffer busy waits?

• Cet événement d'attente se produit lorsque plusieurs sessions tentent d'accéder au même bloc de données dans le cache tamponmais le bloc est actuellement occupé (soit en lecture dans le cache, soit en modification par une autre session).
• Il s'agit d'un problème de contention à la couche de cache tamponIl ne s'agit donc pas d'un problème de disque.

🛠 Causes et solutions pour les enfants de moins de 18 ans buffer busy waits

1️⃣ Contention de blocs chauds (trop de sessions dans le même bloc)

• Exemple: De nombreuses sessions mettent à jour ou lisent le même petit ensemble de blocs (par exemple, des insertions dans le même bloc de table ou des mises à jour des mêmes lignes).
• Corrections:
  • Augmenter les listes de diffusion / groupes de diffusion (pour les tablespaces non-ASSM) pour répartir les insertions sur un plus grand nombre de blocs.
  • Passer à l'ASSM (Automatic Segment Space Management) afin d'éviter les goulets d'étranglement dans la liste libre.
  • Partitionner la table ou utiliser le partitionnement par hachage pour répartir les données sur plusieurs blocs.
  • Reconstruire les index ou répartir les données de manière plus homogène.

2️⃣ Annuler la contestation d'un segment

• Cause: Plusieurs utilisateurs mettent à jour des données en utilisant les mêmes blocs de segments d'annulation.
• Corrections:
  • Augmenter le nombre de segments d'annulation (fixer une valeur plus élevée pour les segments d'annulation). _undo_autotune ou ajuster l'annulation du dimensionnement de l'espace de stockage).
  • Réviser les paramètres de conservation des annulations.

3️⃣ Contention de séquence et d'index

• Cause: Les insertions rapides dans des tables avec des clés séquentielles (comme les champs ID) peuvent provoquer des blocages d'index.
• Corrections:
  • Utilisation index de clés inversées.
  • Utilisation index partitionnés par hachage ou indices mondiaux.
  • Mettre en œuvre séquences mises en cache (CRÉER UNE SÉQUENCE ... CACHE 1000).

4️⃣ Charges par trajet direct ou DML élevé

• Cause: Insertions directes simultanées ou opérations de masse fréquentes.
• Corrections:
  • Utilisation APPEND des conseils judicieux.
  • Échelonner les travaux par lots pour éviter la concurrence.
  • Veiller à ce que le parallélisme ne soit pas trop agressif.

5️⃣ Cache tampon inadéquat

• Cause: SGA insuffisant entraînant un remplacement fréquent des blocs et une relecture à partir du disque.
• Corrections:
  • Augmentation DB_CACHE_SIZE.
  • Utilisation Gestion automatique de la mémoire (AMM) ou Gestion automatique de la mémoire partagée (ASMM) correctement réglée.

📝 Comment diagnostiquer la cause exacte

SELECT event, p1 "File#", p2 "Block#", p3 "Class#",
       COUNT(*) AS waits
FROM v$session_wait
WHERE event = 'buffer busy waits'
GROUP BY event, p1, p2, p3
ORDER BY waits DESC;

👉 Utilisez ceci pour trouver la file# et block# causant l'attente, alors :

SELECT segment_name, segment_type
FROM dba_extents
WHERE file_id = &File#
AND &Block# BETWEEN block_id AND block_id + blocks - 1;

Cela vous indique quel objet (table/index) est responsable.

✅ Résumé des actions :

Attentes de la mémoire tampon libre

Cet événement se produit principalement lorsqu'un processus serveur tente de lire un nouveau tampon dans le cache tampon, mais que trop de tampons sont soit épinglés, soit sales et donc indisponibles pour la réutilisation.

La session envoie des messages à DBWR, puis attend que DBWR crée des tampons libres en écrivant les tampons sales sur le disque.

En termes simples : Imaginez que vous ayez une étagère de jouets complètement remplie.

Maintenant, vous voulez y mettre un nouveau jouet, mais il n'y a pas de place car tous les emplacements sont occupés par des jouets qui sont en train de jouer ou qui doivent d'abord être nettoyés.

Parfait - plongeons dans attente libre de la mémoire tampon en particulier, sur la base de l'événement d'attente que vous avez mentionné dans l'article.

🔍 Qu'est-ce que attente libre de la mémoire tampon?

• Cet événement se produit lorsque une session a besoin d'un tampon libre dans le cache tamponmais aucun n'est disponible car :
  1. Le DBWR (Database Writer) n'a pas encore écrit les tampons sales sur le disque pour libérer de l'espace.
  2. Le système génère des blocs sales plus rapidement que DBWR ne peut les rincer.

Il indique une goulot d'étranglement en écriture: la base de données veut réutiliser les tampons, mais ils ne sont pas encore libres.

🛠 Causes et solutions pour les enfants de moins de 18 ans attente libre de la mémoire tampon

1️⃣ Lenteur de l'E/S du disque

• Cause: DBWR n'est pas en mesure d'écrire les blocs sales assez rapidement pour libérer les tampons en raison de la lenteur des performances du disque.
• Corrections:
  • Déplacer fichiers de données à disques plus rapides (par exemple, SSD).
  • Équilibrer les E/S en répartissant les données sur plusieurs disques ou groupes de disques ASM.
  • Examiner et optimiser la Sous-système E/S (réglage SAN/NAS).

2️⃣ Débit insuffisant du rédacteur de la base de données

• Cause: Trop peu de processus DBWR pour traiter le volume de blocs sales.
• Corrections:
  • Augmenter le nombre de Processus DB Writer en utilisant : DB_WRITER_PROCESSES = (Dans les systèmes OLTP, plusieurs DBWR permettent de répartir la charge d'écriture).

3️⃣ Cache tampon trop petit

• Cause: Le cache tampon (SGA) est trop petit pour gérer la charge de travail, ce qui entraîne une accumulation fréquente de tampons sales.
• Corrections:
  • Augmentation DB_CACHE_SIZE (ou utiliser la gestion automatique de la mémoire (AMM/ASMM)).
  • Examiner les taux de réussite du cache de la mémoire tampon à l'aide de : SELECT name, value FROM v$sysstat WHERE name LIKE '%buffer cache%' ;

4️⃣ Volume élevé de tampons sales

• Cause: Les pics soudains de DML (mises à jour/insertions en masse) remplissent le cache de blocs sales plus rapidement qu'ils ne peuvent être écrits.
• Corrections:
  • Optimiser les instructions DML pour réduire les changements de bloc.
  • Utilisation Charges par trajet direct (indice APPEND) pour les insertions importantes afin de contourner le cache tampon.
  • Répartir les DML lourds dans le temps ou les programmer pendant les heures creuses.

5️⃣ Vérifier le point de contrôle et refaire

• Cause: Un réglage inefficace des points de contrôle peut retarder le recyclage de la mémoire tampon.
• Corrections:
  • Ajuster les paramètres du point de contrôle : FAST_START_MTTR_TARGET.
  • Augmentation taille du fichier journal afin d'éviter des points de contrôle trop fréquents.
  • Vérifier s'il y a des goulots d'étranglement au niveau des entrées/sorties du fichier redo log.

📝 Comment diagnostiquer attente libre de la mémoire tampon

1. Vérifier les sessions en cours : SELECT * FROM v$session_wait WHERE event = 'free buffer waits' ;
2. Vérifier les performances des E/S (temps d'écriture moyen) : SELECT file#, avg_time, writes FROM v$filestat ;
3. Vérifier les statistiques de la mémoire tampon sale : SELECT * FROM v$sysstat WHERE name LIKE '%dirty%' ;

✅ Tableau récapitulatif :

👉 This is a goulot d'étranglement au niveau du système-la solution passe souvent par le matériel (disques, mémoire) et la configuration (DBWR, cache, redo).

Lecture séquentielle d'un fichier de base de données

Cet événement montre l'attente d'un processus de premier plan lors d'une lecture séquentielle de la base de données.

L'E/S est généralement émise sous la forme d'une demande d'E/S unique au système d'exploitation ; l'attente se bloque jusqu'à ce que la demande d'E/S soit terminée.

En termes simples : Imaginez que vous ayez un livre d'images préféré, mais qu'il soit rangé sur une étagère en hauteur.

Lorsque vous souhaitez consulter une page spécifique, vous demandez à un ami de la saisir pour vous.

Pendant que votre ami va chercher la page, vous restez assis et attendez qu'il revienne avec la page.

Très bien, concentrons-nous maintenant sur lecture séquentielle d'un fichier de base de donnéesqui est l'un des événements d'attente Oracle les plus courants.

🔍 Qu'est-ce que lecture séquentielle d'un fichier de base de données?

• Cette attente se produit lorsque Oracle effectue lecture d'un seul bloc du disque vers la mémoire.
• C'est typique de :
  • Recherche d'index (en particulier les index B-tree)
  • Accès ligne par ligne via la table accès par ROWID
  • Boucles imbriquées jointures

Elle est dite "séquentielle" parce qu'Oracle lit un bloc à la fois (et non des blocs multiples).

🛠 Causes et solutions pour les enfants de moins de 18 ans lecture séquentielle d'un fichier de base de données

1️⃣ SQL inefficace (mauvais plans d'exécution)

• Cause: Requêtes utilisant les index de manière inefficace ou accédant aux données ligne par ligne inutilement.
• Corrections:
  • Utilisation EXPLIQUER LE PLAN pour revoir les chemins d'accès aux requêtes.
  • Considérer ajout d'index manquants ou réécriture de SQL pour améliorer les chemins d'accès.
  • Utilisation méthodes de jonction à bon escient : évitez les boucles imbriquées inutiles.

👉 Vérifier le plan d'exécution :

EXPLAIN PLAN FOR <your SQL>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2️⃣ Indices non sélectifs

• Cause: Les index qui récupèrent trop de lignes (faible cardinalité) sont toujours utilisés.
• Corrections:
  • Abandonnez ou évitez d'utiliser les index qui ne filtrent pas bien.
  • Considérer index bitmap pour les colonnes à faible cardinalité (systèmes OLAP/DSS).

3️⃣ Index manquants

• Cause: Les balayages complets de tables sont évités, mais sans index appropriés, Oracle se rabat sur des lectures lentes d'un seul bloc.
• Corrections:
  • Ajouter les index appropriés pour réduire l'accès à la table par ROWID.
  • Mettre en œuvre indices composites pour des conditions de filtrage multiples.

4️⃣ Stockage lent (goulot d'étranglement des E/S du disque)

• Cause: Même si les requêtes sont efficaces, un stockage lent peut entraîner des temps d'attente élevés.
• Corrections:
  • Déplacer les données vers disques plus rapides (SSD).
• Utilisation ASM striping ou une distribution adéquate des disques.

👉 Vérifier les entrées/sorties de fichiers :

SELECT file#, avg_time, reads, writes
FROM v$filestat
ORDER BY avg_time DESC;

5️⃣ Cache tampon insuffisant (trop d'E/S physiques)

• Cause: Les manques dans le cache tampon entraînent davantage de lectures sur le disque.
• Corrections:
  • Augmentation DB_CACHE_SIZE.
  • Révision et mise au point Gestion automatique de la mémoire (AMM/ASMM).

👉 Vérifier l'efficacité du cache :

SELECT name, value FROM v$sysstat
WHERE name IN ('physical reads', 'consistent gets', 'db block gets');

6️⃣ Objets chauds (accès fréquent aux mêmes blocs)

• Cause: Certains objets (index ou petites tables) sont accédés de manière répétée, ce qui entraîne une saturation de la mémoire tampon.
• Corrections:
  • Utilisation GARDER le pool de tampons pour les petites tables ou index fréquemment utilisés : ALTER TABLE your_table STORAGE (BUFFER_POOL KEEP) ;

📝 Requêtes de diagnostic rapide

1. Trouver des sessions d'attente :

SELECT sid, p1 file#, p2 block#, p3 blocks
FROM v$session_wait
WHERE event = 'db file sequential read';

2. Attribuer le fichier# et le bloc# à l'objet :

SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file#
AND &block# BETWEEN block_id AND block_id + blocks - 1;

✅ Tableau récapitulatif :

👉 Cette attente est normal pour les systèmes OLTP, mais des valeurs excessives signalent des problèmes.

Fichier de base de données dispersé en lecture

Il s'agit du même type d'événement que "db file sequential read", sauf qu'Oracle lira plusieurs blocs de données.

Les lectures multi-blocs sont généralement utilisées pour les balayages complets de tables.

Le nom "lecture dispersée" fait référence au fait que plusieurs blocs sont lus dans les tampons de blocs de la base de données qui sont "dispersés" dans la mémoire.

En termes simples : Imaginez que vous ayez un énorme livre d'images et que vous souhaitiez en feuilleter plusieurs pages rapidement.

Au lieu de demander une page à la fois (comme dans une lecture séquentielle), vous demandez un grand nombre de pages en même temps.

Ces pages peuvent provenir de différentes parties du livre, de sorte que lorsqu'elles arrivent, elles sont étalées ou "dispersées" sur votre table de lecture.

Excellent - attaquons fichier de base de données en lecture dispersée maintenant.

🔍 Qu'est-ce que fichier de base de données en lecture dispersée?

• Cet événement d'attente se produit lorsque Oracle effectue lectures multiblocs, typiquement associé à :
  • Balayage complet de la table
  • Numérisation rapide de l'index complet
• Il s'agit de "dispersé" car les blocs lus à partir du disque sont dispersés dans les tampons de mémoire au lieu d'être placés de manière séquentielle (les données sont donc dispersées dans le cache tampon).

C'est courants dans les environnements d'entrepôts de données (DSS) mais doivent être réduits au minimum dans les systèmes OLTP.

🛠 Causes et solutions pour les enfants de moins de 18 ans fichier de base de données en lecture dispersée

1️⃣ Scanners de la table complète inutiles

• Cause: Les requêtes accèdent à des tables entières au lieu d'utiliser des index.
• Corrections:
  • Ajouter les index appropriés (B-tree ou bitmap).
  • Utilisation EXPLIQUER LE PLAN afin d'assurer la l'index est effectivement utilisé.
  • Vérifier si statistiques manquantes ou périmées de l'optimiseur et les rassembler :
    EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMP') ;

2️⃣ Scans complets de petites tables

• Cause: Oracle choisit souvent des analyses complètes pour les très petites tablesce qui est généralement le cas.
• Corrections:
  • Il s'agit de normal et généralement pas de problème à moins que la fréquence ne soit extrême.

3️⃣ Exécution parallèle des requêtes

• Cause: Les requêtes parallèles sur les grandes tables amènent plusieurs sessions à lire simultanément des blocs dispersés.
• Corrections:
  • Optimiser degré de parallélisme en utilisant POLITIQUE_DEGRÉ_PARALLELE et LIMITE_DEGRÉ_PARALLELE.
  • Considérer cloisonnement les grandes tables afin de minimiser la nécessité d'effectuer des analyses complètes en parallèle.

4️⃣ Conception inefficace des requêtes (mauvais filtrage)

• Cause: Les requêtes permettent d'extraire de grands ensembles de résultats sans clauses WHERE sélectives.
• Corrections:
  • Affiner Où les conditions pour réduire le nombre de lignes renvoyées.
  • Évitez les **requêtes SELECT *** inutiles.
  • Révision logique d'application qui déclenche ces lectures.

5️⃣ Performance du système ou du stockage

• Cause: Même lorsque des numérisations complètes sont nécessaires (par exemple, pour les travaux par lots), lenteur des E/S du disque peut exacerber cette attente.
• Corrections:
  • Garantir le sous-système de disque est rapide (envisager les disques SSD, les E/S parallèles, le striping ASM).
  • Augmentation DB_FILE_MULTIBLOCK_READ_COUNT (bien qu'Oracle l'accorde automatiquement dans les versions modernes).

📝 Requêtes de diagnostic rapide

1. Identifier les sessions en attente :

SELECT sid, p1 file#, p2 block#, p3 blocks
FROM v$session_wait
WHERE event = 'db file scattered read';

2. Carte de l'objet :

SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file#
AND &block# BETWEEN block_id AND block_id + blocks - 1;

3. Trouver les SQL qui causent le plus de balayages complets :

SELECT sql_id, executions, buffer_gets, disk_reads
FROM v$sql
WHERE sql_id IN (SELECT sql_id FROM v$session WHERE event = 'db file scattered read')
ORDER BY disk_reads DESC;

✅ Tableau récapitulatif :

👉 Key Tip :

• fichier de base de données en lecture dispersée dans un OLTP est souvent un système de drapeau rouge → se concentrer sur indexation et optimisation des requêtes.
• En DSS/BI de l'environnement, il est souvent comportement attendu mais elle doit encore être efficace.

Mise en file d'attente

Cette mesure représente le nombre total d'attentes par seconde qui se sont produites au cours d'une conversion ou d'une récupération en file d'attente parce que la récupération en file d'attente a été différée.

En termes simples : Imaginez que vous ayez un toboggan spécial à l'aire de jeux que tout le monde veut utiliser.

Lorsque vous arrivez au toboggan et que vous constatez que quelqu'un d'autre est déjà dessus, vous devez attendre votre tour.

Chaque fois que vous attendez parce que la diapositive est occupée, il s'agit d'une "attente enqueue".

Parfait - décomposons enqueue attend dans Oracle et comment les résoudre.

🔍 What are enqueue Attend ?

• Un enqueue est un type de serrure Oracle utilise pour contrôler l'accès simultané aux ressources (comme les lignes, les tables, les index ou les structures internes).
• Lorsqu'une session ne peut pas obtenir immédiatement la serrure requise, il attend un enqueue wait event.

👉 Les attentes de type Enqueue sont généralement perçues comme :

enqueue: <type> - <details>

Exemple :

• enq : TX - contention du verrou de ligne
• enq : TM - contention
• enq : UL - contention

🛠 Types d'Enqueue courants et solutions

1️⃣ enq : TX - contention du verrou de ligne (le plus souvent)

• Cause: Une session est modification d'une ligne et qu'un autre essaie d'accéder à la même ligne → conduit à des verrous TX (transaction).
• Corrections:
  • Identifier et éliminer les conflits au niveau de l'application (même rangée mise à jour trop fréquemment).
  • Mettre en œuvre les transactions à découvertLes transactions de ce type se font plus tôt et évitent les longues périodes d'inactivité pendant lesquelles les verrous sont maintenus.
  • Considérer modifications de la logique d'application à sérialiser l'accès aux données sensibles (files d'attente, compteurs).

👉 Diagnostic :

SELECT * FROM v$lock WHERE type = 'TX';

Trouver une session de blocage :

SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

2️⃣ enq : TM - contention (DML sur les objets)

• Cause: Généralement observée dans les cas suivants questions clés concernant l'étranger ou verrouillage des métadonnées.
• Corrections:
  • Assurez-vous que les clés étrangères sont indexées (pour éviter les verrous de table lors des suppressions/mises à jour parent-enfant).
  • Évitez les DDL pendant que le DML est en cours d'exécution.

3️⃣ enq : TX - contention d'index (Blocs d'indexation)

• Cause: Insertions à haute teneur en devises dans le même index bloc feuille (par exemple, les identifiants séquentiels).
• Corrections:
  • Utilisation index de clés inversées.
  • Utilisation index globaux partitionnés par hachage pour diffuser des encarts.

4️⃣ enq : SQ (Contention de séquence)

• Cause: Sessions multiples à l'aide de l'outil même séquence sans cache → hotspot.
• Corrections:
  • Définir des séquences avec CACHE et/ou SANS COMMANDE: CREATE SEQUENCE my_seq CACHE 1000 NOORDER ;

5️⃣ enq : UL (Verrous définis par l'utilisateur)

• Cause: Application utilisant DBMS_LOCK ou des files d'attente personnalisées.
• Corrections:
  • Examiner et optimiser logique d'application en utilisant ces serrures.

6️⃣ enq : HW (Contestation de la ligne de flottaison)

• Cause: Fréquence élevée de les inserts à l'origine de la croissance du segment.
• Corrections:
  • Préallocation de l'espace à l'aide de : ALTER TABLE my_table ALLOCATE EXTENT ;
  • Considérer inserts parallèles ou la diffusion de données.

📝 Requêtes de diagnostic pour les attentes en file d'attente

1. Sessions en file d'attente :

SELECT sid, event, p1raw, p2, p3
FROM v$session
WHERE event LIKE 'enq%';

2. Bloqueur contre serveur :

SELECT sid, blocking_session, event, wait_class
FROM v$session
WHERE blocking_session IS NOT NULL;

3. Décodage détaillé de la file d'attente (facultatif) :

SELECT chr(bitand(p1, -16777216) / 16777215) ||
       chr(bitand(p1, 16711680) / 65536) "Lock Type"
FROM v$session
WHERE event LIKE 'enq%';

✅ Tableau récapitulatif :

👉 Conseil général :

• Dans la plupart des cas, les attentes de mise en file d'attente sont dues à une conception inefficace de l'application ou à des index manquants.
• Les réglages au niveau du système (SGA, I/O) ont peu d'effet...il s'agit principalement de corrections SQL et logiques.

Espace tampon du journal

Attendre qu'il y ait de la place dans le tampon d'enregistrement car nous écrivons dans le tampon d'enregistrement plus rapidement que lgwr ne peut l'écrire.

En termes simples : Imaginez que vous ayez un bloc-notes spécial sur lequel vous prenez rapidement des notes.

Il y a un assistant, la LBVR, dont le travail consiste à prendre vos notes et à les écrire dans un grand livre sûr.

Si vous écrivez des notes trop rapidement et que le bloc-notes se remplit avant que votre assistant ne puisse libérer de l'espace, vous devez faire une pause et attendre qu'il y ait à nouveau de la place.

Ce temps d'attente est appelé "espace tampon du journal".

Excellent choix - concentrons-nous sur espace de la mémoire tampon du journal les attentes dans Oracle, qui sont moins fréquentes mais critiques lorsqu'elles apparaissent.

🔍 Qu'est-ce que espace de la mémoire tampon du journal?

• Cet événement d'attente se produit lorsque
  • Oracle tente de copier entrées de répétition de la tampon journal (SGA) à la les redo logs en lignemais il y a pas d'espace libre dans la mémoire tampon du journal parce que les données de rétablissement n'ont pas encore été écrites sur le disque.

Il signale un goulot d'étranglement entre processus d'utilisateur générant du redo et LBVR (Log Writer) qui l'envoie dans les journaux de données (redo logs).

🛠 Causes et solutions pour les enfants de moins de 18 ans espace de la mémoire tampon du journal

1️⃣ Refaire les journaux sur un disque lent

• Cause: Le LBVR ne peut pas écrire les données de rétablissement sur le disque assez rapidement.
• Corrections:
  • Lieu les redo logs sur la mémoire la plus rapide disponible (de préférence des disques SSD ou dédiés).
  • Utilisation ASM avec une configuration de groupe de disques appropriée pour les redo logs.
  • Vérifier si latence du stockage.

👉 Vérifier les entrées/sorties du fichier journal :

SELECT * FROM v$logfile;

2️⃣ Petit tampon de journal de répétition (LOG_BUFFER)

• Cause: Le système en mémoire la mémoire tampon du journal est trop petitece qui entraîne des attentes fréquentes lorsque de nombreuses sessions génèrent des données à refaire.
• Corrections:
  • Augmenter la LOG_BUFFER dans le SPFILE ou PFILE. ALTER SYSTEM SET LOG_BUFFER = SCOPE=SPFILE ;
  • Les versions modernes d'Oracle procèdent à un réglage automatique, mais un réglage manuel peut encore s'avérer utile pour les systèmes à forte intensité de redondance.

👉 Vérifier la taille actuelle de la mémoire tampon du journal :

SHOW PARAMETER log_buffer;

3️⃣ LBV Sous-dimensionnée ou lente

• Cause: Le Processus de la LBVR n'est pas à la hauteur du volume de travail à refaire.
• Corrections:
  • Garantir LBVR n'est pas en concurrence avec d'autres processus pour l'utilisation de l'unité centrale.
  • Dans le CCR : vérifier que LGWR et canaux E/S sont correctement distribués.

4️⃣ Génération élevée de redondances (trop de COMMIT ou de DML)

• Cause: Taux très élevé de validations, de DML ou de travaux par lots générant d'importants volumes de données à refaire.
• Corrections:
  • Utilisation Commissions par lots au lieu de procéder à des validations fréquentes par ligne.
  • Optimiser les opérations DML pour réduire refaire (opérations en vrac, chargements par voie directe lorsque cela est possible).
  • Considérer NOLOGGATION des options pour les chargements de données lorsque la récupération n'est pas nécessaire.

👉 Identifier les sessions générant du redo :

SELECT sid, serial#, value
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'redo size'
ORDER BY value DESC;

5️⃣ Fichiers journaux de redémarrage trop petits (changements fréquents de journaux)

• Cause: Petits fichiers de redo log → changements fréquents de logs → pression accrue du LGWR → contention de la mémoire tampon.
• Corrections:
  • Augmentation taille du fichier redo log: ALTER DATABASE ADD LOGFILE SIZE 1G ;
  • Viser ne pas se connecter plus de 10 à 15 fois par heure en OLTP.

👉 Vérifier la fréquence du commutateur d'enregistrement :

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour, COUNT(*)
FROM v$log_history
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour DESC;

📝 Étapes du diagnostic rapide

1. Vérifier les sessions en attente sur la mémoire tampon du journal :

SELECT sid, event, wait_time, seconds_in_wait
FROM v$session
WHERE event = 'log buffer space';

2. Vérifier la taille des enregistrements et le taux de validation :

SELECT name, value FROM v$sysstat
WHERE name IN ('redo size', 'user commits');

3. Vérifier les performances d'écriture du LBVR :

SELECT name, value FROM v$sysstat
WHERE name LIKE '%log file%';

✅ Tableau récapitulatif :

👉 Key Notes :

• espace de la mémoire tampon du journal les attentes sont rares dans les systèmes bien configurés.
• Les solutions consistent presque toujours à le matériel (disques plus rapides), la configuration (taille des journaux, mémoire tampon) et le réglage des applications (modèles DML/commit).

Synchronisation des fichiers journaux

C'est le temps d'attente pour que le LGWR écrive dans les fichiers redo log.

En termes simples : Imaginez que vous jouez à un jeu où chaque mouvement que vous faites doit être noté dans un journal spécial pour que vous puissiez vous en souvenir.

Il y a un ami appelé LBVR dont le travail consiste à noter chaque mouvement.

Si vous jouez un coup et que vous devez attendre parce que votre ami est encore en train d'écrire le coup précédent dans le journal, il s'agit d'une attente de "synchronisation du fichier journal".

Cela signifie que vous faites une pause jusqu'à ce que la LBVR ait fini d'écrire votre mouvement dans les fichiers journaux.

Excellent choix...synchronisation des fichiers journaux est l'un des plus les événements d'attente courants et importants dans les bases de données Oracle, en particulier dans les systèmes OLTP. Voyons cela en détail.

🔍 Qu'est-ce que synchronisation des fichiers journaux?

• Cet événement d'attente se produit lorsqu'une session effectue une transaction et doit attendre que le LGWR (Log Writer) écrive les données de réécriture dans le fichier de réécriture. et confirmer qu'il est stocké en toute sécurité.
• Ce n'est qu'après la confirmation de cette écriture que l'utilisateur peut accuser réception de la validation.

👉 En bref :
Commit → LGWR écrit → User waits → synchronisation des fichiers journaux

🛠 Causes et solutions pour les enfants de moins de 18 ans synchronisation des fichiers journaux

1️⃣ Commits fréquents (Commit Flooding)

• Cause: L'application effectue une validation après chaque ligne ou trop fréquemment.
• Corrections:
  • Mettre en œuvre Commissions par lots: validation après le traitement de plusieurs lignes au lieu de chaque ligne.
  • Réviser la logique de l'application pour réduire les validations inutiles.

👉 Diagnostic :

SELECT name, value FROM v$sysstat WHERE name = 'user commits';

2️⃣ Lenteur des écritures du journal des rétablissements (goulot d'étranglement des E/S du disque)

• Cause: Les disques contenant les les fichiers redo log sont lents ou surchargés, ce qui retarde les écritures sur le LBV.
• Corrections:
  • Placer les journaux redo sur stockage à grande vitesse (SSD, Flash).
  • Isoler les redo logs des datafiles et autres disques occupés.
  • Utilisation ASM avec redondance et striping.

👉 Vérifier les performances du redo log :

SELECT name, value FROM v$sysstat WHERE name LIKE 'redo writes%';

3️⃣ Petits fichiers journaux Redo (trop de commutateurs)

• Cause: Fréquent commutateurs de journaux entraînant des retards et une augmentation de l'activité de la LGWR.
• Corrections:
  • Augmentation taille du fichier redo log pour réduire le nombre de commutations par heure.
  • Vérifier si enregistrer les tempêtes de commutation pendant les heures d'affluence.

👉 Vérifier l'historique des commutations du journal :

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour, COUNT(*)
FROM v$log_history
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour DESC;

4️⃣ Goulot d'étranglement de l'unité centrale affectant la LGWR

• Cause: La LGWR est en concurrence avec d'autres processus pour l'utilisation de l'unité centrale, ce qui entraîne des retards dans la validation des données.
• Corrections:
  • Vérifier Utilisation de l'unité centrale et veiller à ce que la LBVR ne soit pas privée de ressources.
  • Sur les systèmes à forte concurrence, il faut envisager d'ajouter une unité centrale ou d'optimiser la concurrence de l'application.

👉 Vérifier les temps d'attente des processus d'arrière-plan :

SELECT p.spid, s.sid, s.event, s.wait_time
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);

5️⃣ Génération élevée de redondances (trop de DML)

• Cause: De gros volumes de DML génèrent un nombre excessif de redo, ce qui surcharge le LGWR.
• Corrections:
  • Optimiser le DML : utiliser traitement en vrac, minimiser refaire inutilement.
  • Utilisation NOLOGGATION lorsque cela est sûr (chargements ETL, index).

👉 Vérifier le redo généré par transaction :

SELECT name, value FROM v$sysstat WHERE name LIKE '%redo%';

📝 Quick Diagnosis for synchronisation des fichiers journaux

1. Vérifier les sessions d'attente :

SELECT sid, event, seconds_in_wait, state
FROM v$session
WHERE event = 'log file sync';

2. Examiner les statistiques de validation et de rétablissement au niveau du système :

SELECT name, value FROM v$sysstat
WHERE name IN ('user commits', 'redo size', 'log file sync waits');

3. Si l'on utilise AWR ou Statspack, recherchez :

• synchronisation des fichiers journaux le temps d'attente moyen (devrait être idéalement < 1 ms, problématique si > 10 ms).

✅ Tableau récapitulatif des solutions :

👉 Principaux enseignements :

• Haut synchronisation des fichiers journaux → indique temps de réponse de l'engagement des problèmes, souvent perceptibles par les utilisateurs finaux.
• Il est étroitement lié à Performances du LGWR, sous-système de rétablissement et modèles de validation des applications.

J'espère que cela vous a été utile.