SQL tuning sans toucher au sql

**chmanu** · 19/05/2010, 15h26

Bonjour à tous,

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

Code :

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

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 :

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
------------------------
| 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

**mnitu** · 19/05/2010, 16h30

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

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
 
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'));

**chmanu** · 19/05/2010, 16h52

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 :

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
-----------------------------------------------------------------------
| 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 :

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

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

**mnitu** · 19/05/2010, 17h14

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.

**chmanu** · 19/05/2010, 17h38

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

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
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)

**chmanu** · 19/05/2010, 17h41

La même chose en passant par un union :

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
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")

**mnitu** · 19/05/2010, 20h30

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.

**pachot** · 19/05/2010, 21h15

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.

**chmanu** · 25/05/2010, 12h30

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.

**chmanu** · 28/07/2010, 17h22

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

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

Cordialement,

Chmanu

SQL tuning sans toucher au sql

SQL Oracle

Discussions similaires

Partager

Partager