Index de stockage Exadata - Ventilateur racine

Les index de stockage sont une fonctionnalité d'Exadata conçue pour améliorer les performances en réduisant les E/S sur disque.

Contrairement aux index B-tree ou bitmap traditionnels, les index de stockage ne stockent pas de valeurs spécifiques pour faciliter la recherche d'enregistrements ; ils suivent plutôt les valeurs minimales, maximales et nulles des données dans des blocs de stockage de 1 Mo.

Voici comment ils fonctionnent :

Mécanisme de pré-filtrage: Les index de stockage agissent comme un pré-filtre. Lorsqu'une requête est exécutée, Exadata vérifie les prédicats (par exemple, les conditions de la clause WHERE) par rapport aux métadonnées stockées dans les index de stockage (valeurs min, max et null). Si l'index de stockage montre qu'aucune donnée d'un bloc de stockage de 1 Mo ne correspond aux conditions de la requête, ce bloc est ignoré, ce qui évite de devoir le lire sur le disque.
Fonctionnalité de mémoire seule: Les index de stockage sont entièrement conservés en mémoire sur les cellules de stockage et ne sont jamais écrits sur le disque. Ils sont donc volatils, ce qui signifie qu'ils doivent être reconstruits après le redémarrage d'un serveur de stockage.
Huit colonnes: Un index de stockage peut suivre jusqu'à huit colonnes d'une table. Il ne stocke pas les détails de chaque colonne, mais conserve dynamiquement celles qui sont le plus souvent interrogées.
Optimisation des requêtes: Les index de stockage sont particulièrement utiles lorsqu'ils sont utilisés avec Smart Scans, une fonction d'Exadata qui permet de traiter les requêtes sur le serveur de stockage. En pré-filtrant les données non pertinentes, les index de stockage aident les Smart Scans à réduire la quantité de données traitées et renvoyées à la couche de base de données.
Traitement des zéros: Les index de stockage fournissent également une optimisation unique pour les requêtes qui impliquent des valeurs nulles, permettant des recherches plus rapides sur les colonnes qui contiennent des valeurs nulles en éliminant les régions non nulles de la lecture.

Table des matières

Dans la pratique, les index de stockage peuvent améliorer considérablement les performances.

Ils sont plus efficaces lorsque les données dans les zones de stockage sont bien regroupées ou triées.

Cependant, il existe des limitations : ils ne fonctionnent pas avec certains types de données (par exemple, les CLOB) ou certains opérateurs de comparaison (par exemple, "!=").

En résumé, les index de stockage contribuent à réduire les E/S sur disque en permettant au système Exadata de ne pas lire les régions de stockage qui ne contiennent pas de données pertinentes, ce qui se traduit par des gains significatifs en termes de performances des requêtes.

Exemple d'index de stockage Exadata

Voici un exemple illustrant le fonctionnement des index de stockage dans Exadata :

Scénario

Supposons que vous ayez un tableau commandes qui contient les colonnes suivantes :

order_id
date_de_la_commande
numéro de client
montant_total

La table comporte 1 million de lignes et les données sont réparties entre plusieurs régions de stockage sur un système Exadata.

Demande de renseignements

Vous exécutez la requête suivante pour récupérer les commandes passées dans une plage de dates spécifique :

SELECT * 
FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Comportement de l'index de stockage

Création d'un index de stockage: Exadata crée automatiquement des index de stockage lorsque la table est fréquemment consultée.
Pour chaque bloc de données de 1 Mo stocké sur les cellules de stockage, Exadata enregistre les données suivantes minimum et maximum pour les date_de_la_commande colonne.

Exemple d'index de stockage pour une zone de stockage :
- Région 1: MIN(order_date) = '2023-11-15', MAX(order_date) = '2024-02-10'
- Région 2: MIN(order_date) = '2024-03-01', MAX(order_date) = '2024-03-31'
- Région 3: MIN(order_date) = '2023-10-05', MAX(order_date) = '2023-11-01'
Exécution de la requête: Lorsque vous exécutez la requête pour trouver des commandes à partir de janvier 2024, Exadata examine d'abord les index de stockage.
- Région 1 contient des dates entre '2023-11-15' et '2024-02-10', de sorte que cette région sera lue puisqu'il inclut une partie de janvier 2024.
- Région 2 contient des dates entre '2024-03-01' et '2024-03-31'Ainsi, Exadata saute cette région entièrement parce qu'il se situe en dehors de l'intervalle de la requête.
- Région 3 contient des dates entre '2023-10-05' et '2023-11-01'Ainsi, Exadata saute cette région également, car il n'est pas pertinent pour la requête.
Réduction des données: Au lieu d'analyser l'ensemble de la table, Exadata ne lit que les régions concernées sur le disque.
Cela permet de réduire les E/S, d'accélérer les requêtes et d'optimiser les performances du système.

