1. 1 – Introduction

Le contrôle d’exécution est un sujet sensible et récurrent dans les bases de données.
Quand on est dans une situation où une requête s’emballe, exécutée par un grand nombre de sessions simultanément, et consommant ainsi une grande partie des ressources serveur, il y a danger.
Si dans ce type de situation on n’a pas la main sur le développement (ou qu’un correctif ne saurait être disponible dans un temps raisonnable), que l’indexation ou le profiling SQL ne change rien, et que le kill de session ne fait qu’initier un retry (le serveur applicatif ré-exécute inlassablement la requête à chaque plantage), il faut trouver une solution efficace à effet immédiat.

Cet article décrit comment utiliser l’une des fonctionnalités d’ORADEBUG sous SQL*Plus (suspend) ainsi que la façon d’implémenter un mécanisme de type « temps réel » avec le package « DBMS_ALERT » qui permet de piloter un processus externe (ici sqlplus).
A noter qu’il aurait été possible d’utiliser « EXTJOB », mais on n’aurait pas eu à parler de « DBMS_ALERT ».

 

1. 2 – Principes de fonctionnement

La première étape consiste à identifier la requête problématique par son « SQL_ID ». Les sources sont diverses et variées : Cloud Control, V$SESSION, sondes tiers.
Ensuite ce « SQL_ID » est référencé dans une table qui sera créée à cet effet.
Lorsque le démon « Database », constitué d’une procédure PL/SQL, détecte qu’une session exécute une des requêtes référencées, il envoie une alerte à un second démon « OS » constitué, lui, d’un script shell + SQL.
Dès réception du message, le script met en pause la session.
Une option Garbadge Collector permet de faire un kill de sessions précédemment suspendues depuis un temps prédéfini.

L’implémentation s’effectue donc à deux niveaux du fait que la détection est faite au sein de la base de données mais que le contrôle sur une session est fait en dehors de la base de données via SQL*Plus et ORADEBUG.

 

1. 3 – Implémentation

• Création des tables
  

create table system.sql_freezing
  ( sql_id varchar2(13)
  , kill_delay number(*,0)
  , enabled varchar2(5) default 'FALSE'
  , constraint pk_sql_freeze primary key (sql_id) using index tablespace sysaux
  , constraint ck_enabled check (enabled in ('TRUE','FALSE'))
)
tablespace sysaux;

-> Référencement des requêtes à contrôler
-> Un enregistrement par « SQL_ID »
-> Durée « KILL_DELAY » au bout de laquelle la session sera tuée (NULL = jamais tuée)
-> Contrôle activé ou désactivé via « ENABLED »

