Utiliser le cache de table externe pour améliorer les performances des tables externes

Lorsque la mémoire cache est créée, elle est initialement vide et activée pour l'alimentation. La taille du cache augmente chaque fois qu'un fichier est ajouté, en fonction des limites de quota d'espace définies pour le schéma, jusqu'à ce qu'il atteigne les limites affectées. Pour plus d'informations, reportez-vous à Définition de préférences de dimensionnement facultatives pour les caches basés sur une stratégie.

Une fois que vous avez créé un cache, vous pouvez l'alimenter. Le remplissage des fichiers charge le contenu des fichiers de table externes spécifiés dans le cache. Vous pouvez choisir de remplir tous les fichiers d'une table, un pourcentage spécifique de la table, ou spécifier une condition de filtre pour limiter les fichiers que vous souhaitez remplir. Par exemple, vous pouvez filtrer les fichiers en fonction de leur nom ou d'une plage de dates.

Ajouter une table au cache de table externe

Utilisez DBMS_EXT_TABLE_CACHE.ADD_TABLE pour remplir une table entière ou un certain pourcentage de la table externe dans le cache.

Exemples

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_TABLE (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES');
END;
/

Cet exemple tente de remplir la table STORE_SALES dans le cache, en ignorant tous les fichiers existants qui ont déjà été remplis.

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_TABLE (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES',
    percent_files => 80);
END;
/

Cet exemple tente de remplir 80 % de la table STORE_SALES dans le cache, en ignorant tous les fichiers existants qui ont déjà été remplis.

Le paramètre percent_files est facultatif. Si vous n'indiquez pas ce paramètre, la table entière est alimentée dans le cache.

Pour plus d'informations, reportez-vous à Procédure ADD_TABLE.

Ajouter des fichiers au cache de table externe

Exemples

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_FILE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    file_url     => 'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet'
);
END;
/

Cet exemple renseigne les données du fichier salesdata.parquet dans le cache.

Cet exemple ignore l'alimentation du fichier dans le cache si le fichier spécifié existe dans le cache et n'a pas été modifié depuis le dernier cache du fichier.

Pour plus d'informations, reportez-vous à Procédure ADD_FILE.

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales%.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/customer%.parquet"]'
);
END;
/

Cet exemple remplit tous les fichiers avec des noms commençant par sales ou customer, tout en excluant les fichiers déjà remplis.

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data2.parquet"]',
    esc_char     => '#',
    force        => TRUE);
END;
/

Cet exemple renseigne les fichiers sales_data1.parquet et sales_data2.parquet dans le cache.

Dans cet exemple, le caractère '#' est défini comme caractère d'échappement. Le caractère '_' suivant '#' est traité comme un trait de soulignement littéral et non comme un caractère générique correspondant à un caractère unique.

Pour plus d'informations, reportez-vous à Procédure ADD_BY_LIKE.

BEGIN
  DBMS_EXT_TABLE_CACHE.ADD_LATEST_FILES (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    since        => INTERVAL '7' DAY,
    max_files    => 5,
    force        => TRUE);
END;
/

Le paramètre since indique l'intervalle de temps. Seuls les fichiers modifiés au cours des sept (7) derniers jours peuvent être renseignés dans le cache.

Le paramètre max_files limite le nombre de fichiers pouvant être renseignés dans le cache. Cet exemple ne renseigne que cinq (5) fichiers.

Le paramètre force force l'écrasement des fichiers indiqués dans le cache même s'ils n'ont pas été modifiés.

Pour plus d'informations, reportez-vous à Procédure ADD_LATEST_FILES.

Effacer le cache de table externe

Utilisez DBMS_EXT_TABLE_CACHE.CLEAR pour supprimer tous les fichiers du cache de table externe. Par exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.CLEAR (
    owner         => 'SALES', 
    table_name    => 'STORE_SALES');
END;
/

Cet exemple supprime tous les fichiers du cache STORE_SALES et libère tout l'espace utilisé par les fichiers supprimés.

Pour plus d'informations, reportez-vous à la section Procédure CLEAR.

Supprimer des fichiers du cache de table externe

Exemples

Utilisez DBMS_EXT_TABLE_CACHE.DROP_FILE pour supprimer un fichier du cache de table externe. Par exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_FILE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    file_url     => 'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet'
);
END;
/

