Performance de ma query

**vinch999** · 27/08/2012, 10h57

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

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

**skuatamad** · 27/08/2012, 11h11

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

**vinch999** · 27/08/2012, 11h16

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.

**skuatamad** · 27/08/2012, 11h21

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 ?

**vinch999** · 27/08/2012, 11h40

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 :

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

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 :

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

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 :

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

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

**mnitu** · 27/08/2012, 11h58

Il y a deux choses qui interpellent toute de suite

dynamic sampling used FOR this statement

qui peut signifier le manque des statistiques à jour
et

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]

**vinch999** · 27/08/2012, 11h34

Version de la db:

Code :

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

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

Requête sql:

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

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

Performance de ma query

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager