Décharger les requêtes vers les clones actualisables des membres

Lorsque vous disposez de charges globales de lecture lourdes, où une quantité relativement faible de données est analysée par plusieurs requêtes, vous pouvez décharger les requêtes (lectures) d'un leader de pool élastique ou d'un membre de pool élastique vers des clones actualisables.

A propos du déchargement des requêtes

Fournit des informations sur l'utilisation du déchargement de requête avec un pool élastique et décrit les fonctionnalités de déchargement de requête.

Le déchargement des requêtes de pool élastique offre des avantages en termes de performances en permettant à un ou plusieurs clones actualisables de gérer les requêtes pour un leader de pool élastique ou pour un membre de pool élastique. Cette fonction vous permet également d'ajouter des clones actualisables pour répondre à l'augmentation de la demande de requête (lecture). Le déchargement des requêtes permet à votre application d'évoluer horizontalement, où vous pouvez ajouter des clones actualisables pour maintenir les performances globales du système selon vos besoins afin de satisfaire le volume de vos requêtes.

Lorsque le déchargement des requêtes est activé, les requêtes sont soumises au leader du pool élastique ou à un membre du pool élastique et un ou plusieurs clones actualisables disponibles deviennent des candidats pour le déchargement des requêtes. En outre, lorsque davantage de clones actualisables sont ajoutés, le déchargement des requêtes s'ajuste dynamiquement pour utiliser les nouvelles ressources.

Un cas d'emploi pour le déchargement de requête est d'activer la fonctionnalité pendant les heures de pointe pour retirer le chargement de l'amorce du pool élastique ou d'un membre du pool élastique. Pendant les heures d'inactivité, vous pouvez désactiver le déchargement des requêtes pour effectuer des opérations de maintenance telles que l'actualisation des clones actualisables.

Par défaut, le déchargement des requêtes prend en compte les requêtes de n'importe quelle session. Vous pouvez également décharger des requêtes d'une liste de sessions que vous indiquez par nom de module ou d'action.

La figure suivante illustre le déchargement des requêtes de l'amorce du pool élastique.



La figure suivante illustre le déchargement des requêtes d'un membre de pool élastique.



Les données des clones actualisables sont à jour en fonction de l'heure de la dernière actualisation pour chaque clone actualisable. Cela signifie que lorsque le déchargement d'interrogation est activé, vous effectuez toutes les opérations LDD, LMD et PL/SQL sur le leader du pool élastique ou sur le membre du pool élastique qui décharge les interrogations. Une fois qu'un clone actualisable est actualisé, les modifications sont répercutées sur le clone actualisable.

Pour plus d'informations, reportez-vous à Utilisation de clones actualisables avec Autonomous Database.

Fonctionnalités de déchargement de requête

  • Ajout dynamique : les clones actualisables peuvent être ajoutés en tant que membres du pool élastique à tout moment. Le déchargement des requêtes est ajusté dynamiquement pour utiliser de nouveaux membres.

  • Suppression dynamique : les clones actualisables peuvent être supprimés en tant que membres du pool élastique à tout moment. Le déchargement des requêtes s'ajuste de manière dynamique pour arrêter le déchargement des requêtes vers un clone actualisable qui a été supprimé du pool élastique.

  • Déchargement persistant basé sur une session : le déchargement de requête est persistant dans une session, ce qui signifie que si une requête d'une session a été déchargée vers un clone actualisable particulier, le déchargement de requête utilise le même clone actualisable pour décharger les requêtes suivantes dans la même session.

  • Mappage de service un-à-un : il existe un mappage un-à-un entre le service utilisé dans l'amorce de pool élastique ou le membre de pool élastique par la requête d'origine et le service utilisé dans un clone actualisable pour toute requête déchargée. Par exemple, si une session est connectée au service MEDIUM, le déchargement des requêtes pour cette session utilise également le service MEDIUM sur le clone actualisable.

  • Déterminer si la session est déchargée : à l'aide d'une requête SYS_CONTEXT, vous pouvez déterminer si une session s'exécute sur l'amorce ou le membre du pool élastique ou est déchargée vers un clone actualisable.

  • Déchargement de requête à partir de PL/SQL : les requêtes sont déchargées vers un clone actualisable même si elles sont imbriquées dans PL/SQL. Le code PL/SQL peut être des procédures stockées, des fonctions, des packages ou des blocs anonymes.

  • Déchargement de requête à partir d'un travail de planificateur : les requêtes à partir d'actions de travail Oracle Scheduler sont déchargées. Les requêtes de travail Oracle Scheduler sont déchargées, qu'elles soient exécutées en avant-plan ou en arrière-plan.