Cet exemple supprime le fichier salesdata.parquet du cache et libère tout l'espace utilisé par le fichier supprimé.

Pour plus d'informations, reportez-vous à Procédure DROP_FILE.

Utilisez DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE pour supprimer des fichiers en fonction du paramètre path_filters. Par exemple :

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/salesdata1.parquet"]'
);
END;
/

Cet exemple supprime les fichiers salesdata.parquet et salesdata1.parquet du cache et libère tout l'espace utilisé par les fichiers supprimés.

BEGIN
  DBMS_EXT_TABLE_CACHE.DROP_BY_LIKE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    path_filters => '["https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data1.parquet",
                      "https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/your_namespace/your_bucket/sales#_data2.parquet"]'
);
END;
/

Cet exemple supprime les fichiers sales#_data1 et sales#_data2 du cache et libère tout l'espace utilisé par les fichiers supprimés.

Dans cet exemple, le caractère '#' est défini comme caractère d'échappement. Le caractère '_' suivant '#' est traité comme un trait de soulignement littéral et non comme un caractère générique correspondant à un caractère unique.

Pour plus d'informations, reportez-vous à Procédure DROP_BY_LIKE.

Utilisez DBMS_EXT_TABLE_CACHE.RETIRE_FILES pour supprimer des fichiers en fonction de l'intervalle indiqué. Par exemple :

BEGIN
 DBMS_EXT_TABLE_CACHE.RETIRE_FILES (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES',
    before       => INTERVAL '30' DAY);
END;
/

Cet exemple supprime les fichiers qui datent de plus de trente (30) jours du cache et libère tout l'espace utilisé par les fichiers supprimés.

Pour plus d'informations, reportez-vous à Procédure RETIRE_FILES.

Les exemples ci-dessus suppriment un ou plusieurs fichiers du cache tout en conservant le cache. Si nécessaire, vous pouvez charger à nouveau les fichiers dans le cache. Pour plus d'informations, reportez-vous à Alimentation de fichiers dans le cache de table externe.

Exécutez DBMS_EXT_TABLE_CACHE.DISABLE pour désactiver le cache de table externe de la base de données. La désactivation d'un cache ne supprime pas les données du cache. Au lieu de cela, le cache est marqué comme DISABLED et l'optimiseur ne peut pas utiliser le cache pour les réécritures de requête.

Exemple

BEGIN
    DBMS_EXT_TABLE_CACHE.DISABLE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES');                                                                 
END;                                                                 
/

Cet exemple désactive le cache STORE_SALES.

Pour plus d'informations, reportez-vous à Procédure DISABLE.

Une fois que vous avez désactivé un cache de table externe, utilisez DBMS_EXT_TABLE_CACHE.ENABLE pour l'activer.

BEGIN
  DBMS_EXT_TABLE_CACHE.ENABLE (
    owner        => 'SALES', 
    table_name   => 'STORE_SALES'
 );
END;
/

Cet exemple active le cache STORE_SALES.

Pour plus d'informations, reportez-vous à Procédure ACTIVABLE.

Exécutez DBMS_EXT_TABLE_CACHE.DROP_CACHE pour supprimer un cache de table externe. La procédure DBMS_EXT_TABLE_CACHE.DROP_CACHE supprime le cache de table externe indiqué de la base de données et libère l'espace de stockage associé au cache.

Exemple :

BEGIN
    DBMS_EXT_TABLE_CACHE.DROP_CACHE (      
      owner          => 'SALES',
      table_name     => 'STORE_SALES');                                                             
END;                                                                 
/

Cet exemple supprime le cache STORE_SALES du schéma SALES.

La suppression d'un cache enlève ses métadonnées du dictionnaire de données et supprime toutes ses données en cache.

Pour plus d'informations, reportez-vous à Procédure DROP_CACHE.

SELECT external_table_name, cached
  FROM user_external_tab_caches;

Pour plus d'informations, reportez-vous aux vues DBA_EXTERNAL_TAB_CACHES et USER_EXTERNAL_TAB_CACHES.

Par défaut, le cache de table externe est désactivé pour un utilisateur. Pour activer et créer le cache de table externe, utilisez la procédure DBMS_EXT_TABLE_CACHE.CREATE_CACHE. Le cache est créé dans le schéma par défaut et hérite des limites de quota d'espace définies pour le schéma. Toutefois, vous pouvez également utiliser la procédure DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY pour définir des quotas d'espace pour le cache de table externe. Vous utilisez les paramètres PROPERTY_NAME et PROPERTY_VALUE de la procédure DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY pour définir les limites de quota d'espace.

