SQL tuning sans toucher au sql

Bonjour à tous,

Suite à un certains nombres de tests, je n'arrive pas à optimiser cette requete (très moche) :

Code:

---

1 2 3 4 5 6 7 8 9

select DATASET, START, FINISH from STRUCTURE_DATES s where s.dataset > 0 and exists ( select 1 from task where origin_number=3 and s.structure=onb) or exists (select 1 from network where origin_number= 3 and s.structure=onb);

---

Voici son plan :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ ------------------------ | 0 | SELECT STATEMENT | | 84451 | 2144K| 1382 (2)| 00:00:20 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | STRUCTURE_DATES | 866K| 21M| 1382 (2)| 00:00:20 | |* 3 | TABLE ACCESS BY INDEX ROWID | TASK | 1 | 9 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | I_TASK | 1 | | 1 (0)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| NETWORK | 1 | 10 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | I_NETWORK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------

---

Cette requête étant générée, je ne peux en aucun cas la modifier (je suis avec l'éditeur en parallèle).

Pour vous donner plus d'éléments, voici un complément :
Si je touche la requete et mets des union, pas de pb de perf : utilisation de 2 merge loop, pour info le cout tombe à 10.
Si j'utilise use_concat, use_nl, leading en hint le plan ne change pas.
Si je split la requete en 2 (en séparant les or mais en laissant le exists), le plan est OK (2 merge loop)
Si je mets un hint rule, j'ai un changement de plan (juste pour dire que les hint sont pris en compte)
(les hints peuvent être une piste)
J ai essayé un index sur s.structure, dataset, pas de changement.

J'ai fait varier les optimizer_index_caching et optimizer_cost_adj à foison, sans aucun changement de plan.
J'ai essayé de grossir les histogrammes des tables des sous jointures, meme résultat.

J'ai également cherché tous azimuts, mais je suis surement passé à coté de qq choses.

Auriez vous d'autres pistes (à part se battre encore plus avec l'éditeur) ?

Version d'oracle : 10.2.0.3

D'avance merci.

Chmanu

Les statistiques des tables sont-elles à jour ?
Pourriez-vous éxecuter et poster le plan

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL>set serveroutput off SQL>SELECT /*+ gather_plan_statistics*/ DATASET, START, FINISH FROM STRUCTURE_DATES s WHERE s.dataset > 0 AND EXISTS ( SELECT 1 FROM task WHERE origin_number=3 AND s.structure=onb) OR EXISTS (SELECT 1 FROM network WHERE origin_number= 3 AND s.structure=onb) SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

---

Bonjour,

Merci de vous interresser au problème.
Oui, les statistiques sont à jour, j'ai essayé en dynamic sampling, avec les stats auto et en créant des histogrammes supérieur à auto (auto m en faisait 2, je suis monté à 50).

Voici les infos demandées mais je ne vois pas ce qu'elles apportent de plus.

Code:

---

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

----------------------------------------------------------------------- | Id | Operation | Name | E-Rows | ----------------------------------------------------------------------- |* 1 | FILTER | | | | 2 | TABLE ACCESS FULL | WORK_STRUCTURE_DATES | 866K| |* 3 | TABLE ACCESS BY INDEX ROWID | TASK | 1 | |* 4 | INDEX RANGE SCAN | I_TASK | 1 | |* 5 | TABLE ACCESS BY INDEX ROWID| NETWORK | 1 | |* 6 | INDEX RANGE SCAN | I_NETWORK | 1 | -----------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter((("DATASET">0 AND IS NOT NULL) OR IS NOT NULL)) 3 - filter("ORIGIN_NUMBER"=3) 4 - access("ONB"=:B1) 5 - filter("ORIGIN_NUMBER"=3) 6 - access("ONB"=:B1)   Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level

---

Je peux même mettre les statistiques de la requete :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4311527 consistent gets 30379 physical reads 0 redo size 461 bytes sent via SQL*Net to client 481 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed

---

Je ne l'avais pas précisé, mais vous remarquerez le 0 rows processed.

Merci,

Chmanu

Avez-vous utilisé le hint comme dans mon example ? Parce-que ce que je voulais c'était d'analyser c'est le E-ROWS par rapport au A-ROWS.

Excusez moi, je m'étais trompé dans la manip. Voici le résultat :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Plan hash value: 357719560   ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 0 |00:01:16.16 | 4311K| 44225 | | 2 | TABLE ACCESS FULL | WORK_STRUCTURE_DATES | 1 | 866K| 869K|00:00:07.82 | 3633 | 3658 | |* 3 | TABLE ACCESS BY INDEX ROWID | TASK | 869K| 1 | 0 |00:00:49.12 | 2412K| 33973 | |* 4 | INDEX RANGE SCAN | I_TASK | 869K| 1 | 671K|00:00:12.55 | 1740K| 997 | |* 5 | TABLE ACCESS BY INDEX ROWID| NETWORK | 869K| 1 | 0 |00:00:18.29 | 1895K| 6594 | |* 6 | INDEX RANGE SCAN | I_NETWORK | 869K| 1 | 153K|00:00:09.37 | 1742K| 247 | -------------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter((("DATASET">:SYS_B_0 AND IS NOT NULL) OR IS NOT NULL)) 3 - filter("ORIGIN_NUMBER"=:SYS_B_2) 4 - access("ONB"=:B1) 5 - filter("ORIGIN_NUMBER"=:SYS_B_4) 6 - access("ONB"=:B1)

---

La même chose en passant par un union :

Code:

---

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

