Discussion :

[nuke_y]

Bonjour à tous.

J'ai un problème que j'ai rencontré de nombreuses fois depuis plusieurs mois. J'ai toujours cru que c'était un comportement "normal" bien que rare mais Oracle nous a créé un bug sur le sujet.

Sur certaines requêtes, le coût total est faible, alors que certaines étapes ont un coût très élevé. Evidemment quand la requête est lancée elle est très longue.

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
Plan hash value: 3243721844
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                            |    57 | 25080 |  2124   (3)| 00:00:01 |
|   1 |  WINDOW SORT                              |                            |    57 | 25080 |  2124   (3)| 00:00:01 |
|*  2 |   FILTER                                  |                            |       |       |         |             |
|*  3 |    HASH JOIN                              |                            |   133 | 58520 |  1643   (3)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                      | SECURITY_TABLE1_PK         |   116 |  5220 |     4   (0)| 00:00:01 |
|*  5 |     HASH JOIN                             |                            |   128 | 50560 |  1638   (3)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                     | SECURITY_TABLE1_PK         |   116 |  5220 |     4   (0)| 00:00:01 |
|   7 |      NESTED LOOPS OUTER                   |                            |   124 | 43400 |  1634   (3)| 00:00:01 |
|   8 |       NESTED LOOPS OUTER                  |                            |   124 | 33728 |  1383   (3)| 00:00:01 |
|   9 |        NESTED LOOPS                       |                            |   124 | 21452 |  1133   (3)| 00:00:01 |
|  10 |         NESTED LOOPS OUTER                |                            |   104 | 11856 |   817   (3)| 00:00:01 |
|  11 |          NESTED LOOPS                     |                            |   104 |  8112 |   508   (3)| 00:00:01 |
|* 12 |           TABLE ACCESS BY INDEX ROWID     | FCT_HEADERS                |    99 |  5148 |   108   (4)| 00:00:01 |
|* 13 |            INDEX RANGE SCAN               | FCT_HEADERS_ID1            |   126 |       |     3   (0)| 00:00:01 |
|  14 |           TABLE ACCESS BY INDEX ROWID     | FCT_ADDRESSES              |     1 |    26 |     4   (0)| 00:00:01 |
|* 15 |            INDEX RANGE SCAN               | FCT_ADDRESSES_PK           |     1 |       |     3   (0)| 00:00:01 |
|  16 |          VIEW PUSHED PREDICATE            | V_ACTORS01                 |     1 |    36 |     3   (0)| 00:00:01 |
|  17 |           NESTED LOOPS OUTER              |                            |     1 |   143 |    15   (0)| 00:00:01 |
|  18 |            NESTED LOOPS OUTER             |                            |     1 |   124 |    14   (0)| 00:00:01 |
|  19 |             NESTED LOOPS OUTER            |                            |     1 |   101 |    13   (0)| 00:00:01 |
|  20 |              NESTED LOOPS OUTER           |                            |     1 |    78 |    12   (0)| 00:00:01 |
|  21 |               NESTED LOOPS OUTER          |                            |     1 |    51 |     6   (0)| 00:00:01 |
|  22 |                TABLE ACCESS BY INDEX ROWID| ACTORS01                   |     1 |    40 |     4   (0)| 00:00:01 |
|* 23 |                 INDEX RANGE SCAN          | ACTORS01_PK                |     1 |       |     3   (0)| 00:00:01 |
|* 24 |                INDEX RANGE SCAN           | ACTOR01_PROFILE_PK         |     1 |    11 |     2   (0)| 00:00:01 |
|* 25 |               TABLE ACCESS BY INDEX ROWID | ACTOR01_GROUPS_MAINTENANCE |     1 |    27 |     6   (0)| 00:00:01 |
|* 26 |                INDEX RANGE SCAN           | BPM_DVE_UID_IDX2           |     5 |       |     2   (0)| 00:00:01 |
|* 27 |              TABLE ACCESS BY INDEX ROWID  | ACTORS01_GROUP             |     1 |    23 |     1   (0)| 00:00:01 |
|* 28 |               INDEX UNIQUE SCAN           | PK_ACTOR01_GROUPS          |     1 |       |     1   (0)| 00:00:01 |
|* 29 |             TABLE ACCESS BY INDEX ROWID   | ACTORS01_GROUP             |     1 |    23 |     1   (0)| 00:00:01 |
|* 30 |              INDEX UNIQUE SCAN            | PK_ACTOR01_GROUPS          |     1 |       |     1   (0)| 00:00:01 |
|* 31 |            TABLE ACCESS BY INDEX ROWID    | ACTORS01_GROUP             |     1 |    19 |     1   (0)| 00:00:01 |
|* 32 |             INDEX UNIQUE SCAN             | PK_ACTOR01_GROUPS          |     1 |       |     1   (0)| 00:00:01 |
|  33 |         TABLE ACCESS BY INDEX ROWID       | FCT_DETAILS                |     1 |    59 |     3   (0)| 00:00:01 |
|* 34 |          INDEX RANGE SCAN                 | FCT_DETAILS_NEW_PK         |     1 |       |     2   (0)| 00:00:01 |
|  35 |        VIEW PUSHED PREDICATE              |                            |     1 |    99 |     2   (0)| 00:00:01 |
|* 36 |         HASH JOIN SEMI                    |                            |     1 |   197 | 71724  (33)| 00:00:31 |
|  37 |          NESTED LOOPS                     |                            |     1 |   108 |     4   (0)| 00:00:01 |
|* 38 |           TABLE ACCESS BY INDEX ROWID     | JMPD_DETAILS_ITEM          |     1 |    63 |     3   (0)| 00:00:01 |
|* 39 |            INDEX RANGE SCAN               | JMPD_PK                    |     1 |       |     2   (0)| 00:00:01 |
|* 40 |           INDEX UNIQUE SCAN               | SECURITY_TABLE1_PK         |     1 |    45 |     1   (0)| 00:00:01 |
|* 41 |          VIEW                             | VW_SQ_2                    |  9020K|   765M| 69396  (31)| 00:00:30 |
|* 42 |           HASH JOIN                       |                            |  9020K|   516M| 69396  (31)| 00:00:30 |
|* 43 |            INDEX RANGE SCAN               | SECURITY_TABLE1_PK         |   116 |  5220 |     4   (0)| 00:00:01 |
|  44 |            INDEX FAST FULL SCAN           | FCT_DEPARTMENTS_PK         |    32M|   468M| 60954  (21)| 00:00:27 |
|  45 |       VIEW PUSHED PREDICATE               |                            |     1 |    78 |     2   (0)| 00:00:01 |
|* 46 |        HASH JOIN SEMI                     |                            |     1 |   176 | 71724  (33)| 00:00:31 |
|  47 |         NESTED LOOPS                      |                            |     1 |    87 |     4   (0)| 00:00:01 |
|* 48 |          TABLE ACCESS BY INDEX ROWID      | DPCD_DELIVERY              |     1 |    42 |     3   (0)| 00:00:01 |
|* 49 |           INDEX RANGE SCAN                | DPCD_PK                    |     1 |       |     2   (0)| 00:00:01 |
|* 50 |          INDEX UNIQUE SCAN                | SECURITY_TABLE1_PK         |     1 |    45 |     1   (0)| 00:00:01 |
|* 51 |         VIEW                              | VW_SQ_1                    |  9020K|   765M| 69396  (31)| 00:00:30 |
|* 52 |          HASH JOIN                        |                            |  9020K|   516M| 69396  (31)| 00:00:30 |
|* 53 |           INDEX RANGE SCAN                | SECURITY_TABLE1_PK         |   116 |  5220 |     4   (0)| 00:00:01 |
|  54 |           INDEX FAST FULL SCAN            | FCT_DEPARTMENTS_PK         |    32M|   468M| 60954  (21)| 00:00:27 |
|  55 |    NESTED LOOPS                           |                            |     2 |   120 |     5   (0)| 00:00:01 |
|* 56 |     INDEX RANGE SCAN                      | FCT_DEPARTMENTS_PK         |     2 |    30 |     3   (0)| 00:00:01 |
|* 57 |     INDEX UNIQUE SCAN                     | SECURITY_TABLE1_PK         |     1 |    45 |     1   (0)| 00:00:01 |
|  58 |      NESTED LOOPS                         |                            |     2 |   120 |     5   (0)| 00:00:01 |
|* 59 |       INDEX RANGE SCAN                    | FCT_DEPARTMENTS_PK         |     2 |    30 |     3   (0)| 00:00:01 |
|* 60 |       INDEX UNIQUE SCAN                   | SECURITY_TABLE1_PK         |     1 |    45 |     1   (0)| 00:00:01 |

