Performance de ma query

Version imprimable

Bonjour à tous j'ai des problèmes de perf sur cette query:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT rphc.proc_id,rps.step_name, round(avg( rpha.date_of_action_end - rpha.date_of_action_start),2) average FROM rphistory_case rphc INNER JOIN rpcase_attr_1 rpca ON rpca.case_id = rphc.case_id INNER JOIN rphistoryaction rpha ON rpha.case_id = rphc.case_id INNER JOIN rpstep rps ON rps.step_id = rpha.step_id WHERE rpha.action_type = 4 AND rps.step_type = 'NORMAL' AND rphc.proc_id = 2004 AND rpca.agent_traitant IN( select userid from rplogin_userid where login = 'myLogin') --AND rpca.pup_id IN (2270,2271,2269,2268) AND rpca.year_start BETWEEN 2011 AND 2012 GROUP BY rphc.proc_id, rps.step_name ORDER BY rphc.proc_id, rps.step_name
Toutes les tables sont des materialized view, j'ai rajouté des indexs sur les id concernés.
malgré cela, la requête est lente à exécuter.
Sauf si j'ajoute la clause commentée --AND rpca.pup_id IN (2270,2271,2269,2268)

Y aurait-il moyen d'optimiser la query?

Cordialement,
Vinc.

27/08/2012, 11h11
skuatamad

Est ce que les filtres suivant ont une bonne sélectivité ?
- rps.step_type = 'NORMAL'
- rphc.proc_id = 2004
- rpca.year_start BETWEEN 2011 AND 2012

Inspirez vous de ce post pour rechercher (et fournir) les premiers éléments nécessaires à toute démarche d'optimisation :
http://www.developpez.net/forums/d11...e/#post6505385
27/08/2012, 11h16
vinch999

Les filtres sont corrects.
Ma requête retourne bien les résultats désirés.
Le seul problème est que c'est lent.

Je vais aller voir ton lien, merci.
27/08/2012, 11h21
skuatamad

Citation:

Envoyé par vinch999

Les filtres sont corrects.

Je parle de sélectivité... Est ce que ces filtres ramènent un faible nombre de lignes de la VM concernée ou pas ?

Version de la db:

Code:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

Requête sql:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT rphc.proc_id,rps.step_name,
 round(avg( rpha.date_of_action_end - rpha.date_of_action_start),2) average 
FROM rphistory_case rphc 
INNER JOIN rpcase_attr_1 rpca 
  ON rpca.case_id = rphc.case_id 
INNER JOIN rphistoryaction rpha 
  ON rpha.case_id = rphc.case_id 
INNER JOIN  rpstep rps 
  ON rps.step_id = rpha.step_id
WHERE rpha.action_type = 4 
AND rps.step_type = 'NORMAL' 
AND rphc.proc_id = 2004 
AND rpca.agent_traitant IN( SELECT userid FROM rplogin_userid WHERE login = 'myLogin') 
--AND rpca.pup_id IN (2270,2271,2269,2268) 
AND rpca.year_start BETWEEN 2011 AND 2012 
GROUP BY rphc.proc_id, rps.step_name
ORDER BY rphc.proc_id, rps.step_name

SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')) :

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SQL_ID  da7pwbv58vfsy, child number 1
-------------------------------------
SELECT rphc.proc_id,rps.step_name,  round(avg( rpha.date_of_action_end 
- rpha.date_of_action_start),2) average  FROM rphistory_case rphc  
INNER JOIN rpcase_attr_1 rpca    ON rpca.case_id = rphc.case_id  INNER 
JOIN rphistoryaction rpha    ON rpha.case_id = rphc.case_id  INNER JOIN 
 rpstep rps    ON rps.step_id = rpha.step_id WHERE rpha.action_type = 4 
 AND rps.step_type = 'NORMAL'  AND rphc.proc_id = 2004  AND 
rpca.agent_traitant IN( select userid from rplogin_userid where login = 
'myLogin')  --AND rpca.pup_id IN (2270,2271,2269,2268)  AND rpca.year_start 
BETWEEN 2011 AND 2012  GROUP BY rphc.proc_id, rps.step_name ORDER BY 
rphc.proc_id, rps.step_name
 
