Un des thèmes qui revient souvent concernant les bases de données est l’optimisation des requêtes SQL. La réponse générale à apporter à la question « comment faire pour que ma requête prenne moins de temps » est « ca dépend ». Pour sortir du flou, je vais vous proposer sur ce blog quelques exemples concrets. Pour commencer, j’ai envie de parler d’un problème que j’ai rencontré récemment : améliorer le temps d’exécution d’une grosse mise à jour. Prenez ainsi 2 tables importantes table1 et table2 et tentez de mettre à jour une colonne de table1 à partir d’une colonne de table2. Par exemple comme requête :
update table1 set col3 = (select table2.col4 from table2 where table1.col2 = table2.col3)
Le plan d’exécution est assez simple à lire et donc difficile à optimiser :
| Id | Operation |Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | 8905K| 144M| 25265 (3)| 00:05:04 | | |
| 1 | UPDATE | | | | | | |
| 2 | PARTITION LIST ALL | 8905K| 144M| 25265 (3)| 00:05:04 | 1 | 6 |
| 3 | TABLE ACCESS FULL | 8905K| 144M| 25265 (3)| 00:05:04 | 1 | 6 |
| 4 | TABLE ACCESS BY INDEX ROWID| 1 | 21 | 1 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | 1 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------
Dans ce cas là (la version de la base est la 10.2.0.4), en raison de la taille conséquente des tables, l’update est tellement long que je ne sais même pas le temps qu’il aurait fallu pour le finir.
Pour aider l’optimiseur, on peut réécrire et simplifier la requête dans un bloc PL/SQL en utilisant tout simplement un curseur :
declare
type rct is ref cursor ;
cur rct ;
l_idp integer := -13 ;
l_rowid rowid ;
begin
open cur for select table2.col4, table1.rowid
from table1
join table2 on table1.col2 = table2.col3 ;
loop
fetch cur into l_idp, l_rowid ;
exit when cur%notfound ;
update table1 set col3=l_idp where rowid=l_rowid ;
end loop ;
close cur ;
end ;
On passe à environ 1 minute d’exécution.
A bientôt pour un nouveau cas.
14 réflexions sur “Optimisation SQL – 1”
Ping : Optimisation SQL – 1 Ter : On passe à 2 ? « EASYTEAM LE BLOG
Bonjour,
Un bon « merge » à la place de l’update (ou du PL/SQL) aurait peut-etre pu faire l’affaire.
Cdlt,
Bonjour,
peut-être, j’ai pas testé. j’ai quand même des doutes avec le merge car il n’utilise pas les rowid (sauf contre exemple que je n’ai pas en tête).
Depuis l’écriture de cet article, je me suis retrouvé dans quelques cas similaires avec des deletes ou des updates qui étaient plus performants en les passant en rowid.
Ce qu’il y a de fun c’est que j’ai remarqué que OWB a un mode row qui fait la même chose que dans cet article, et j’y vois une certaine forme de justification, en outre de mes expériences récentes.
Je pense qu’en travaillant en rowid, il y a moins de verrous en jeu, mais je n’arrive pas a tracer suffisamment bas-niveau les requêtes pour valider mon hypothèse.
Ping : Optimisation SQL – 1 : de la sueur et du sang « EASYTEAM Le BLOG
Cet exemple est très intéressant à au moins 3 titre.
Ne vous faîtes pas de fausses idées: Ces 2 commandes sont sémantiquement différentes. Ce qui revient à dire que faire son marché chez A est plus rapide que chez B. Pire, s’il y a plusieurs lignes dans table2 correspondant à une ligne de table1 à travers la jointure, le résultat de la mise à jour dans le bloc PL/SQL risque de dépendre du plan du select
au vu des différents commentaire cela mériterais peut être un erratum ou des explications commentaires?
Cdt.
Bonjour,
étant en congés récemment, je n’ai pas pu répondre rapidement à vos commentaires, donc voici un flot groupé :
> j’ai l’habitude de remplir une collection et ensuite de l’inserer/updater dans la base
> avec une boucle FORALL. C’est beaucoup plus efficace qu’une boucle avec
> un traitement SQL itératif comme vous le faites dans votre exemple.
je suis d’accord mais l’amélioration des performances grâce aux « collections/bulk-collect/forall » n’est possible que parce que la base met en mémoire les résultats pour éviter de faire des allers-retours. Ceci peut entraîner un remplissage complet de la mémoire quand on manipule beaucoup de données. cela n’est donc pas forcément possible dans tous les cas (à moins d’avoir des tera-octets de mémoire), et notamment celui de cet article.
> n’y aurait t’il tout simplement pas un probléme avec votre requête sql qui ne
> correspond pas exactement à ce que vous fait dans votre bloc SQL.
ah bon, je n’ai pas l’impression. ce que j’ai oublié de dire je pense et qui peut nuire à la compression de l’article c’est que les données dans table1.col3 et table2.col4 sont uniques.
> Pour finir, je serai interessé de voir le plan d’execution de la sous requete :
désolé, les données sur lesquelles étaient exécutées la requête ne sont plus disponibles.
de mémoire, il est ultra simple (jointure standard). c’est ce qui m’a paru le plus surprenant dans cette histoire : les plans sont simples et justes (mis à part le update qui est plutot obscur). Le select s’exécute sans problème très rapidement, mais le update explose.
J’ai essayé de reproduire le cas avec des données autogénérées pour pouvoir détailler plus amplement l’expérience, mais pour l’instant, c’est plutot un échec. comme dit, en théorie la théorie et la pratique sont la même chose, en pratique non.
J’espère juste que cet article pourra aider quelques personnes ayant des problèmes d’update (ce qui arrive assez souvent). Si je réussis à resimuler le problème, je vous tiendrai au courrant.
Cordialement,
Bonjour,
les deux méthodes de mise à jour me semblent correctent selon leur contexte. Je m’explique :
l’update sera plus efficace si la selectivité est forte cad s’il y a plusieurs table1.col2 = table2.col3 ..et donc ce cas il faut eviter de passer par les indexes par l’usage d’un hint par exemple ou encore les desactiver si possible.c’est aussi l’interet d’avoir des requetes ensembliste et qui demandent plus de memory et moins d’accès séquentiel.
le bloc pl quant à lui sera efficace en cas de selectivité faible…
pareil pour la requete de YC quand la selectivité est faible:
update table1 set col3 = (select table2.col4 from table2 where table1.col2 = table2.col3)
where col2 in (select col3 from table2) ;
cela fontionnera Better!
et pour les bons (nouveaux) reflexes, l’utilisation du for all dans les bloc pl/sql est fortement souhaitée pour éviter les aller retour entre la couche sql et la couche PL.
Pour finir, je serai interessé de voir le plan d’execution de la sous requete :
select table2.col4
from table2, Table1
where table1.col2 = table2.col3
Merci
Bonsoir,
Je suis de l’avis de YC. Si le nombre de mises à jour est égal dans les 2 cas, le problème est ailleurs car dans le PL/SQL, on va finalement faire un full sur table1 mais par rowid: ce qui ne peut pas être plus efficace qu’un bon vieux full scan(multi block read).
De plus, pour un update de masse, un PL écrit de cette façon ne correspond pas vraiment aux bonnes pratiques mais c’était probablement pour simplifier le propos.
Cdt
pour être un peux plus précis voilà un petit test qui illustre mon propos :
j’insère 100000 lignes dans la table1 et seulement 50000 dans la table2.
Il y aura donc seulement 50000 lignes qui respecteront le critère de jointure.
insert into table1 select level,level from dual connect by level<100000;
commit;
100000 ligne(s) creee(s).
insert into table2 select level,level from dual where mod(level,2)=0 connect by level<100000;
50000 ligne(s) creee(s).
commit;
update table1 set col3 = (select table2.col4 from table2 where table1.col2 = table2.col3);
100000 ligne(s) mise(s) a jour
Toutes les lignes sont updatées dans votre cas même si la jointure n'est pas respecté.
En effet dans ce cas oracle update la colonne avec la valeur nulle.
update table1 set col3 = (select table2.col4 from table2 where table1.col2 = table2.col3)
where col2 in (select col3 from table2) ;
50000 ligne(s) mise(s) a jour
avec cette requête les valeurs sont seulement updatées lorsque la condition est respectée.
Lorsque l'on exécute la première requête l'optimiseur doit réaliser un full scan de la table t1 d'ou l'écart de performance je pense.
il fallait comprendre « qui ne correspond pas exactement à ce que vous fait dans votre bloc PL/SQL » désolé
bonjour ,
n’y aurait t’il tout simplement pas un probléme avec votre requête sql qui ne correspond pas exactement à ce que vous fait dans votre bloc SQL.
Ne fallait t’il pas écrire votre requête de la facon suivant :
update table1 set col3 = (select table2.col4 from table2 where table1.col2 = table2.col3)
where col2 in (select col3 from table2) ;
Cordialement.
Bonjour,
Personnellement pour ce genre de traitement, j’ai l’habitude de remplir une collection et ensuite de l’inserer/updater dans la base avec une boucle FORALL. C’est beaucoup plus efficace qu’une boucle avec un traitement SQL itératif comme vous le faites dans votre exemple.
Plus efficace, pour la simple et bonne raison que le moteur PL/SQL donne complètement la main au moteur SQL sans faire d’aller-retour consommateur comme votre exemple 😉 J’ai vu des fois des traitements diminuer de 5 leur temps d’exécution avec les collections.
Cordialement,
Guireg CAPITAINE
Bonjour,
Peux tu nous montrer en quoi cela consiste ? Je pense que tu as éveillé l’intérêt de plus d’un lecteur 🙂
Merci
Phil
Les commentaires sont fermés.