create table system.sql_frozen
  ( sid number(*,0)
  , serial# number(*,0)
  , pid number(*,0)
  , sql_id varchar2(13)
  , frozen_date date default sysdate
  , kill_date date default null
  , constraint pk_sql_frozen primary key (sid, serial#) using index tablespace sysaux
  , constraint fk_sql_frozen_1 foreign key (sql_id) references system.sql_freezing(sql_id) on delete cascade
)
tablespace sysaux;

-> Suivi des sessions contrôlées
-> Un enregistrement inséré par le démon lorsqu’il repère une exécution à suspendre
-> « SID », « SERIAL# » et « PID » de la session
-> « SQL_ID » détecté
-> Date/heure de suspension de la session « FROZEN_DATE »
-> Date/heure de destruction de la session « KILL_DATE »

create table system.sql_freeze_message
( message_date  date default sysdate not null
, message       varchar2(4000) not null
)
tablespace sysaux;

-> Suivi des erreurs

• Création du Directory

create or replace directory sql_freeze_dir as '/tmp';

-> Répertoire où le démon « Database » génère les scripts pour le démon « OS »

• Attribution des droits
  

grant read,write on directory sql_freeze_dir to system;
grant select on v_$session to system;
grant select on v_$process to system;
grant select on dbms_alert_info to system;
grant execute on dbms_lock to system;
grant alter system to system;

-> Le propriétaire de la procédure PL/SQL doit être en capacité d’écrire des fichiers dans le répertoire « /tmp », de lire le contenu des vues système « V$SESSION », « V$PROCESS » et « DBMS_ALERT_INFO », d’exécuter les procédures du package « DBMS_LOCK », et d’exécuter la commande « ALTER SYSTEM »

• Création du Package PL/SQL

create or replace package system.sql_freeze
is
/*-----------------------------------------------------------------------------+
| Description :                                                                |
| Mise en suspension de requêtes identifiées par leur SQL_ID                   |
| Optionnellement, kill de la session après un délai paramétrable              |
+------------------------------------------------------------------------------+
| Usage :                                                                      |
| - Dans la BDD, connecte en SYSTEM, creer un job.                             |
| Exemple (sqlplus) :                                                          |
| var job number                                                               |
| exec dbms_job.submit(:job, 'sql_freeze.run')                                 |
| - Cote OS, connecté en oracle, créer un cron.                                |
| Exemple :                                                                    |
| /usr/local/bin/sql_freeze.sh <SID>                                           |
| - <SID> : le nom d'instance, doit exister dans oratab                        |
| - Référencer les requêtes à contrôler.                                       |
| Exemple (sqlplus) :                                                          |
| insert into system.sql_freezing                                              |
| (sql_id)                                                                     |
| values 'b8f77jgywn8f5');                                                     |
| commit;                                                                      |
| - Pour arreter, executer :                                                   |
| sql_freeze.stop                                                              |
| - Options :                                                                  |
| - La colonne sql_freezing.kill_delay peut indiquer quand la session          |
| exécutant la requête sera stopée (delai en secondes).                        |
| - La colonne sql_freezing.enabled suspend le contrôle si valorisée a FALSE   |
+-----------------------------------------------------------------------------*/
  procedure run;
  procedure stop;
  procedure wait;
end;
/
create or replace package body system.sql_freeze
is
  calert_name constant sys.dbms_alert_info.name%type := 'SQL_FREEZE';
  calert_continue constant sys.dbms_alert_info.name%type := 'CONTINUE';
  calert_stop constant sys.dbms_alert_info.name%type := 'STOP';
  calert_do constant sys.dbms_alert_info.name%type := 'DO';
  calert_next constant sys.dbms_alert_info.name%type := 'NEXT';

  procedure run
  is
    lcount binary_integer;
    lmessage sys.dbms_alert_info.message%type;
    lstatus integer;
    lfile utl_file.file_type;
  begin
    delete sql_frozen;
    commit;
    dbms_alert.register(calert_name);
    -- Attente message
    dbms_alert.waitone(calert_name, lmessage, lstatus);
    while (lmessage != calert_stop) loop
      begin
        utl_file.fclose_all;
        lfile := utl_file.fopen('SQL_FREEZE_DIR', 'sql_freeze.sql', 'w');
        lcount := 0;
        -- Quelles sessions à suspendre ?
        for lf in (select s.sid, s.serial#, p.pid, f.sql_id, f.kill_delay
                     from sql_freezing f
                     inner join v$session s
                       on f.sql_id = s.sql_id
                     inner join v$process p
                       on s.paddr = p.addr
                    where not exists (select 1
                                        from sql_frozen r
                                       where r.sql_id = s.sql_id
                                         and r.sid = s.sid
                                         and r.serial# = s.serial#)
                      and f.enabled = 'TRUE') loop
          insert into sql_frozen
            (sid, serial#, pid, sql_id, frozen_date, kill_date)
            values (lf.sid, lf.serial#, lf.pid, lf.sql_id, sysdate, sysdate + (lf.kill_delay / 24 / 60 / 60));
          -- Génération des commandes ORADEBUG
          -- Accroche de la session
          utl_file.put_line(lfile, 'oradebug setpid '||to_char(lf.pid));
          -- Suspension de la session
          utl_file.put_line(lfile, 'oradebug suspend');
          lcount := lcount + 1;
        end loop;
        commit;
        -- Quelles session à tuer ?
        for lf in (select f.rowid, f.sid, f.serial#, f.pid
                     from sql_frozen f
                     inner join v$session s
                       on s.sid = f.sid and f.serial# = s.serial#
                     where s.status != 'killed'
                       and f.kill_date <= sysdate) loop
          -- Génération des commandes ORADEBUG
          -- Accroche de la session
          utl_file.put_line(lfile, 'oradebug setpid '||to_char(lf.pid));
          -- Reprise d'activité de la session...
          utl_file.put_line(lfile, 'oradebug resume');
          -- ... pour la tuer
          utl_file.put_line(lfile, 'alter system kill session '''||lf.sid||','||lf.serial#||''' immediate;');
          delete sql_frozen
           where rowid = lf.rowid;
          lcount := lcount + 1;
        end loop;
        commit;
        utl_file.put_line(lfile, 'exit');
        utl_file.fclose_all;
        if (lcount > 0) then
          -- Réveil du démon OS
          dbms_alert.signal(calert_name, calert_do);
          commit;
          -- Ignorer le signal juste émis
          dbms_alert.waitone(calert_name, lmessage, lstatus);
          -- Attente fin d'exécution des commandes par le démon OS
          dbms_alert.waitone(calert_name, lmessage, lstatus);
          while (lmessage not in (calert_stop, calert_next)) loop
            dbms_alert.waitone(calert_name, lmessage, lstatus);
          end loop;
        end if;
      exception
        when others then
          lmessage := sqlerrm;
          insert into sql_freeze_message
            (message_date, message)
            values (sysdate, lmessage);
          commit;
      end;
      -- Synchronisation
      dbms_alert.waitone(calert_name, lmessage, lstatus, 1);
      if (lstatus != 0) then
        lmessage := calert_continue;
      end if;
    end loop;
    dbms_alert.remove(calert_name);
  end;

  procedure stop
  is
  begin
    dbms_alert.register(calert_name);
    dbms_alert.signal(calert_name, calert_stop);
    commit;
    dbms_alert.remove(calert_name);
  end;

  procedure wait
  is
    lmessage varchar2(1800);
    lstatus integer;
  begin
    dbms_alert.register(calert_name);
    dbms_alert.signal(calert_name, calert_next);
    commit;
    -- Ignorer le signal juste émis
    dbms_alert.waitone(calert_name, lmessage, lstatus);
    -- Synchronisation
    dbms_alert.waitone(calert_name, lmessage, lstatus);
    if (lmessage = calert_stop or lstatus > 0) then
      dbms_alert.remove(calert_name);
      raise_application_error(-20000, 'Stopped');
    end if;
  end;
end;
/

-> Procédure « RUN » de démarrage du démon
-> Procédure « END » d’arrêt des démons
-> Procédure interne « WAIT » de synchronisation
-> Explication du fonctionnement dans les commentaires

• Création du script Shell

/usr/local/bin/sql_freeze.sh :

#! /bin/bash

if [ "$1" == "" ] ; then
  echo "Syntax : $(basename $0) "
  exit 1
fi
# Sort si déjà en cours d'exécution
export PATH=$PATH:/usr/local/bin
. oraenv >/dev/null <<< $1 PID_FILE="/tmp/$(basename $0).pid" if [ -f "$PID_FILE" ] ; then ps -f $(cat "$PID_FILE") > /dev/null
  if [ $? == 0 ] ; then
    exit
  fi
fi

echo $$ > "$PID_FILE"
trap "rm -f \"$PID_FILE\"" exit
# Synchronisation avec le démon Database
sqlplus -S / as sysdba <<EOF
whenever sqlerror exit 1
exec system.sql_freeze.wait
EOF
# Booucle de traitement. Sortie sur message STOP
rc=$?
while [ $rc == 0 ] ; do
  # Exécution des commandes générées par le démon Database
  sqlplus -S / as sysdba @/tmp/sql_freeze.sql
  # Attente du message suivant
  sqlplus -S / as sysdba <<EOF
whenever sqlerror exit 1
set echo on verif on
exec system.sql_freeze.wait
EOF
  rc=$?
done

-> L’unique argument du script est le SID de l’instance de la base de données qui doit être présent dans le fichier « /etc/oratab »
-> Ce script ne s’arrête que sur exécution de la procédure « SQL_FREEZE.STOP »
-> Explication du fonctionnement dans les commentaires

 

1. 4 – Mise en oeuvre

Côté Shell, le script « /usr/local/bin/sql_freeze.sh » doit être exécuté en « Background », ou via un crontab si l’on souhaite éviter toute désactivation (arrêt) intenpestif.
Exemple :

# nohup /usr/local/bin/sql_freeze.sh MYDB &

Côté base de données, la procédure « SQL_FREEZE.RUN » doit être exécutée via un Job à exécution unique, ou récurrent si l’on souhaite éviter toute désactivation (arrêt) intenpestif.
Il faut ensuite insérer les informations de contrôle.
Exemple :

SQL> var job number
SQL> exec dbms_job.submit(:job, 'system.sql_freeze.run;')
SQL> insert into system.sql_freezing
values ('b8f77jgywn8f5',NULL,'TRUE');
SQL> commit;

Chaque session suspendue apparaitra dans la table sql_frozen :

SQL> select * from system.sql_frozen;

 

• 5 – Conclusion

Lorsque les requêtes comportent des « Bind variables » et que les plans d’exécution varient, le « SQL_ID » peut être remplacé par le « PLAN_HASH_VALUE », mais qui n’est pas directement récupérable depuis « V$SESSION » : à vos claviers pour adapter le code !

Une fois encore, et article est un prétexte pour commencer à décrire des fonctionnalités Oracle avancées, ici ORADEBUG et une programmation de type temps réel avec le système de messagerie DBMS_ALERT.

Enjoy Oracle