Plan hash value: 3116754070
 
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |      1 |        |     20 |00:00:00.59 |     168K|       |       |          |
|   1 |  SORT ORDER BY                       |                             |      1 |      1 |     20 |00:00:00.59 |     168K|  6144 |  6144 | 6144  (0)|
|   2 |   HASH GROUP BY                      |                             |      1 |      1 |     20 |00:00:00.59 |     168K|   716K|   716K| 1197K (0)|
|   3 |    NESTED LOOPS                      |                             |      1 |        |  39666 |00:00:00.55 |     168K|       |       |          |
|   4 |     NESTED LOOPS                     |                             |      1 |      1 |  58458 |00:00:00.44 |     110K|       |       |          |
|   5 |      NESTED LOOPS                    |                             |      1 |      1 |  58458 |00:00:00.38 |     110K|       |       |          |
|   6 |       NESTED LOOPS                   |                             |      1 |      1 |   2532 |00:00:00.06 |   47888 |       |       |          |
|*  7 |        HASH JOIN SEMI                |                             |      1 |      1 |  25775 |00:00:00.03 |     834 |  1532K|  1114K| 2222K (0)|
|*  8 |         MAT_VIEW ACCESS FULL         | RPCASE_ATTR_1               |      1 |     65 |  25930 |00:00:00.01 |     788 |       |       |          |
|*  9 |         MAT_VIEW ACCESS FULL         | RPLOGIN_USERID              |      1 |    387 |    377 |00:00:00.01 |      46 |       |       |          |
|* 10 |        MAT_VIEW ACCESS BY INDEX ROWID| RPHISTORY_CASE              |  25775 |      1 |   2532 |00:00:00.03 |   47054 |       |       |          |
|* 11 |         INDEX UNIQUE SCAN            | PK_RTHISTORY_CASE           |  25775 |      1 |  23012 |00:00:00.01 |   24042 |       |       |          |
|* 12 |       MAT_VIEW ACCESS BY INDEX ROWID | RPHISTORYACTION             |   2532 |     12 |  58458 |00:00:00.31 |   62302 |       |       |          |
|* 13 |        INDEX RANGE SCAN              | IDX_CASE_ID_RPHISTORYACTION |   2532 |     49 |    228K|00:00:00.01 |    6464 |       |       |          |
|* 14 |      INDEX UNIQUE SCAN               | PK_RTSTEP                   |  58458 |      1 |  58458 |00:00:00.03 |       4 |       |       |          |
|* 15 |     MAT_VIEW ACCESS BY INDEX ROWID   | RPSTEP                      |  58458 |      1 |  39666 |00:00:00.06 |   58458 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("RPCA"."AGENT_TRAITANT"="USERID")
   8 - filter(("RPCA"."AGENT_TRAITANT" IS NOT NULL AND TO_NUMBER("RPCA"."YEAR_START")>=2011 AND TO_NUMBER("RPCA"."YEAR_START")<=2012))
   9 - filter("LOGIN"='DVA')
  10 - filter("RPHC"."PROC_ID"=2004)
  11 - access("RPCA"."CASE_ID"="RPHC"."CASE_ID")
  12 - filter("RPHA"."ACTION_TYPE"=4)
  13 - access("RPHA"."CASE_ID"="RPHC"."CASE_ID")
  14 - access("RPS"."STEP_ID"="RPHA"."STEP_ID")
  15 - filter("RPS"."STEP_TYPE"='NORMAL')
 
Note
-----
   - dynamic sampling used for this statement

PS: elle semble être dans le cache :( Les infos sont-elles tout de mêm pertinentes?

Citation:

Envoyé par skuatamad

Je parle de sélectivité... Est ce que ces filtres ramènent un faible nombre de lignes de la VM concernée ou pas ?

Code:

1
2
3
4
5
6
7
8
SELECT count(*)
FROM rphistory_case rphc 
INNER JOIN rpcase_attr_1 rpca 
  ON rpca.case_id = rphc.case_id 
INNER JOIN rphistoryaction rpha 
  ON rpha.case_id = rphc.case_id 
INNER JOIN  rpstep rps 
  ON rps.step_id = rpha.step_id

me retourne 680000 lignes

Code:

1
2
3
4
5
6
7
8
9
10
SELECT rphc.proc_id,rps.step_name,
 round(avg( rpha.date_of_action_end - rpha.date_of_action_start),2) average 
FROM rphistory_case rphc 
INNER JOIN rpcase_attr_1 rpca 
  ON rpca.case_id = rphc.case_id 
INNER JOIN rphistoryaction rpha 
  ON rpha.case_id = rphc.case_id 
INNER JOIN  rpstep rps 
  ON rps.step_id = rpha.step_id
  GROUP BY rphc.proc_id, rps.step_name

Me retourne 164 lignes

Code:

1
2
3
4
5
6
7
8
9
10
SELECT count(*)
FROM rphistory_case rphc 
INNER JOIN rpcase_attr_1 rpca 
  ON rpca.case_id = rphc.case_id 
INNER JOIN rphistoryaction rpha 
  ON rpha.case_id = rphc.case_id 
INNER JOIN  rpstep rps 
  ON rps.step_id = rpha.step_id
  WHERE rpha.action_type = 4 
AND rps.step_type = 'NORMAL'

me retourne 207730 lignes

27/08/2012, 11h58
mnitu

Il y a deux choses qui interpellent toute de suite

Citation:

dynamic sampling used FOR this statement

qui peut signifier le manque des statistiques à jour
et

Citation:

AND TO_NUMBER("RPCA"."YEAR_START")>=2011

qui indique une coercition pouvant faire sauter un éventuel index.

[Edit]
Et bien sur les différences importantes entre les cardinalités estimés et celles réelles.
[/Edit]
27/08/2012, 12h13
vinch999

Citation:

Envoyé par mnitu

le manque des statistiques à jour

C'est-à-dire?

Citation:

Envoyé par mnitu

qui indique une coercition pouvant faire sauter un éventuel index.

Ce champ n'est pas indexé (j'ai peut-être mal compris ce commentaire)

