Bonjour a tous,
J'ai besoin d'aide pour le tunning d'une requête :
Problème 1 : j'ai identifié 2 tables , il doit manquer des indexes mais il n'y a aucunes trace de l'appel de ces deux tables dans la requête
--> voir les lignes 21 & 22 de l'exécution plan ( en rouge)
Problème 2 : dans l'exécution plan , à la ligne 8 on trouve VW_NSO_1 : je n'ai trouvé aucune trace dans la base d'une table ou vue avec ce nom..... je ne sais pas ce que c'est.
--> voir ligne 8 de l'execution plan
---
voila la requete :
-- requete
---> les bind variables (vues dans v$sql_bind_capture)
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 SELECT * FROM V_VOL_TRONCON_JOUR WHERE TO_DATE(TO_CHAR(TVJ_DATEVOL, 'DD/MM/YYYY') || ' ' || TO_CHAR(VJO_HHMMDEP, 'HH24:MI'), 'DD/MM/YYYY HH24:MI') BETWEEN :B9 AND :B8 AND ( :B7 IS NULL OR TVJ_CODECLI = :B7 ) AND ( :B6 != 3 OR TVJ_CODECLI IN ( SELECT DCLI_CODECLI FROM DTCLIENT WHERE DCLI_CODEUTI = :B10 ) ) AND TVJ_CODECLI IN ( SELECT CLI_CODECLI FROM COMARM, VALJOURATEL WHERE VJA_DATE BETWEEN SUBSTR(:B9 , 1, 10) AND SUBSTR(:B8 , 1, 10) AND VJA_CODESIT = :B5 AND VJA_CODEATEL = :B11 AND VJA_KEYCLI = 'ZZZZZZZ' AND CLI_LCTSPE = '0' UNION SELECT CLI_CODECLI FROM COMARM, VALJOURATEL WHERE VJA_DATE BETWEEN SUBSTR(:B9 , 1, 10) AND SUBSTR(:B8 , 1, 10) AND VJA_CODESIT = :B5 AND VJA_CODEATEL = :B11 AND VJA_KEYCLI = CLI_CODECLI AND CLI_LCTSPE = '1' ) AND TVJ_ACCROCHAGE NOT IN ('P', 'D', 'A', 'V') AND TVJ_PDT_CODECLI IS NOT NULL AND TVJ_PDT_CODEPDT IS NOT NULL AND ( :B5 IN ( SELECT STVJ_CODESIT FROM SITE_TVJ WHERE STVJ_DATEVOL = TVJ_DATEVOL AND STVJ_CODECLI = TVJ_CODECLI AND STVJ_NOVOL = TVJ_NOVOL AND STVJ_NOTRONC = TVJ_NOTRONC AND STVJ_CODESIT = :B5 ) OR TVJ_CODESIT = :B5 OR TVJ_CODESIT IS NULL ) AND ( VJO_ANNUL IS NULL OR VJO_ANNUL <> 'A') AND ( 'Tout' IN (:B4 , :B3 , :B2 , :B1 ) OR NVL(TVJ_TVOL_CODETVOL, 'Sans type') IN (:B4 , :B3 , :B2 , :B1 ) ) ORDER BY TVJ_DATEVOL, TO_CHAR(VJO_HHMMDEP, 'HH24:MI')
NAME VALUE_STRING
-------------- --------------------------------------------------
:B1 ****
:B10 NOM
:B10 PRENOM
:B11 APC
:B11 DQ
:B11 SEVG
:B2 ****
:B3 comptes
:B3 ville
:B4 3ème niveau
:B4 CM2
:B5 BHD1
:B5 S2
:B6 1
:B6 2
:B7 FA
:B8 10/06/2009 23:59:00
:B8 11/06/2009 23:59:00
:B9 10/06/2009 00:00:00
:B9 11/06/2009 00:00:00
Execution 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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4799 (100)| | | 1 | SORT ORDER BY | | 1 | 160 | 4799 (2)| 00:00:58 | | 2 | FILTER | | | | | | | 3 | FILTER | | | | | | | 4 | FILTER | | | | | | | 5 | NESTED LOOPS OUTER | | 15 | 2400 | 4798 (2)| 00:00:58 | | 6 | HASH JOIN | | 15 | 1980 | 4783 (2)| 00:00:58 | | 7 | HASH JOIN | | 6022 | 546K| 3431 (2)| 00:00:42 | | 8 | VIEW | VW_NSO_1 | 3 | 12 | 1 (0)| 00:00:01 | | 9 | SORT UNIQUE | | | | | | | 10 | UNION-ALL | | | | | | | 11 | FILTER | | | | | | | 12 | MERGE JOIN CARTESIAN | | 11 | 286 | 5 (0)| 00:00:01 | | 13 | INDEX RANGE SCAN | VALJOURATEL_PK | 1 | 20 | 2 (0)| 00:00:01 | | 14 | BUFFER SORT | | 163 | 978 | 3 (0)| 00:00:01 | | 15 | TABLE ACCESS FULL | COMARM | 163 | 978 | 3 (0)| 00:00:01 | | 16 | FILTER | | | | | | | 17 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 | | 18 | INDEX RANGE SCAN | VALJOURATEL_PK | 1 | 20 | 2 (0)| 00:00:01 | | 19 | TABLE ACCESS BY INDEX ROWID| COMARM | 1 | 6 | 1 (0)| 00:00:01 | | 20 | INDEX UNIQUE SCAN | COMARM_PK | 1 | | 0 (0)| | | 21 | TABLE ACCESS FULL | TRONCON_VJ | 214K| 18M| 3428 (2)| 00:00:42 | | 22 | TABLE ACCESS FULL | VOL_JOUR | 965K| 35M| 1346 (3)| 00:00:17 | | 23 | TABLE ACCESS BY INDEX ROWID | VOL_BASE | 1 | 28 | 1 (0)| 00:00:01 | | 24 | INDEX UNIQUE SCAN | VOL_BASE_PK | 1 | | 0 (0)| | | 25 | INDEX UNIQUE SCAN | DTCLIENT_PK | 1 | 12 | 1 (0)| 00:00:01 | | 26 | FILTER | | | | | | | 27 | TABLE ACCESS BY INDEX ROWID | SITE_TVJ | 1 | 21 | 2 (0)| 00:00:01 | | 28 | INDEX RANGE SCAN | SITE_TVJ_PK | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------
Partager