DBA_PROXY_SQL Vues

Utilisez les vues DBA_PROXY_SQL DBA_PROXY_SQL_ACTIONS et DBA_PROXY_SQL_MODULES pour afficher la liste des modules ou des actions configurés pour le déchargement de requête. Vous devez interroger ces vues à partir d'une session qui n'est pas activée pour le déchargement des requêtes.

Activer le déchargement de requête

Explique comment activer le déchargement des requêtes pour un leader de pool élastique ou pour un membre de pool élastique.

Les conditions requises pour activer le déchargement de requête sont les suivantes :

  • Vous pouvez activer le déchargement des requêtes pour un leader de pool élastique ou pour un membre de pool élastique sans clone actualisable. Après avoir activé le déchargement des requêtes, vous pouvez ajouter des clones actualisables et la fonction de déchargement des requêtes s'ajuste dynamiquement pour utiliser les clones actualisables.

  • Un clone actualisable candidat pour le déchargement de requête doit :

    • Faites en sorte que l'amorce du pool élastique soit sa base de données source et qu'elle se trouve dans la même région que l'amorce du pool élastique.

      ou

      Avoir un membre de pool élastique comme base de données source et être dans la même région que le membre de pool élastique.

    • Soyez un membre élastique du pool.

Pour activer le déchargement de requête, procédez comme suit :

  1. Vérifiez que l'instance Autonomous Database est un leader de pool élastique ou un membre de pool élastique :
    1. Utilisez l'interrogation suivante pour vérifier qu'une instance est un leader de pool élastique :
      SELECT sys_context('userenv', 'is_elastic_pool_leader') FROM DUAL;

      Cette requête doit renvoyer YES.

    2. Utilisez la requête suivante pour vérifier qu'une instance est membre d'un pool élastique :
      SELECT sys_context('userenv', 'is_elastic_pool_member') FROM DUAL;

      Cette requête doit renvoyer YES.

    Remarque

    La valeur sys_context peut prendre jusqu'à 15 minutes pour refléter la valeur en cours si le pool élastique a été récemment créé ou modifié.
  2. Activer le déchargement de requête.

    Il existe deux options : vous pouvez activer le déchargement de requête pour les requêtes de n'importe quelle session ou limiter le déchargement de requête aux sessions que vous indiquez par nom de module ou d'action.

    • Afin d'activer le déchargement de requête pour toutes les sessions, exécutez DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD sans paramètre. Exemples :

      EXEC DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD;
    • Afin d'activer le déchargement de requête pour des sessions spécifiques par nom de module ou nom d'action, exécutez DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD avec les paramètres module_name ou action_name.

      Pour plus d'informations, reportez-vous à Activer le déchargement de requête pour les actions ou les modules nommés.

Lorsque le déchargement de requête est activé pour une session, vous pouvez trouver le nom du clone actualisable vers lequel les requêtes sont déchargées. Par exemple, à partir de la session, exécutez la requête suivante :

SELECT sys_context('userenv', 'con_name') FROM DUAL;

Si les requêtes ne sont pas déchargées vers un clone actualisable, cette requête affiche le nom de l'amorce du pool élastique (ou du membre du pool élastique qui n'est pas un clone actualisable).

Pour plus d'informations, reportez-vous à SYS_CONTEXT.

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

Activer le déchargement de requête pour les modules ou les actions nommés

Explique comment activer le déchargement des requêtes pour les sessions avec des modules ou des actions nommés.