Plan hash value: 1690908786   --------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | SORT UNIQUE | | 1 | 4 | 0 |00:00:00.02 | 4 | 4 | 1024 | 1024 | | | 2 | UNION-ALL | | 1 | | 0 |00:00:00.02 | 4 | 4 | | | | |* 3 | TABLE ACCESS BY INDEX ROWID | WORK_STRUCTURE_DATES | 1 | 1 | 0 |00:00:00.01 | 2 | 2 | | | | | 4 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 2 | 2 | | | | | 5 | SORT UNIQUE | | 1 | 1 | 0 |00:00:00.01 | 2 | 2 | 1024 | 1024 | | | 6 | TABLE ACCESS BY INDEX ROWID| TASK | 1 | 1 | 0 |00:00:00.01 | 2 | 2 | | | | |* 7 | INDEX RANGE SCAN | I01_TASK | 1 | 1 | 0 |00:00:00.01 | 2 | 2 | | | | |* 8 | INDEX RANGE SCAN | I_WORK_STRUCTURE_DATES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 9 | TABLE ACCESS BY INDEX ROWID | WORK_STRUCTURE_DATES | 1 | 1 | 0 |00:00:00.01 | 2 | 2 | | | | | 10 | NESTED LOOPS | | 1 | 3 | 1 |00:00:00.01 | 2 | 2 | | | | | 11 | SORT UNIQUE | | 1 | 3 | 0 |00:00:00.01 | 2 | 2 | 1024 | 1024 | | | 12 | TABLE ACCESS BY INDEX ROWID| NETWORK | 1 | 3 | 0 |00:00:00.01 | 2 | 2 | | | | |* 13 | INDEX RANGE SCAN | I01_NETWORK | 1 | 3 | 0 |00:00:00.01 | 2 | 2 | | | | |* 14 | INDEX RANGE SCAN | I_WORK_STRUCTURE_DATES | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   3 - filter("DATASET">:SYS_B_0) 7 - access("ORIGIN_NUMBER"=:SYS_B_2) 8 - access("WORK_STRUCTURE"="ONB") 9 - filter("DATASET">:SYS_B_3) 13 - access("ORIGIN_NUMBER"=:SYS_B_5) 14 - access("WORK_STRUCTURE"="ONB")

---

Première remarque: vous avez mis le paramètre cursor_sharing à une autre valeur (FORCE ou SIMILAIRE) que celle par défaut (EXACT), probablement parce que les requêtes utilise des littéraux codés en dur au lieu des variables de liaison. Il est possible que vos problèmes vient de la.

Deuxième remarque: comme vous pouvez constater facilement le mauvais plan corresponde à des mauvais estimations des cardinalités pour l'accès via les index des tables NETWORK et TASK. En fait l'optimiseur pense qu'il y aurait une seule ligne qui sortiras, E-ROWS = 1 mais en réalité il y en a des tonnes, A-ROWS = 153 000. Et comme c'est étape est exécuté 869 000 fois ça donné la Berezina.

Troisième remarque: l'accès aux tables NETWORK et TASK est très différente entre les deux plans, la mauvais plan utilise un mauvais index I_TASK et le bon plan un bon index I01_TASK.

Bref, la solution sera vite trouvée quand la cause des ces mauvais estimations sera comprise. Il se peut que vous souffrez d'un effet de bind variable peeking.

Modifier la requête d'origine, ou fait un flush shared_pool pour savoir si c'est le cas (je ne pense pas ).

Vérifiez/recalculez les statistiques pour les deux tables TASK et NETWORK pour y être certain qu'elle sont à jour. Vérifiez si vous collectez des histogrammes pour les colonnes en question .

Faite un test aussi avec cursor_sharing = EXACT pour voir si ce n'est pas qui pose des problèmes.

Bonjour,

La requête avec UNION ne donne pas le même résultat à mon avis:
- la requête initiale renvoit une ligne par ligne de la table principale
- UNION ALL peut en renvoyer plus (doublons) s'il y a des lignes qui répondent à la fois aux 2 OR
- UNION va tout dédoublonner, et donc renvoyer moins de lignes (sauf si DATASET, START, FINISH) est unique.

Est-ce que l'optimiseur a toutes les infos qui l'aiderait la dedans (primary keys, contraintes d'unicité...) ?

Cordialement,
Franck.

Bonjour et merci pour vos réponses.

Concernant cursor_sharing, il est à similar, mais uniquement sur la session en cours car j'ai effectivement fait d'autres tests sur cette session et j'ai mesuré l'amélioration avec ce paramètre au niveau session dans un premier temps. Sur mon test global, il est bien à EXACT ; donc pas de pb de bind peeking.

Comme précisé auparavant, j'avais collecté les statistiques de différentes manières sur les tables NETWORK et TASK (y compris en utilisant le dynamic sapling). Comme vous le souligner, la différence entre le e-rows et le a-rows montre le manque de précision à ce niveau ; peut être encore plus d'histogrammes sur WORK_STRUCTURE_DATES arrangerait l'affaire.

Pour répondre à pachot, effectivement, la requête n'est pas tout à fait identique, mais je voulais juste m'assurer que le chemin d'accès aux données était possible. un peu de CPU supplémentaire devrait permettre à l'optimizer de renvoyer les données voulues. Mais je vais tout de même m'assurer de voir si une contrainte d'unicité existe et sinon, si elle ne serait pas utile. Ou bien encapsuler la requête dans un select.

Enfin et surtout, je voulais savoir pourquoi le hint use_concat n'est pas utilisé ; est ce parce que comme indiqué il manque des infos d'unicité ?

Chmanu

PS : Je n'ai plus accès à ma base ce jour, c'est pourquoi je ferai mes tests un peu plus tard.

Merci pour vos aides diverses, ce problème m'a appris différentes choses.

Il a finalement été solutionné par l'éditeur.

Cordialement,

Chmanu