Bénéfice

Dans ce cas, Exadata ayant ignoré les régions de stockage non pertinentes (région 2 et région 3), la requête se termine plus rapidement, ce qui se traduit par des coûts d'E/S nettement inférieurs.

C'est un exemple de la façon dont les index de stockage aident Exadata à contourner les données inutiles et à améliorer les performances des requêtes.

DDL de la création de tables et d'index :

Voici un exemple de langage de définition de données (DDL) permettant de créer le fichier commandes et comment créer un index sur cette table (si nécessaire).

Il convient de noter que index de stockage dans Exadata ne sont pas explicitement créées par un administrateur de bases de données.

Ils sont gérés automatiquement par le logiciel de stockage d'Exadata en fonction des données auxquelles on accède.

Cependant, je montrerai également comment vous devriez créer un index si vous avez affaire à un environnement non Exadata.

Exemple de DDL pour le `commandes` Tableau

CREATE TABLE orders (
    order_id       NUMBER PRIMARY KEY,
    order_date     DATE,
    customer_id    NUMBER,
    total_amount   NUMBER(10, 2)
);

Cela permet de créer une base commandes table avec un clé primaire sur order_id.

Le date_de_la_commande est celle qui est impliquée dans le filtrage de la requête pour l'exemple.

Note sur les index de stockage dans Exadata

En Exadata, vous ne pas créer manuellement des index de stockage.

Exadata les gère automatiquement en mémoire en fonction des requêtes qui accèdent fréquemment à certaines colonnes.

Vous ne pouvez pas contrôler la création de l'index de stockage comme vous le faites avec les index traditionnels (par exemple, B-tree ou bitmap).

Exemple de création d'un index traditionnel (en cas d'utilisation d'un système sans exadonnées)

Si vous voulez créer un index B-tree traditionnel sur date_de_la_commandevous écririez quelque chose comme ceci :

CREATE INDEX idx_order_date 
ON orders (order_date);

Cet index traditionnel permet d'optimiser les requêtes qui filtrent sur le date_de_la_commandemais son fonctionnement est différent de celui du index de stockage.

Comment fonctionnent les index de stockage dans Exadata

Avec Exadata, il n'est pas nécessaire de créer un index explicite sur date_de_la_commande pour bénéficier des index de stockage.

Le système Exadata crée et utilise de manière dynamique index de stockage au niveau de l'élément de stockage lorsqu'il détecte qu'une certaine colonne (comme date_de_la_commande) est fréquemment interrogé.

Il suit les valeurs minimales et maximales de chaque bloc de stockage, ce qui permet au système d'ignorer les blocs non pertinents lors d'une requête.

En résumé :

