Discussion :

[Ikebukuro]

Hello les DBA Oracle,

Je me posais la question suivante : est-ce que Oracle peut détecter et bloquer une requête qui consomme trop de ressource car cela mets en péril l'utilisation même de la base de données?
Par exemple une jointure oubliée entre deux tables de 1 000 000 d'enregistrements avec un ORDER BY va générer un résultat de, houlala, je ne sais même pas combien de ligne plus un tri interminable sur le TBS temporaire! Je pense que cette requête va mettre à genoux la base!

Je pose cette question car j'avais fais le test suivant il y a quelques mois : dans un trigger AFTER INSERT sur la table T1, j'avais mis un INSERT sur T1 et donc cela devait boucler indéfiniment... Hé bien Oracle m'a tout de suite rendu la main (en deux secondes) avec un message d'erreur disant que j'avais dépassé le nombre d'appels récursifs autorisés! Alors là, chapeau, ça m'avait impressionné même si c'est normal de détecter ce genre de problème.

Et donc je me demandais si pour les requêtes trop lentes, trop gourmandes en ressources, est-ce que le même système existe?

[Pomalaix]

1)
Si vous êtes en édition standard, je ne vois guère que le mécanisme des profils, qui permettra de couper en plein vol une requête dont la consommation CPU (exprimée en centièmes de seconde) dépasse le seuil fixé.

Ici je crée un profil avec une limite de 5 secondes de CPU et 1000 lectures logiques par appel, puis je l'attribue à SCOTT.
Si l'une ou l'autre de ces limites est franchie, la requête sera annulée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
create profile prof_demo
limit
    cpu_per_call 500
    logical_reads_per_call 1000;
 
alter user scott profile prof_demo;
grant select any dictionary to scott;

La lourde requête suivante va être interrompue au bout de 5 secondes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
connect scott/tiger
set timing on
 
select count(*) from dba_objects, dba_objects;
 
ERREUR à la ligne 1 :
ora-02393: limite d'appel sur utilisation cpu dépassée
 
ecoulé : 00 :00 :05.01

Cette technique a au moins 2 inconvénients :
- la requête va commencer à s'exécuter, pour être annulée une fois la limite atteinte, ce qui peut être frustrant pour l'utilisateur
- les limites de type xxx_PER_CALL s'appliquent pour chaque "appel". La doc précise que les phases PARSE, EXECUTE et FETCH d'une instruction SQL constituent chacune un appel. Ce qu'elle ne dit pas, c'est qu'un bloc PL/SQL avec tout ce qu'il contient est considéré comme un seul appel.
La difficulté, c'est donc qu'il faut réussir à trouver une limite qui soit pertinente tout autant pour une requête individuelle que pour un bloc PL/SQL complet...

Dans l'exemple suivant, le bloc PL/SQL va finir par être interrompu à cause de la limite de 1000 lectures logiques.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
set serveroutput on
declare
    cumul number;
begin
    for i in 1..10 loop
        dbms_output.put_line('passage N° ' || i);
    select sum(object_id) into cumul
    from dba_objects;
    end loop;
end;
/

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
passage N° 1
passage N° 2
declare
*
ERREUR à la ligne 1 :
ORA-02395: limite d'appel sur utilisation IO dépassée
ORA-06512: à ligne 6
 
Ecoulé : 00 :00 :00.19

Mais en dehors d'un bloc PL/SQL, la même requête ( select sum(object_id) from dba_objects ) pourrait être exécutée autant de fois qu'on le souhaite, car chaque exécution individuelle reste en dessous des limites fixées, c'est à dire moins de 1000 blocs dans mon environnement.


2)
En édition Entreprise, le gestionnaire de ressources donne plus de souplesse, car on peut se débarrasser du premier inconvénient que j'indiquais plus haut.
On pourra notamment interdire l'exécution d'une requête dont le temps d'exécution estimé dépasse un seuil fixé : elle ne démarrera même pas.

Par exemple, en jouant sur une directive avec une limite MAX_EST_EXEC_TIME fixée à 10 secondes de temps CPU :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select count(*) from dba_objects, dba_users
 
ERREUR à la ligne 1 :
ORA-07455: temps d'exécution prévu (105 sec.) hors limites (10 sec.)

Pour plus d'information, voir DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE.

[Ikebukuro]

Merci beaucoup pour la réponse Pomalaix, je vais explorer tout ça à tête reposée.