Pour activer le déchargement de requête pour des sessions spécifiques par nom de module ou d'action :

  1. Vérifiez que l'instance est un leader de pool élastique ou un membre de pool élastique.
    1. Utilisez l'interrogation suivante pour vérifier qu'une instance est un leader de pool élastique :
      SELECT sys_context('userenv', 'is_elastic_pool_leader') FROM DUAL;

      Cette requête doit renvoyer YES.

    2. Utilisez la requête suivante pour vérifier qu'une instance est membre d'un pool élastique :
      SELECT sys_context('userenv', 'is_elastic_pool_member') FROM DUAL;

      Cette requête doit renvoyer YES.

    Remarque

    La valeur sys_context peut prendre jusqu'à 15 minutes pour refléter la valeur en cours si le pool élastique a été récemment créé ou modifié.
  2. Utilisez les routines SET_ACTION ou SET_MODULE dans DBMS_APPLICATION_INFO pour définir le nom du module et/ou le nom de l'action dans la session en cours.

    Exemple

    CREATE or replace PROCEDURE add_employee( 
      name VARCHAR2, 
      salary NUMBER, 
      manager NUMBER, 
      title VARCHAR2, 
      commission NUMBER, 
      department NUMBER) AS 
    BEGIN 
      DBMS_APPLICATION_INFO.SET_MODULE( 
        module_name => 'add_employee', 
        action_name => 'insert into emp'); 
      INSERT INTO emp 
        (ename, empno, sal, mgr, job, hiredate, comm, deptno) 
        VALUES (name, emp_seq.nextval, salary, manager, title, SYSDATE, 
                commission, department); 
      DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
    END;

    Pour plus d'informations, reportez-vous à DBMS_APPLICATION_INFO.

  3. Activez le déchargement des requêtes et spécifiez les sessions admissibles par nom de module ou par nom d'action (ou par nom de module et nom d'action).

    Exemples :

    DECLARE 
       mod_values clob := to_clob('["mod1", "mod2"]');
       act_values clob := to_clob('["act1", "act2"]');
    BEGIN 
       DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD(
          module_name => mod_values,                                          
          action_name => act_values);
    END;
    /

Lorsque le déchargement de requête est activé pour une session, à partir de la session, vous pouvez trouver le nom du clone actualisable vers lequel les requêtes sont déchargées. Exemples :

SELECT sys_context('userenv', 'con_name') FROM DUAL;

Si les requêtes ne sont pas déchargées vers un clone actualisable, cette requête affiche le nom de l'amorce du pool élastique (ou du membre du pool élastique qui n'est pas un clone actualisable).

Pour plus d'informations, reportez-vous à SYS_CONTEXT.

Les vues DBA_PROXY_SQL_ACTIONS et DBA_PROXY_SQL_MODULES affichent la liste des modules ou des actions configurés pour le déchargement de requête. Vous devez interroger ces vues à partir d'une session qui n'est pas activée pour le déchargement des requêtes. Pour plus d'informations, reportez-vous à DBA_PROXY_SQL Vues.

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

Désactiver le déchargement des requêtes

Décrit comment désactiver le déchargement des requêtes pour un leader de pool élastique ou pour un membre de pool élastique.

Sur l'instance qui décharge les requêtes, désactivez le déchargement des requêtes.

Exemples :

EXEC DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOAD;

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

Déchargement de requête à partir de PL/SQL

Lorsque le déchargement des requêtes est activé, les requêtes sont déchargées vers des clones actualisables même si elles sont imbriquées dans du code PL/SQL. Le code PL/SQL peut être des procédures stockées, des fonctions, des packages ou des blocs anonymes.

