Discussion :

[crubs]

Bonjour à tous.

Je suis en train d'optimiser les performances(du moins j'essaye) d'une application qui présente des problèmes de lenteurs.
Parmi les requêtes lentes, j'en ai une de la forme :

Code :

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

SELECT * FROM Table1 WHERE Champ1 IN (SELECT Champ2 FROM Table2 WHERE Champ2_2 = 1)

Où Table1 comporte plusieurs millions de lignes, la requête 'SELECT Champ2 FROM Table2 WHERE Champ2_2 = 1' ne renvoie que 5 lignes et le résultat ne comporte que 30 lignes.
J'ai effectué deux tentatives d'amélioration différentes:

Code :

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

1
2
WITH Table3 AS (SELECT Champ2 FROM Table2 WHERE Champ2_2 = 1)
SELECT * FROM Table1 WHERE Champ1 IN (SELECT Champ2 FROM Table3)

Les performances sont à peu près identiques
J'ai ensuite essayé avec une jointure:

Code :

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

SELECT Table1.* FROM Table1 INNER JOIN Table2 ON Table1.Champ1 = Table2.Champ2 WHERE Table2.Champ2_2 = 1

Les performances sont un peu meilleures.
Enfin, j'ai essayé un truc du genre(Vous me comprendrez):

Code :

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

String maChaine = Join(Result("SELECT Champ2 FROM Table2 WHERE Champ2_2 = 1"), ","))

Ensuite

Code :

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

SELECT * FROM Table1 WHERE Champ1 IN (" + maChaine + ")

Et là, les performances sont très bonne(environ 3 à 10 fois meilleures que les solutions précédentes.

C'est à peu près l'inverse que j'aurais imaginé: La solution avec la clause In sous forme de chaîne n'est pas indexée, elle nécessite une boucle pour concaténer la chaîne de caractères... ça n'envoie pas du rêve!
Alors comment peut-elle être plus performante que les autres solutions, je ne comprends pas??
J'ai l'impression que la solution avec 'With' exécute la requête 'SELECT Champ2 FROM Table2 WHERE Champ2_2 = 1' pour chaque ligne??
Peut-on utiliser des hint pour améliorer la solution avec la clause With, en forçant Oracle à ne la calculer qu'une seule fois?

Avez-vous des idées à me proposer, sachant que je ne suis pas dba et que je ne peux pas toucher à la structure de la base de données.

Merci d'avance pour vos réponses et joyeuses fêtes.

[Waldar]

Votre démarche est un peu surprenante, vous partez dans la réécriture de requête, vous envisagez des HINT mais vous ne commencez pas par l'essentiel qui est le plan d'exécution (réel).
On cherche d'abord à comprendre ce qui se passe avant d'appliquer des solutions !

[crubs]

Bonjour,

Peut-être cela peut-il paraître surprenant pour l'expert que je ne suis pas, mais ce qui me surprend, moi, c'est qu'il y ait une différence de temps d'exécution d'un facteur quasiment 10 entre...

Code :

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

1
2
 
SELECT * FROM Table1 WHERE Champ1 IN (SELECT Champ2 FROM Table2 WHERE Champ2_2 = 1)

...et...

Code :

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

1
2
3
 
String maChaine = Join(Result("SELECT Champ2 FROM Table2 WHERE Champ2_2 = 1"), ","))
SELECT * FROM Table1 WHERE Champ1 IN (" + maChaine + ")

...alors que le plan d'exécution est censé être le même.
Il y a dans tous les cas un problème évident d'optimisation de la part d'Oracle et il pourrait exister(sait-on jamais) une technique courante pour améliorer les performance dans ce cas très commun.

J'ai travaillé beaucoup plus sur SQL Server et je n'ai jamais remarqué un tel comportement.

Comme je le disais, je n'ai pas accès à la base de données, j'ai juste une ip et un user/pwd et je ne suis pas expert Oracle.
Ai-je tout de même un moyen d'afficher le plan d'exécution de cette requête?

Merci d'avance.

Cordialement,

[skuatamad]

Pour le plan d'exécution :
Afficher le plan d’exécution avec les stats de chaque étape

Par ailleurs, il est bon de vérifier que les statistiques sont à jour, et d'envisager de calculer des histogrammes pour les colonnes ayant une répartition de données très peu uniforme.
Après tout dépend de vos droits d'accès, il sera peut être (probablement) nécessaire de passer en partie la main au DBA.

[crubs]

Merci skuatamad pour cette réponse.

Comme je pouvais m'y attendre:

Code :

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

1
2
3
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
==> "User has no SELECT privilege on V$SESSION"

