2 requêtes rapides donnent une requête lente ?

Les utilisateurs n'aiment ni interrompre leur travail ni regarder le sablier
Répondre
Phil
Administrateur du site
Messages : 244
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

2 requêtes rapides donnent une requête lente ?

Message par Phil » mar. 7 nov. 2017 15:57

Merci à Guillaume pour sa question : "J’aurai un conseil à vous demander concernant une requête qui me pose problème (dans une base Oracle) :
Quand je fais ceci :

Code : Tout sélectionner

SELECT LIBELLE,
  GROUPING(LIBELLE) GROUPING_LIBELLE,
  SUM(C1_D0) C1_D0,
  SUM(C2_D0) C2_D0,
  SUM(C3_D0) C3_D0,
  SUM(C4_D0) C4_D0
FROM
  tabletest
GROUP BY rollup (LIBELLE);
Dans tabletest, je n’ai que 3 lignes, le résultat est très rapide (les 3 lignes de la table + la ligne « total »)

Code : Tout sélectionner

Par contre, si je fais ceci :
SELECT LIBELLE,
  GROUPING(LIBELLE) GROUPING_LIBELLE,
  SUM(C1_D0) C1_D0,
  SUM(C2_D0) C2_D0,
  SUM(C3_D0) C3_D0,
  SUM(C4_D0) C4_D0
FROM
  (select complexe) tmp
GROUP BY rollup (LIBELLE);
Si je lance uniquement le select complexe, oracle me ramène 3 lignes en 15s.
Si je lance la requête ci-dessus, oracle me ramène 4 lignes (les 3 précédentes + la ligne « total ») en …..3 heures….

Auriez-vous une idée pour contourner mon problème ? mots clés à rajouter dans la requête ? au pire une table temporaire (solution à éviter si possible) ?"


Réponse : cette application devant passer sur PostgreSQL, voici une solution qui fonctionnera temporairement sur Oracle et qui sera nativement OK au passage à PostgreSQL.
D'après les informations fournies, la requête (select complexe) est toujours très efficace et ramène peu de lignes. En théorie, le CBO (optimiseur) d'Oracle doit arriver tout seul à la conclusion qu'il aurait intérêt à matérialiser le jeu de de résultats mais il arrive qu'il se trompe.
Il faudrait normalement déterminer la raison qui le conduit à faire ce choix erroné. Cependant, ici, autant matérialiser systématiquement le jeu de résultats et ne pas laisser à l'optimiseur de choix à ce sujet avec une CTE et le hint /*+ materialize */ :

Code : Tout sélectionner

With sqlsummary as (select /*+ materialize */  complexe)
SELECT LIBELLE,
  GROUPING(LIBELLE) GROUPING_LIBELLE,
  SUM(C1_D0) C1_D0,
  SUM(C2_D0) C2_D0,
  SUM(C3_D0) C3_D0,
  SUM(C4_D0) C4_D0
FROM
  (select * from sqlsummary) t
GROUP BY rollup (LIBELLE)
Le fait de matérialiser ou non le jeu de résultats intermédiaire n'est pas une option sous PostgreSQL, les CTE de PostgreSQL sont toujours équivalentes aux CTE d'Oracle avec le hint /*+ materialize */ donc cette requête passera sans modification sous PostgreSQL. Même le hint ne gênera pas, il sera considéré comme un commentaire.
Attention, cette solution est uniquement valable car l'application doit prochainement passer sous PostgreSQL, le hint /*+ materialize */ n'est pas documenté et il n'est PAS recommandé de l'utiliser en général avec Oracle Database.
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 244
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Re: 2 requêtes rapides donnent une requête lente ?

Message par Phil » lun. 18 févr. 2019 18:50

Attention, le comportement par défaut du planner avec les CTE devrait changer à partir de PostgreSQL 12, cf https://pgphil.ovh/opti_cte_12_devel_01.php
Cdlt. Phil - pgphil.ovh

Répondre