Citation:

Envoyé par mnitu

Et bien sur les différences importantes entre les cardinalités estimés et celles réelles.

Peux-tu être plus explicite à ce sujet?

Merci pour l'aide. (Je ne suis pas expert dans ce domaine précis:?)

Quelques pistes :

Il me semble que cette requête est équivalente
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT rphc.proc_id, rps.step_name, round(avg(rpha.date_of_action_end - rpha.date_of_action_start),2) average FROM rphistory_case rphc INNER JOIN rpcase_attr_1 rpca ON rpca.case_id = rphc.case_id INNER JOIN rphistoryaction rpha ON rpha.case_id = rphc.case_id INNER JOIN rpstep rps ON rps.step_id = rpha.step_id INNER JOIN (SELECT userid FROM rplogin_userid WHERE login = 'myLogin') mdv ON mdv.userid = rpca.agent_traitant WHERE rpha.action_type = 4 AND rps.step_type = 'NORMAL' AND rphc.proc_id = 2004 AND rpca.year_start BETWEEN 2011 AND 2012 AND rpca.case_id=rpha.case_id -- simple rappel de la transitivité en cas d'oubli de l'optimiseur GROUP BY rphc.proc_id, rps.step_name ORDER BY rphc.proc_id, rps.step_name
Il y a cependant peu de chance pour qu'elle soit meilleure, mais le plan donné n'indiquant pas de vue dynamique elle vaut le coup d'être tentée.

Autrement :
Y a -t- il un index sur rphlogin_userid.login ?

Est-il possible de faire (ou existe-t-il) un index bi colonne sur proc_id et case_id sur la table rphistory_case (double effet => meilleure estimation de cardinalité, et évitement de la table ) ?

Ensuite concernant la principale question de mnitu :
Oracle indique qu'il effectue du dynamiqc sampling ce qui signifie qu'il lui manque des statistqiue sur au moins un des objets impliqués dans la table.

27/08/2012, 14h01
mnitu

Trouver le plan d’exécution optimal implique une connaissance des données qu’on recherche ce que l’optimiseur arrive à faire correctement en présence des statistiques détaillées des ces données. Normalement, ces statistiques sont collectées via une tâche automatique et les diverses valeurs de ces statistiques (num_rows, num_block, etc.) sont disponible dans le méta dictionnaire d’Oracle (dba/all/user_tables, dba/all_user_tab_clomns, dba/all_user_indexes, etc.) ainsi que la date de la dernière mise à jour (last_analyzed).

Probablement que vos vues matérialisées manque des statistiques; essayez d’abord de le mettre à jour via le package dbms_stats.
27/08/2012, 14h09
StringBuilder
J'ai beau lire et relire la requête, à quoi sert la sous-requête sur le login ? Pourquoi ne pas faire une bête jointure ?

Ensuite, quels sont les index utilisés lorsque la ligne qui pose problème est en commentaire ? Et quels sont les index utilisés lorsqu'elle n'est plus en commentaire ?

Peut-être que si les stats ne sont pas à jour, le choix de l'index est catastrophique : j'ai le souci chez un client, où par moment Oracle "pète un plomb" et prends un index qui s'avère catastrophique.

Un truc du genre :
Code:

1 2 where A = 1 and B = 1 and C = 1 and D in (1, 2, 3, 4)
Qui au lieu de filtrer sur l'index unique (A, B, C) utilise l'index non unique (Z, Y, X, W, V, U, D)

Résultat, quand on supprime le filtre sur D (qui est en plus inutile, vu qu'on a déjà isolé l'unique ligne à retourner avec les filtres sur A, B et C), la requête passe de 3 heures à 2 millisecondes.

Essayez donc déjà de voir si votre base ne vous joue pas ce genre de tours.
27/08/2012, 17h40
Rei Ichido

Attention, parfois les performances deviennent catastrophiques APRES la mise à jour des stats. J'ai eu l'exemple sur une appli où après avoir mis à jour les stats, certaines requêtes devenaient catastrophiques suite à la création de certains histogrammes (et là j'avoue que je n'ai toujours pas compris pourquoi le moteur d'optimisation buggait comme ça avec ces histogrammes).
=> J'ai viré les histogrammes problématiques et j'ai réglé le niveau des stats sur ces tables.
27/08/2012, 17h43
mnitu

Citation:

Envoyé par Rei Ichido

...et là j'avoue que je n'ai toujours pas compris pourquoi le moteur d'optimisation buggait comme ça avec ces histogrammes...

En règle générale, jusqu’au à la version 11g les histogrammes ne font pas de bon ménage avec les variables de liaison utilisées par les requêtes SQL.