D'autres idées éventuellement de quelqu'un qui se serait heurté à ce problème?

[mnitu]

...alors que le plan d'exécution est censé être le même.

En fait vous voulez dire que le résultat est le même parce que vous avez écrit les requêtes pour qu'elles donnent le même résultat. Mais ces deux requêtes que vous comparez ne sont pas équivalentes!

Requête de type 1: l'optimiseur est obligé de visiter la table departements pour pouvoir trouver les enregistrements de la table employees correspondantes.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
SQL> explain plan for
  2  select first_name
  from hr.employees e
 Where e.department_id In (Select d.department_id
                             from hr.departments d
                            Where d.department_name like 'IT%'
                          )  3    4    5    6    7
  8  /
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3985490701
 
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    10 |   260 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    10 |   100 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |             |    10 |   260 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | DEPARTMENTS |     1 |    16 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_DEPT_ID |    10 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("D"."DEPARTMENT_NAME" LIKE 'IT%')
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 
17 ligne(s) sélectionnée(s).

Requête de type 1: Les valeurs filtres fournies concernent seulement la table employees

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
SQL> explain plan for
  2  select first_name
  from hr.employees e
 Where e.department_id in (60,210,230)  3    4
  5  /
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
Plan hash value: 2485388078
 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     6 |    60 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     6 |    60 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPT_ID |     6 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("E"."DEPARTMENT_ID"=60 OR "E"."DEPARTMENT_ID"=210 OR
              "E"."DEPARTMENT_ID"=230)
 
16 ligne(s) sélectionnée(s).

[crubs]

On avance on avance! :-)
Merci mnitu pour cette réponse,
donc cela confirme bien ce que je pensais!
Le 'TABLE ACCESS FULL' sur la table DEPARTMENTS est situé dans une boucle(NESTED LOOPS).
donc pour chaque ligne de la table EMPLOYEES, le 'TABLE ACCESS FULL' sur la table DEPARTMENTS est exécuté.
Or, le résultat du 'TABLE ACCESS FULL' sur la table DEPARTMENTS est invariable quelque soit la ligne de la table EMPLOYEES.
Donc ça me semble une aberration que le 'TABLE ACCESS FULL' soit exécuté à chaque fois.
Pourquoi Oracle ne mettrait pas les données en 'cache' pour les utiliser sur chaque ligne de la table EMPLOYEES, comme je le fais moi avec ma clause In sous forme de chaîne de caractère??

J'avoue qu'il y a un truc qui me dérange!

En bon français, Oracle raisonne ainsi:

Code :

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

1
2
3
4
Pour chaque employé 
   Je recherche le département de l`employé <= Full Scan sur toute la table département pour en retrouver le nom
      Si le nom du département like 'IT%' alors je retiens cet employé

Or, j'aurais imaginé un plan d'exécution du style:

Code :

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

1
2
3
4
5
6
7
Pour chaque département
   Si le nom du département like 'IT%' alors je retiens ce département

Pour chaque employé 
   Si le département de l'employé est dans la liste des département retenus <= Scan sur une liste de quelques départements préalablement filtrés
      alors je retiens cet employé

Pour les matheux, dans les deux cas on est en face d'un algorithme d'ordre ln(a) x ln(b).
Si dans les deux cas a = nombre d'employés:
- dans le cas 1, b = nombre départements
- dans le cas 2, b = nombre départements dont le nom like 'IT%'
Or, en ce qui me concerne b = 15000 dans le premier cas puis 5 dans le deuxième cas, soit ln(15000) / ln(5) soit un facteur 6!

Cela me parait tellement gros que je suis sûr qu'Oracle a bien une solution pour pallier à ça!

[mnitu]

...
En bon français, Oracle raisonne ainsi:

Code :

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

1
2
3
4
Pour chaque employé 
   Je recherche le département de l`employé <= Full Scan sur toute la table département pour en retrouver le nom
      Si le nom du département like 'IT%' alors je retiens cet employé

...

Non vous n'avez pas compris ce plan. Oracle commence avec l'étape 3 qui est un accès en balayage complet de la table département (première opération après le Nested Loop). Si vous regardez la section prédicat vous trouvez que les enregistrements ainsi ramenés sont filtrés par rapport à la condition "Like 'IT%'". Une fois qu'un département a passé la condition la boucle recherche les rowids correspondantes de la table employés via l'index EMP_DEPT_ID. Ces rowids sont envoyés à l'opération 1 qui retourne le nom des employées. Après on passe au deuxième enregistrement de la table des départements qui respecte la condition, etc.