Par exemple, vous pouvez montrer qu'une requête est déchargée vers un clone actualisable lorsque vous activez le déchargement de requête pour un module et une action spécifiques. Dans cet exemple, supposons que les données du leader ont été mises à jour et que le clone actualisable n'a pas encore été mis à jour (les données du clone actualisable sont donc obsolètes et différentes). Dans cet exemple, avec des valeurs différentes sur l'amorce du pool élastique et le clone actualisable, vous pouvez voir quand les données proviennent de l'amorce ou du clone actualisable.

  1. Activez le déchargement des requêtes et spécifiez les sessions admissibles par nom de module et nom d'action. Par exemple, 'mod1', 'act1'.

    DECLARE 
       mod_values clob := to_clob('["mod1"]');
       act_values clob := to_clob('["act1"]');
    BEGIN 
       DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD(
          module_name => mod_values,                                          
          action_name => act_values);
    END;
    /
  2. Définissez la fonction f1 pour tester le déchargement des requêtes.

    SQL> create or replace function f1 (n number)
      2  return number
      3  as
      4    l_cnt number;
      5  begin
      6    select sum(c1) into l_cnt from u2.tab1;
      7    return l_cnt;
      8  end;
      9  /
     
    Function created.
  3. Exécutez la fonction f1. La requête avec la fonction f1 s'exécute sur l'amorce de pool élastique (ou sur un membre de pool élastique pour lequel la requête de déchargement est activée). Cette session s'exécute avec des noms de module et d'action non spécifiés qui ne correspondent pas à ceux spécifiés pour le déchargement de requête à l'étape 1.

    SQL> -- expected to fetch from Elastic Pool Leader and returns value 40
    SQL> select f1(3) from dual;
         F1(3)
    ----------
            40
     
    1 row selected.
  4. Indiquez le nom de module et le nom d'action de la session pour qu'ils correspondent aux noms indiqués à l'étape 1. Les requêtes de cette session peuvent désormais être déchargées vers un clone actualisable.

    SQL> exec dbms_application_info.set_module('mod1', 'act1');
     
    PL/SQL procedure successfully completed.
  5. Exécutez à nouveau la fonction f1. Dans ce cas, le nom du module et le nom de l'action correspondent aux noms spécifiés à l'étape 1. La requête de la fonction f1 est déchargée vers un clone actualisable.

    SQL> -- Expected to fetch from Refreshable Clone and returns value 10
    SQL> select f1(3) from dual;
     
         F1(3)
    ----------
            10
     
    1 row selected.
  6. Indiquez des noms de module et d'action autres que mod1 et act1. Les requêtes de cette session ne peuvent plus être déchargées vers un clone actualisable, car les noms de module et d'action ne correspondent pas à ceux spécifiés à l'étape 1.

    SQL> exec dbms_application_info.set_module('random', 'random');
     
    PL/SQL procedure successfully completed.
  7. Exécutez la requête avec la fonction f1. Etant donné que le nom de module et d'action de cette session ne correspond pas à ceux définis à l'étape 1, cette requête n'est pas déchargée et s'exécute sur le leader du pool élastique.

    SQL> -- expected to fetch from Elastic Pool Leader and returns value 40
    SQL> select f1(3) from dual;
         F1(3)
    ----------
            40
     
    1 row selected.

Déchargement de requête à partir d'un travail de planificateur

Lorsque le déchargement des requêtes est activé, les requêtes d'une action de travail Oracle Scheduler sont déchargées vers des clones actualisables. Les requêtes sont déchargées, que le travail s'exécute au premier plan ou en arrière-plan.

Par exemple, comme dans l'exemple précédent pour PL/SQL, vous pouvez montrer qu'une requête est déchargée vers un clone actualisable lorsque vous activez le déchargement de requête pour un travail Oracle Scheduler. Dans cet exemple, supposons que les données du leader ont été mises à jour et que le clone actualisable n'a pas encore été mis à jour (les données du clone actualisable sont donc obsolètes et différentes). Dans cet exemple, avec des valeurs différentes sur l'amorce du pool élastique et le clone actualisable, vous pouvez voir quand les données proviennent de l'amorce ou du clone actualisable.

SQL> create or replace procedure sproc1 (n number)
  2  as
  3    l_cnt number;
  4  begin
  5    select sum(c1) into l_cnt from u2.tab1;
  6    dbms_output.put_line('l_cnt is ' || l_cnt);
  7  end;
  8  /
 
Procedure created.

Offload Queries from Elastic Pool Leader to Member Refreshable Clones

SQL> create or replace procedure sproc1 (n number)
  2  as
  3    l_cnt number;
  4  begin
  5    select sum(c1) into l_cnt from u2.tab1;
  6    dbms_output.put_line('l_cnt is ' || l_cnt);
  7  end;
  8  /
 
Procedure created.
 
SQL>
SQL> BEGIN
  2    dbms_scheduler.create_job(job_name    => 'PROXYTEST1',
  3                              job_type    => 'PLSQL_BLOCK',
  4                              job_action  => 'BEGIN sproc1(3); END;',
  5                              enabled     => FALSE);
  6  END;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> -- Job runs in the foreground in the current session
SQL> -- expected to fetch from Leader: 40
SQL> exec dbms_scheduler.RUN_JOB(job_name => 'PROXYTEST1', use_current_session => true);
l_cnt is 40
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> exec dbms_proxy_sql.enable_read_only_offload;
 