Oracle a ouvert ce bug :

Bug No. 8865635
CBO UNDERESTIMATES THE OVERALL COST ALTHOUGH RECURSIVE STEPS SHOW HIGHER COSTS

Donc il semblerait que ce soit bien un bug. Toutefois cela me semble tellement énorme que je voulais avoir votre avis : l'avez-vous rencontré ? A votre avis, est-ce bien un bug et sinon comment expliqueriez-vous ce comportement ?

Merci

[mnitu]

Peu être des sous-requêtes scalaires.
Mais, pour vos problèmes de performances c'est plutôt les cardinalités estimées par rapport aux celles réelles qu'il convient d'examiner.

[nuke_y]

Merci de la réponse, ça me parait une explication valable au problème. Effectivement nous faisons un

Code :

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

select sum() over partition from ( select ...)

ce qui pourrait expliquer ce comportement.

Le problème c'est que nous utilisons Oracle Resource Manager qui est censé tuer, sans même les lancer, les requêtes qu'il estime devoir durer plus de 10mn. Evidemment dans ce cas là il ne se déclenche pas.

Pour votre remarque sur les cardinalités estimées, je ne suis pas sûr qu'il se trompe de beaucoup sur ce qu'il annonce dans le plan. Sur quel point pensez-vous qu'il pourrait se tromper ?

