Vue d'ensemble du paramètre

Le paramètre caché _serial_direct_read influence la manière dont Oracle gère les lectures d'opérations sérielles, comme les scans de table complets. Il détermine si ces lectures contournent la mémoire cache tampon (buffer cache) pour aller directement du disque à la PGA du processus serveur.

Les valeurs possibles sont :

• AUTO (défaut) : L'optimiseur Oracle prend une décision dynamique.
• TRUE : Force systématiquement l'utilisation de la lecture directe.
• FALSE : Force systématiquement l'utilisation de la mémoire cache tampon.
• NEVER : Interdit strictement la lecture directe.

L'utilisation de la lecture directe présente des avantages et des inconvénients. Elle évite de saturer la mémoire cache tampon avec des données lues en bloc, ce qui est bénéfique pour les très grands objets. Elle réduit également la contention sur les structures de la cache. En contrepartie, elle augmente le nombre de lectures physiques I/O disque et n'est pas adaptée aux tables consultées fréquemment, car chaque lecture doit être effectuée à nouveau.

Environnement de test et configuraton

L'objectif est d'observer le comportement du paramètre dans son mode par défaut (AUTO). Le test a été réalisé sur une base de données Oracle version 23c, hébergée sur l'infrastructure cloud Oracle. Les connexions clientes utilisaient l'Oracle Instant Client et un script Python avec le connecteur cx_Oracle.

Deux jeux de données ont été créés pour contraster les comportements :

1. Une table volumineuse nommée big_dataset, contenant approximativement 50 000 enregistrements et occupant ~72 Mo d'espace. Les données ont été générées avec des colonnes de type VARCHAR2(1000) remplies pour assurer une taille significative par ligne.
2. Une table de petite taille, small_reference, peuplée à partir du dictionnaire de données et contenant 100 lignes.

Pour garantir que seul le comportement sériel soit testé, le parallélisme a été désactivé au niveau de la session : ALTER SESSION DISABLE PARALLEL QUERY;. Cette étape est cruciale car une requête parallèle ignorerait toujours ce paramètre et utiliserait la lecture directe.

Protocole d'expérimentation

Étape 1 : Initialisation et captures de statistiques

Avant d'exécuter la requête principale, l'identifiant de la session (SID) a été récupéré. Ensuite, un ensemble de statistiques de performance spécifiques a été capturé pour cette session via les vues v$sesstat et v$statname. Les indicateurs clés retenus étaient : le nombre total de lectures physiques (physical reads), le nombre de lectures directes (physical reads direct) et le compteur de scans de grandes tables (table scans (long tables)).

-- Récupération des compteurs initiaux
SELECT s.name, t.value
FROM v$statname s
JOIN v$sesstat t ON s.statistic# = t.statistic#
WHERE t.sid = (SELECT sid FROM v$mystat WHERE ROWNUM = 1)
  AND s.name IN ('physical reads', 'physical reads direct', 'table scans (long tables)');

Les valeurs initiales relevées étaient par exemple : lectures physiques = 8 603, lectures directes = 8 336, scans de grandes tables = 0.

Étape 2 : Exécution de la requête cible et analyse

Une requête de comptage sur la table volumineuse a été exécutée. L'astuce FULL a été utilisée pour s'assurer qu'un accès complet à la table était bien effectué.

SELECT /*+ FULL(big_dataset) */ COUNT(*) FROM big_dataset;

Immédiatement après, les mêmes statistiques de performance ont été à nouveau capturées pour calculer la différence.

Étape 3 : Analyse du plan d'exécution et calcul des deltas

Le plan d'exécution de la requête a été généré pour confirmer la méthode d'accès. Le résultat indiquait une opération de type TABLE ACCESS FULL avec un degré de parallélisme de 1, comme attendu.

Le calcul des différences a révélé les variations suivantes :

• Augmentation totale des lectures physiques : 8 334 blocs.
• Augmentation des lectures directes : 0 bloc.

Cela indique que toutes les lectures ont été satisfaites via la mémoire cache tampon. Le taux de lecture directe était de 0% pour ce scan d'une table de 72 Mo dans la configuration par défaut.

Interprétation des résultats et facteurs d'influence

Plusieurs facteurs peuvent expliquer pourquoi l'optimiseur a choisi de ne pas utiliser la lecture directe dans ce test :

1. Taille du tableau et seuils internes : Oracle interne possède un seuil (lié à _small_table_threshold). Une table de 72 Mo peut ne pas dépasser ce seuil dans certains environnements ou configurations mémoire, la classifiant comme "pas assez grande".
2. État de la mémoire cache : Si les blocs de la table étaient déjà présents dans la buffer cache (ce qui peut arriver si la table a été accédée récemment), Oracle les réutilise plutôt que de relancer une lecture I/O physique.
3. Optimisations spécifiques à la plateforme : Sur des architectures optimisées comme Exadata (souvent utilisé pour les services gérés comme Autonomous DB), des mécanismes comme le Smart Scan peuvent prendre le pas sur la lecture directe traditionnelle.

Lorsque le paramètre est sur AUTO, la décision d'utiliser ou non la lecture directe n'est pas statique. Elle dépend d'une heuristique complexe qui évalue la taille de l'objet, la charge de la mémoire cache tampon, l'historique d'accès à l'objet et la charge globale du système.

Stratégies pour contrôler le comportement

Pour forcer l'utilisation de la lecture directe, plusieurs approches existent, avec des prérequis différents :

1. Modifier le paramètre : Cela nécessite des privilèges DBA. Le changement peut être effectué au niveau de la session (ALTER SESSION SET "_serial_direct_read" = TRUE;) ou au niveau système (nécessitant un redémarrage de la base).
2. Utiliser des indications SQL : L'indication FULL sur une tible peut inciter l'optimiseur à envisager la lecture directe, mais elle ne le garantit pas toujours.
3. Activer le parallélisme : Toute requête parallélisée (PARALLEL(t, N)) utilisera systématiquement la lecture directe, indépendamment du paramètre _serial_direct_read.

Le choix de la valeur du paramètre dépend du cas d'usage :

• Pour des environnements d'analyse décisionnelle (OLAP) ou des traitements par lots sur de grands volumes, forcer TRUE peut être bénéfique pour la performance de la mémoire cache globale.
• Pour des systèmes transactionnels (OLTP) avec de nombreux petits accès, la valeur FALSE garantit la réutilisation de la mémoire cache.
• La valeur AUTO reste le choix le plus prudent pour des charges de travail mixtes.

Surveillance et diagnostics

Pour surveiller l'activité de lecture directe, les métriques à observer sont principalement dans les vues de statistiques de session et système. Le compteur physical reads direct est l'indicateur clé. Il peut être agrégé par session (v$sesstat) ou consulté au niveau du système (v$sysstat). Comparer physical reads direct au physical reads total donne le ratio d'utilisation de la lecture directe.