PL/SQL procedure successfully completed.
 
SQL> show con_name
 
CON_NAME
------------------------------
CDB1_PDB1
SQL> 
SQL> set serveroutput on
SQL> -- Job runs in the foreground in the current session
SQL> -- expected to fetch from Refreshable Clone: 10
SQL> exec dbms_scheduler.RUN_JOB(job_name => 'PROXYTEST1', use_current_session => true);
l_cnt is 10
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> -- Job runs in the background and is expected to fetch from Refreshable Clone: 10
SQL> exec dbms_scheduler.RUN_JOB(job_name => 'PROXYTEST1', use_current_session => false);
 
PL/SQL procedure successfully completed.

Remarques relatives au déchargement des requêtes

Fournit des remarques supplémentaires sur la fonctionnalité de déchargement des requêtes.

Remarques relatives au déchargement des requêtes :

  • Ajout ou suppression d'un clone actualisable : lorsqu'un clone actualisable est ajouté en tant que membre de pool élastique, il peut y avoir un délai allant jusqu'à quinze minutes pour que l'ajout soit reflété dans l'amorce de pool élastique. Un clone actualisable nouvellement ajouté n'est pas considéré comme une cible pour les décharges de requête tant que le leader n'a pas connaissance du membre de pool élastique nouvellement ajouté.

    De même, lorsqu'un clone actualisable est retiré du pool élastique, il peut y avoir un délai de quinze minutes maximum pour que le retrait soit réfléchi dans l'amorce du pool élastique. Le clone actualisable supprimé est considéré comme une cible pour les décharges de requête jusqu'à ce que le leader sache que le membre a été supprimé du pool élastique.

  • Actualiser le clone actualisable : lors de l'actualisation d'un clone actualisable, les requêtes qui sont déchargées vers le clone actualisable peuvent être retardées.

  • Prise en charge des adresses publiques et privées : le déchargement de requête est pris en charge pour les instances sur une adresse publique et pour les instances sur une adresse privée.

  • Clone actualisable déconnecté : si un clone actualisable est déconnecté, il n'est plus admissible en tant que cible pour le déchargement de requête. Le leader du pool élastique peut attendre jusqu'à quinze minutes pour détecter qu'un clone actualisable est déconnecté et pour arrêter de décharger les requêtes.

    Pour vous assurer qu'un clone actualisable reste disponible pour le déchargement des requêtes, activez les actualisations automatiques ou actualisez manuellement périodiquement le clone actualisable.

    Pour plus d'informations, reportez-vous à Actualisation d'un clone actualisable sur Autonomous Database.

  • Nom de module sensible à la casse et nom d'action : lorsque vous déchargez des requêtes à partir d'une liste de sessions que vous indiquez par nom de module ou d'action, le nom de module et le nom d'action définis avec DBMS_APPLICATION_INFO sont sensibles à la casse. DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD respecte ce comportement sensible à la casse. Par exemple, lorsqu'une session a un nom de module en minuscules ou un nom de module en majuscules/minuscules, la casse doit correspondre dans les valeurs de paramètre lorsque vous activez le déchargement de requête avec DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD et que vous incluez les paramètres module_name ou action_name.

    Vous pouvez vérifier le nom du module et le nom de l'action pour la session en cours à l'aide de DBMS_APPLICATION_INFO.READ_MODULE :

    set serveroutput on;
    declare
      l_mod varchar2(50);
      l_act varchar2(50);
    BEGIN
    DBMS_APPLICATION_INFO.READ_MODULE(l_mod, l_act);
      DBMS_OUTPUT.PUT_LINE('l_mod: ' || l_mod);
      DBMS_OUTPUT.PUT_LINE('l_act: ' || l_act);
    END;
    /
  • Cas d'utilisation standard : un cas d'utilisation pour le déchargement de requête consiste à activer la fonctionnalité pendant les heures de pointe pour retirer le chargement de l'élément Elastic Pool Leader (ou du membre Elastic Pool où le déchargement de requête est activé). Pendant les heures d'inactivité, vous pouvez désactiver le déchargement des requêtes pour effectuer des opérations de maintenance telles que l'actualisation des clones actualisables.