[mnitu]

A chaque mauvais plan il corresponde une erreur d’estimation entre les nombre des lignes que l’optimiseur pense qu’il va ramener et celui qu’il ramène effectivement. La cause de cette sous-estimation peut varier : mauvaise statistiques des objets, interdépendance des colonnes, complexité de la requête, bug dans l’optimiseur, etc. Et parfois si la cause est bien ciblée il suffit de la corriger pour que tout rentre dans l’ordre (recalculer les statistiques, faire de l’échantillonnage dynamique, etc.) . Sur une base 10g (à partir de R2 il me semble) comparer les deux valeurs est très simple. Dans les versions antérieures cella revient en gros à comparer l’explain plan avec le plan d’exécution trouvé dans une trace sql de la requête (via tkprof).
Les sous-requêtes scalaires sont un peu contraignantes ; elles imposent un chemin d’accès : c’est seulement après avoir obtenu les lignes de la source des données principale que la requête scalaire peut s’effectuer. Dans le cas de gros volumes ce n’est pas l’idéal, sauf si vous limitez le nombre des lignes à traiter. Donc si les cardinalités sont correctes et la requête parte quand même dans le choux, cella signifie que les sous-requêtes scalaires sont plutôt un problème qu’une solution. Dans ce cas il n’y pas mille choix, vous devez réécrire la requête.

[nuke_y]

Bon, sans retour d'Oracle on ne sait pas trop si le bug en est un ou pas, mais les indications de mnitu semblent indiquer que non.

Donc je marque le sujet en Résolu puisque je pense que nous avons fait le tour.

Merci à toi mnitu.