Indice traditionnel: Vous le créez manuellement (comme indiqué dans l'exemple ci-dessus).
Index de stockage (Exadata) : Géré automatiquement par le système, aucun DDL n'est requis.

Si vous exécutez votre base de données sur une machine Exadata, il vous suffit d'exécuter vos requêtes, et Exadata utilisera automatiquement les index de stockage pour les optimiser en fonction des modèles de requêtes.

Comment vérifier si un index de stockage a été utilisé ?

Dans Oracle Exadata, les index de stockage sont gérés automatiquement par le système, mais vous pouvez surveiller et vérifier si les index de stockage sont utilisés pendant l'exécution de la requête à l'aide de divers outils et vues de surveillance.

Voici quelques moyens de vérifier si un index de stockage a été créé ou utilisé pendant l'exécution de la requête :

1. V$SESSTAT Voir

Vous pouvez vérifier les statistiques d'utilisation de l'index de stockage par session à l'aide de la commande V$SESSTAT qui contient des statistiques au niveau de la session.

Plus précisément, la statistique octets d'E/S physiques de la cellule sauvegardés par l'index de stockage indique si des index de stockage ont été utilisés.

Voici une requête que vous pouvez utiliser pour vérifier si les index de stockage ont été utilisés par une session particulière :

SELECT s.sid, 
       s.value AS "Bytes Saved by Storage Index"
FROM v$sesstat s
JOIN v$statname n ON s.statistic# = n.statistic#
WHERE n.name = 'cell physical IO bytes saved by storage index';

Si la requête renvoie une valeur positive dans le champ Octets sauvegardés par l'index de stockage indique que des index de stockage ont été utilisés lors de l'exécution de la requête pour cette session.

2. Événements d'attente spécifiques à Exadata

Un autre indicateur de l'utilisation de l'index de stockage est la réduction des événements d'attente liés aux entrées/sorties.

Vous pouvez surveiller des événements d'attente spécifiques dans Exadata, tels que

cell smart table scan
balayage de l'index intelligent de la cellule

Ces événements indiquent que des analyses intelligentes sont en cours et que, par extension, les index de stockage peuvent avoir été impliqués dans la réduction des données à analyser.

Pour vérifier les événements d'attente spécifiques à Exadata :

SELECT event, total_waits, time_waited
FROM v$session_event
WHERE event LIKE 'cell smart%';

Un nombre élevé d'attentes pour les cell smart table scan peut indiquer que des index de stockage ont été utilisés pour ignorer des données non pertinentes au cours de l'analyse.

3. Commande CellCLI (sur les cellules de stockage)

Si vous avez accès aux cellules de stockage elles-mêmes, vous pouvez utiliser la fonction CellCLI (Cell Command-Line Interface) pour surveiller et analyser les performances des cellules de stockage. Il s'agit notamment de vérifier si les index de stockage sont utilisés.

Pour répertorier les statistiques actuelles des index de stockage sur une cellule de stockage, vous pouvez exécuter l'opération suivante CellCLI commande :

CellCLI> list metriccurrent where objectType = 'CELLIODATA' and name = 'DB_IO_BY_STORIDX'

Cela permet d'afficher les mesures relatives à l'utilisation de l'index de stockage sur cette cellule de stockage particulière.

Rechercher des valeurs dans le DB_IO_BY_STORIDX qui indique la quantité de données économisées grâce à l'utilisation d'index de stockage.

4. Rapport de surveillance SQL

Lorsqu'une requête éligible au Smart Scan (et potentiellement à l'utilisation d'index de stockage) est exécutée, vous pouvez générer un fichier Rapport de surveillance SQL pour obtenir des informations détaillées sur l'exécution.

Le rapport de surveillance SQL vous indiquera

Si Smart Scan a été utilisé.
La quantité de données filtrées à l'aide des index de stockage.

Pour générer le rapport de surveillance SQL, vous pouvez utiliser la commande SQL suivante :

SELECT dbms_sqltune.report_sql_monitor(sql_id => 'your_sql_id') 
FROM dual;

Ce rapport permettra de savoir si les index de stockage ont été utilisés, en examinant la réduction des données traitées lors des balayages intelligents.

5. Vue DBA_HIST_SYSSTAT (surveillance historique)

Pour vérifier l'utilisation historique des index de stockage au fil du temps, vous pouvez interroger la base de données DBA_HIST_SYSSTAT qui enregistre les statistiques de l'ensemble du système sur une période donnée.

SELECT s.snap_id,
       s.instance_number,
       ss.value AS "Storage Index IO Bytes Saved"
FROM dba_hist_sysstat ss
JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
WHERE ss.stat_name = 'cell physical IO bytes saved by storage index';

Cette requête donnera un aperçu du nombre d'octets d'E/S physiques économisés par les index de stockage au fil du temps, tel qu'enregistré dans les instantanés AWR (Automatic Workload Repository).

Résumé

Vous ne pouvez pas créer manuellement des index de stockage dans Exadata, mais vous pouvez surveiller et vérifier leur utilisation :

Vérification des statistiques au niveau de la session (V$SESSTAT).
Analyse des événements d'attente Exadata (cell smart table scan).
Utilisation des commandes CellCLI sur les cellules de stockage.
Génération de rapports de surveillance SQL.
L'examen des statistiques historiques du système (DBA_HIST_SYSSTAT).

Ces outils vous permettront de voir si et quand les index de stockage ont été créés et utilisés pour améliorer les performances des requêtes.

Faut-il créer des index dans Exadata ?

Pas nécessairement.

Tandis que index de stockage dans Exadata sont automatiquement gérées et créées dynamiquement par le système afin d'optimiser les performances, indices traditionnels (comme les index B-tree ou bitmap) peuvent encore être bénéfiques dans certains cas.

Voici une explication des cas où il est nécessaire ou non de créer des index traditionnels sur Exadata :

1. Quand les index de stockage suffisent

Accès séquentiel aux données: Si la plupart de vos requêtes impliquent le balayage séquentiel de grandes quantités de données (par exemple, les requêtes d'entrepôt de données avec balayage complet des tables), le système Exadata Scanners intelligents et index de stockage peut être extrêmement efficace pour réduire les E/S et améliorer les performances.
Interrogations sur les plages: Pour les requêtes portant sur des plages (comme les plages de dates), les index de stockage peuvent permettre d'ignorer les blocs non pertinents et de minimiser les E/S disque sans avoir besoin d'index traditionnels.
Nature dynamique: Les index de stockage sont créés et maintenus dynamiquement en mémoire, ce qui signifie qu'ils peuvent s'adapter aux modèles de charge de travail sans nécessiter l'intervention d'un administrateur de bases de données.

2. Quand les index traditionnels sont encore nécessaires

Charges de travail OLTP (Online Transaction Processing): En Environnements OLTPoù les requêtes accèdent souvent à des lignes individuelles ou à de petits ensembles de données (par exemple, la recherche d'un client par son numéro d'identification), les systèmes traditionnels d Index B-tree peut encore s'avérer nécessaire.
Les index de stockage d'Exadata sont les plus efficaces pour réduire les E/S pendant les balayages complets, mais ils ne sont pas utilisés pour les recherches sur une seule rangée.
Conditions d'adhésion: Si vos requêtes impliquent des jointures sur des colonnes à clé non primaire, les index traditionnels (par exemple, un index B-tree ou bitmap) peuvent contribuer à améliorer les performances des jointures en réduisant le nombre de lignes analysées.
Mises à jour des données: Dans les environnements où les met à jour ou supprimeLes index traditionnels peuvent contribuer à garantir un accès efficace aux lignes et des opérations de maintenance.
Requêtes Smart Scan non éligibles: Toutes les requêtes ne peuvent pas utiliser Smart Scan (par exemple, les requêtes portant sur de petites tables ou celles impliquant des opérateurs complexes).
Pour ces requêtes, les index traditionnels peuvent encore être utiles.

3. Cas particuliers pour lesquels vous pouvez encore avoir besoin d'index traditionnels

Contraintes uniques: La création d'index sur les colonnes avec des contraintes uniques (par exemple, clés uniques, clés primaires) est toujours nécessaire pour assurer l'intégrité des données.
Relations clés avec l'étranger: Les index sur les clés étrangères peuvent toujours être utiles pour éviter les balayages complets de la table lors des mises à jour ou des suppressions sur les tables parentes.
Systèmes sans exadonnées: Si vous avez des environnements mixtes dans lesquels les données peuvent être consultées à la fois sur des systèmes Exadata et non Exadata, les index traditionnels peuvent encore être nécessaires pour optimiser les performances sur les plates-formes non Exadata.

4. Charges de travail mixtes

Pour les systèmes qui ont charges de travail mixtes (une combinaison d'OLTP et de reporting), les index traditionnels peuvent compléter les index de stockage d'Exadata.

Par exemple :

Charges de travail OLTP bénéficier des index traditionnels pour une recherche rapide des lignes.
Requêtes analytiques bénéficier des index de stockage d'Exadata pour des analyses efficaces à grande échelle.

Conclusion :

Tandis que index de stockage peuvent réduire le besoin d'index traditionnels dans Exadata, ils ne les remplacent pas complètement. Vous devez toujours envisager de créer des indices traditionnels pour :

environnements OLTP ou charges de travail mixtes.
Requêtes nécessitant des recherches précises sur les lignes.
Renforcer les contraintes d'unicité et les relations de clés étrangères.

Dans un environnement d'entrepôt de données où les balayages complets de tables sont courants, les index de stockage, combinés à Smart Scan et aux optimisations d'Exadata, peuvent réduire considérablement le besoin d'indexation traditionnelle.

Toutefois, pour les charges de travail OLTP ou les cas impliquant des requêtes sélectives ou des conditions de jointure, les index traditionnels peuvent encore être essentiels pour des performances optimales.