Le paramètre PROPERTY_NAME accepte les valeurs MAX_CACHE_SIZE et MAX_CACHE_PERCENT. La propriété MAX_CACHE_SIZE indique la taille totale du cache externe en octets. La propriété MAX_CACHE_PERCENT indique la taille totale du cache externe en pourcentage du quota de l'utilisateur spécifié.

Exemples

BEGIN
    DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY (
    property_name  => 'MAX_CACHE_PERCENT',          
    property_value => 50,                   
    owner          => 'SALES');                                                                
END;                                                                 
/

Cet exemple définit la préférence de mise en cache sur MAX_CACHE_PERCENT pour le schéma SALES.

property_value est égal à 50 %, ce qui indique que le quota d'espace de cache pour le schéma SALES correspond au maximum à 50 % du quota d'espace total défini pour SALES.

BEGIN
    DBMS_EXT_TABLE_CACHE.SET_USER_PROPERTY (
    owner          => 'SALES',   
    property_name  => 'MAX_CACHE_SIZE',          
    property_value => 5368709120);                                                                
END;                                                                 
/

Cet exemple définit la préférence de mise en cache sur MAX_CACHE_SIZE pour le schéma SALES.

property_value est 5368709120, ce qui indique que la taille maximale du cache pour le schéma SALES est de 5 Go maximum.

Pour plus d'informations, reportez-vous à Procédure SET_USER_PROPERTY et à Procédure CREATE_CACHE.

Utilisez DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY pour extraire les propriétés de taille de cache.

Exemple :

SET SERVEROUTPUT ON
    DECLARE
    max_cache_sz NUMBER,
    BEGIN
    max_cache_sz := DBMS_EXT_TABLE_CACHE.GET_USER_PROPERTY (
        property_name       => 'MAX_CACHE_SIZE',         
        owner               => 'SALES');                                                                
END;                                                                 
/

Pour plus d'informations, reportez-vous à Fonction GET_USER_PROPERTY.

Notez l'ordre de priorité suivant pour définir les propriétés de taille de cache :

Par défaut, la mise en cache automatique est désactivée. Vous pouvez activer la mise en cache automatique pour vos tables externes de manière globale pour tous les utilisateurs de base de données ou pour un utilisateur spécifique. Une fois la mise en cache automatique activée, la base de données crée automatiquement des caches de table externes en fonction des statistiques d'utilisation des requêtes de table externe disponibles dans les SQL Tuning Sets (STS - Ensembles de réglages SQL). Tous les caches nouvellement créés sont marqués comme AUTO. Les caches existants continuent de suivre les paramètres de gestion du cache basés sur une stratégie.

Lorsque vous activez la mise en cache automatique, vous pouvez éventuellement spécifier la portée de l'actualisation du cache, c'est-à-dire les caches pouvant être actualisés à chaque cycle d'actualisation, et définir la fenêtre de temps autorisée pour terminer le processus d'actualisation.

Vous utilisez DBMS_CACHE.SET_GLOBAL_PROPERTY ou DBMS_CACHE.SET_USER_PROPERTY pour définir les propriétés de mise en cache AUTO, y compris les propriétés permettant d'activer la mise en cache automatique globalement ou pour un utilisateur spécifié, respectivement. Les préférences de mise en cache au niveau de l'utilisateur sont prioritaires sur les préférences globales de mise en cache. Utilisez les paramètres PROPERTY_NAME et PROPERTY_VALUE de ces procédures pour définir des limites de quota d'espace pour les caches de table externes.

En fonction du quota d'espace spécifié, Oracle crée les caches et tente d'alimenter l'ensemble des données de la table externe dans le cache. Le processus de remplissage échoue si la taille du cache n'est pas suffisante pour prendre en charge l'ensemble des données de la table externe.

Vous pouvez également utiliser la procédure DBMS_CACHE.REFRESH pour effectuer une actualisation à la demande pour tous les caches d'un utilisateur spécifié.

A chaque cycle d'actualisation, les caches non valides et les caches les moins récemment consultés sont supprimés de la base de données. Vous pouvez également utiliser la procédure DBMS_CACHE.CLEAR pour supprimer tous les caches d'un utilisateur donné.