Jointure : question de syntaxe ou performance

**stof** · 29/12/2011, 15h47

Bon, j'arrive au bout de mes capacités là; au cas où j'ai essayé de séparer des sous-requêtes de la façon suivante (pour bien suivre, mes 2 branches sont AAA et BBB) mais le problème c'est juste qu'à la fin je ne peux pas faire 2 outer join en même temps
and lr.LIGRPMIDT (+)= dataAAA.LIGRPMIDT
and lr.LIGRPMIDT (+)= dataBBB.LIGRPMIDT
-->c'est dommage car si t'as compris, le principe c'est d'avoir des lignes avec des données AAA renseignées et/ou BBB

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
select *
from
(
	WITH 
	STOCKAAA AS
    (select lr.LIGRPMIDT, sAAA.TITIDT, tAAA.AAATITIDT, tAAA.AAACOD2, ptAAA.PVDTITCOD, ptAAA.PVDTITLIB, crsAAA.CRSVLO, sAAA.PLACOTCOD
    from RPM r 
    join LIGRPM lr on lr.RPMIDT = r.RPMIDT
    join LIGRPMINVLIG lrlAAA on lrlAAA.LIGRPMIDT = lr.LIGRPMIDT
    join INVLIG ilAAA on (ilAAA.INVLIGIDT = lrlAAA.INVLIGIDT and ilAAA.PVDIDT = 2 and ilAAA.FLGLIQ = 'F') 
    join STK sAAA on sAAA.INVLIGIDT = ilAAA.INVLIGIDT 
    join PVDTIT ptAAA on (ptAAA.TITIDT = sAAA.TITIDT and ptAAA.PVDIDT = ilAAA.PVDIDT) 
    left join CRS crsAAA on crsAAA.CRSIDT = sAAA.CRSIDT 
    left join AAATIT tAAA on (tAAA.TITIDT = sAAA.TITIDT and tAAA.STALIGIDT in (select STALIGIDT from STALIG where PVDIDT = r.PVD_PVDIDT and STALIGCOD = case when sAAA.QTETIT > 0 then 'RECU' else 'VERSE' end))
    where r.DATVL = to_date('20/12/2011', 'DD/MM/YYYY')
    and ptAAA.PVDTITCOD = (select max(PVDTITCOD) from PVDTIT t1 where t1.PVDIDT = 2 and t1.TITIDT = ptAAA.TITIDT)
    ),
    stockBBB AS
    (select lr.LIGRPMIDT, sBBB.TITIDT, ptBBB.PVDTITCOD, ptBBB.PVDTITLIB, crsBBB.CRSVLO, sBBB.PLACOTCOD
    from RPM r 
    join LIGRPM lr on lr.RPMIDT = r.RPMIDT
    join LIGRPMINVLIG lrlBBB on lrlBBB.LIGRPMIDT = lr.LIGRPMIDT
    join INVLIG ilBBB on (ilBBB.INVLIGIDT = lrlBBB.INVLIGIDT and ilBBB.PVDIDT = r.PVD_PVDIDT and ilBBB.FLGLIQ = 'F') 
    join STK sBBB on sBBB.INVLIGIDT = ilBBB.INVLIGIDT 
    join PVDTIT ptBBB on (ptBBB.TITIDT = sBBB.TITIDT and ptBBB.PVDIDT = ilBBB.PVDIDT)
    left join CRS crsBBB on crsBBB.CRSIDT = sBBB.CRSIDT  
    where r.DATVL = to_date('20/12/2011', 'DD/MM/YYYY')
    and ptBBB.PVDTITCOD = (select max(PVDTITCOD) from PVDTIT t2 where t2.PVDIDT != 2 and t2.TITIDT = ptBBB.TITIDT)
    )
    select
    dataAAA.TITIDT as TITAAAIDT,
    dataBBB.TITIDT as TITBBBIDT, 
     nvl(( 
     	     select AAAREFEXT  
     	     from AAAREF aRef 
     	     where aRef.TITIDT = dataAAA.TITIDT 
     	     and aRef.AAATITIDT = dataAAA.AAATITIDT 
     	     and aRef.PERTPSDATDEB =(select max(t1.PERTPSDATDEB) from AAAREF t1 where t1.PVDIDT != 2 and t1.AAATITIDT = aRef.AAATITIDT and t1.PERTPSDATDEB <= R.DATVL) 
    	), nvl(dataAAA.AAACOD2,dataAAA.PVDTITCOD)) as CODAAA, 
    dataBBB.PVDTITCOD as CODBBB, 
    dataAAA.PVDTITLIB as LIBAAA, 
    dataBBB.PVDTITLIB as LIBBBB, 
    r.DATVL, 
    dataAAA.CRSVLO as CRSAAA, 
    dataBBB.CRSVLO as CRSBBB, 
    dataAAA.PLACOTCOD as PLACOTAAA, 
    dataBBB.PLACOTCOD as PLACOTBBB
    from
    RPM r,
    LIGRPM lr,
    STOCKAAA dataAAA,
    stockBBB dataBBB
    where r.DATVL = to_date('20/12/2011', 'DD/MM/YYYY')
    and r.RPMIDT = lr.RPMIDT
    and lr.LIGRPMIDT (+)= dataAAA.LIGRPMIDT
    and lr.LIGRPMIDT (+)= dataBBB.LIGRPMIDT
)

**Mohamed.Houri** · 29/12/2011, 16h08

Quel est votre problème ici :

(a) une réécriture de la requête
(b) ou une tentative d'amélioration de la performance

Si c'est le point (b) alors prenez le problème du bon bout et commencez par:

(1) poster votre requête entière (ce qui est déjà fait)
(2) poster le vrai explain plan tiré de la mémoire après exécution de la requête et contenant les Estimations faites par le CBO (Optimisateur d'Oracle) et accompagné de sa partie prédicate.

Pour le point (2) vous pouvez suivre l'exemple contenu dans cet article que je viens à l'instant de le publier (merci, vous m'avez motivé)

http://hourim.wordpress.com/?p=598&preview=true

**stof** · 29/12/2011, 16h19

Envoyé par Mohamed.Houri

Quel est votre problème ici :
(a) une réécriture de la requête
(b) ou une tentative d'amélioration de la performance

Evidemment si j'aimerais que ce soit joli (j'entends lisible), je veux surtout essayer d'optimiser.

Envoyé par Mohamed.Houri

Pour le point (2) vous pouvez suivre l'exemple contenu dans cet article (...)

Caramba, site encore bloqué (on se demande comment ils veulent qu'on bosse!)

La requête initiale de référence (juste réécrite avec AAA / BBB)

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
 select distinct
 sAAA.TITIDT as TITAAAIDT, 
 sBBB.TITIDT as TITBBBIDT, 
 nvl(( 
 	     select AAAREFEXT  
 	     from AAAREF aRef 
 	     where aRef.TITIDT = tAAA.TITIDT 
 	     and aRef.AAATITIDT = tAAA.AAATITIDT 
 	     and aRef.PERTPSDATDEB =(select max(t1.PERTPSDATDEB) from AAAREF t1 where t1.PVDIDT != 2 and t1.AAATITIDT = aRef.AAATITIDT and t1.PERTPSDATDEB <= R.DATVL) 
	), nvl(tAAA.AAACOD2,ptAAA.PVDTITCOD)) as CODAAA, 
 ptBBB.PVDTITCOD as CODBBB, 
 ptAAA.PVDTITLIB as LIBAAA, 
 ptBBB.PVDTITLIB as LIBBBB, 
 r.DATVL, 
 crsAAA.CRSVLO as CRSAAA, 
 crsBBB.CRSVLO as CRSBBB, 
 sAAA.PLACOTCOD as PLACOTAAA, 
 sBBB.PLACOTCOD as PLACOTBBB
 from RPM r 
 join LIGRPM lr on lr.RPMIDT = r.RPMIDT
 left join LIGRPMINVLIG lrlAAA on lrlAAA.LIGRPMIDT = lr.LIGRPMIDT
 left join LIGRPMINVLIG lrlBBB on lrlBBB.LIGRPMIDT = lr.LIGRPMIDT
 left join INVLIG ilAAA on (ilAAA.INVLIGIDT = lrlAAA.INVLIGIDT and ilAAA.PVDIDT = 2 and ilAAA.FLGLIQ = 'F') 
 left join INVLIG ilBBB on (ilBBB.INVLIGIDT = lrlBBB.INVLIGIDT and ilBBB.PVDIDT = r.PVD_PVDIDT and ilBBB.FLGLIQ = 'F')
 left join STK sAAA on sAAA.INVLIGIDT = ilAAA.INVLIGIDT 
 left join STK sBBB on sBBB.INVLIGIDT = ilBBB.INVLIGIDT 
 left join PVDTIT ptAAA on (ptAAA.TITIDT = sAAA.TITIDT and ptAAA.PVDIDT = ilAAA.PVDIDT) 
 left join PVDTIT ptBBB on (ptBBB.TITIDT = sBBB.TITIDT and ptBBB.PVDIDT = ilBBB.PVDIDT) 
 left join AAATIT tAAA on (tAAA.TITIDT = sAAA.TITIDT and tAAA.STALIGIDT in (select STALIGIDT from STALIG where PVDIDT = r.PVD_PVDIDT and STALIGCOD = case when sAAA.QTETIT > 0 then 'RECU' else 'VERSE' end))
 left join CRS crsAAA on crsAAA.CRSIDT = sAAA.CRSIDT 
 left join CRS crsBBB on crsBBB.CRSIDT = sBBB.CRSIDT 
 where r.DATVL = to_date('20/12/2011', 'DD/MM/YYYY')
 and (ptAAA.PVDTITCOD is null or ptAAA.PVDTITCOD = (select max(PVDTITCOD) from PVDTIT t1 where t1.PVDIDT = 2 and t1.TITIDT = ptAAA.TITIDT)) 
 and (ptBBB.PVDTITCOD is null or ptBBB.PVDTITCOD = (select max(PVDTITCOD) from PVDTIT t2 where t2.PVDIDT != 2 and t2.TITIDT = ptBBB.TITIDT)) 
 and (crsAAA.CRSVLO is not null or crsBBB.CRSVLO is not null)

Plan d'exécution :

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
PLAN_TABLE_OUTPUT                                                                                                     
[CHAR                                                                                                                ]
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 2085107805                                                                                           
 
--------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                                   | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | 
--------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                            |               |     1 |   178 |       | 54046   (4)| 00:10:49 | 
|*  1 |  FILTER                                     |               |       |       |       |            |          | 
|*  2 |   TABLE ACCESS FULL                         | AAAREF        |     1 |    32 |       |    20   (5)| 00:00:01 | 
|   3 |   SORT AGGREGATE                            |               |     1 |    16 |       |            |          | 
|*  4 |    TABLE ACCESS FULL                        | AAAREF        |     1 |    16 |       |    20   (5)| 00:00:01 | 
|   5 |  HASH UNIQUE                                |               |     1 |   178 |       | 54046   (4)| 00:10:49 | 
|*  6 |   FILTER                                    |               |       |       |       |            |          | 
|   7 |    NESTED LOOPS OUTER                       |               |     1 |   178 |       | 54045   (4)| 00:10:49 | 
|*  8 |     FILTER                                  |               |       |       |       |            |          | 
|   9 |      NESTED LOOPS OUTER                     |               |     1 |   163 |       | 54043   (4)| 00:10:49 | 
|  10 |       NESTED LOOPS OUTER                    |               |     1 |   152 |       | 54041   (4)| 00:10:49 | 
|* 11 |        HASH JOIN OUTER                      |               |     1 |   141 |       | 54039   (4)| 00:10:49 | 
|* 12 |         HASH JOIN RIGHT OUTER               |               |     1 |   100 |  1784K| 53980   (4)| 00:10:48 | 
|  13 |          TABLE ACCESS FULL                  | PVDTIT        | 34441 |  1378K|       |    58   (2)| 00:00:01 | 
|  14 |          VIEW                               |               | 34419 |  1983K|       | 53716   (4)| 00:10:45 | 
|* 15 |           HASH JOIN OUTER                   |               | 34419 |  3966K|  3560K| 53716   (4)| 00:10:45 | 
|* 16 |            HASH JOIN OUTER                  |               | 34022 |  3156K|  2928K| 39374   (4)| 00:07:53 | 
|* 17 |             HASH JOIN OUTER                 |               | 33630 |  2528K|  2568K| 25983   (4)| 00:05:12 | 
|  18 |              VIEW                           |               | 33630 |  2167K|       | 14369   (4)| 00:02:53 | 
|* 19 |               HASH JOIN OUTER               |               | 33630 |  2167K|  1272K| 14369   (4)| 00:02:53 | 
|* 20 |                HASH JOIN OUTER              |               | 19075 |  1043K|  1064K| 11618   (4)| 00:02:20 | 
|* 21 |                 HASH JOIN OUTER             |               | 19075 |   838K|       |  2029   (4)| 00:00:25 | 
|  22 |                  TABLE ACCESS BY INDEX ROWID| LIGRPM        |    28 |   308 |       |     3   (0)| 00:00:01 | 
|  23 |                   NESTED LOOPS              |               | 10820 |   369K|       |  1120   (1)| 00:00:14 | 
|* 24 |                    TABLE ACCESS FULL        | RPM           |   382 |  9168 |       |   159   (3)| 00:00:02 | 
|* 25 |                    INDEX RANGE SCAN         | RPMLIGRPM_FK  |    28 |       |       |     2   (0)| 00:00:01 | 
|  26 |                  TABLE ACCESS FULL          | LIGRPMINVLIG  |  1688K|    16M|       |   886   (6)| 00:00:11 | 
|* 27 |                 TABLE ACCESS FULL           | INVLIG        |   838K|  9007K|       |  8602   (4)| 00:01:44 | 
|  28 |                TABLE ACCESS FULL            | LIGRPMINVLIG  |  1688K|    16M|       |   886   (6)| 00:00:11 | 
|* 29 |              TABLE ACCESS FULL              | INVLIG        |  2515K|    26M|       |  8686   (5)| 00:01:45 | 
|  30 |             TABLE ACCESS FULL               | STK           |  3890K|    66M|       |  7624   (4)| 00:01:32 | 
|  31 |            TABLE ACCESS FULL                | STK           |  3890K|    85M|       |  7624   (4)| 00:01:32 | 
|  32 |         TABLE ACCESS FULL                   | PVDTIT        | 34441 |  1378K|       |    58   (2)| 00:00:01 | 
|  33 |        TABLE ACCESS BY INDEX ROWID          | CRS           |     1 |    11 |       |     2   (0)| 00:00:01 | 
|* 34 |         INDEX UNIQUE SCAN                   | PK_CRS        |     1 |       |       |     1   (0)| 00:00:01 | 
|  35 |       TABLE ACCESS BY INDEX ROWID           | CRS           |     1 |    11 |       |     2   (0)| 00:00:01 | 
|* 36 |        INDEX UNIQUE SCAN                    | PK_CRS        |     1 |       |       |     1   (0)| 00:00:01 | 
|  37 |     VIEW                                    |               |     1 |    15 |       |     2   (0)| 00:00:01 | 
|  38 |      TABLE ACCESS BY INDEX ROWID            | AAATIT        |     1 |    18 |       |     2   (0)| 00:00:01 | 
|  39 |       NESTED LOOPS                          |               |     1 |    31 |       |     4   (0)| 00:00:01 | 
|* 40 |        TABLE ACCESS BY INDEX ROWID          | STALIG        |     1 |    13 |       |     2   (0)| 00:00:01 | 
|* 41 |         INDEX RANGE SCAN                    | STALIGPVD_FK  |    47 |       |       |     0   (0)| 00:00:01 | 
|* 42 |        INDEX RANGE SCAN                     | STALIG_UNIQUE |     1 |       |       |     1   (0)| 00:00:01 | 
|  43 |    SORT AGGREGATE                           |               |     1 |    19 |       |            |          | 
|* 44 |     TABLE ACCESS FULL                       | PVDTIT        |     1 |    19 |       |    58   (2)| 00:00:01 | 
|  45 |      SORT AGGREGATE                         |               |     1 |    19 |       |            |          | 
|* 46 |       TABLE ACCESS FULL                     | PVDTIT        |     1 |    19 |       |    58   (2)| 00:00:01 | 
--------------------------------------------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id):                                                                   
---------------------------------------------------                                                                   
 
   1 - filter("AREF"."PERTPSDATDEB"= (SELECT MAX("T1"."PERTPSDATDEB") FROM "REFVL"."AAAREF" "T1" WHERE                
              "T1"."AAATITIDT"=:B1 AND "T1"."PERTPSDATDEB"<=:B2 AND "T1"."PVDIDT"<>2))                                
   2 - filter("AREF"."AAATITIDT"=:B1 AND "AREF"."TITIDT"=:B2)                                                         
   4 - filter("T1"."AAATITIDT"=:B1 AND "T1"."PERTPSDATDEB"<=:B2 AND "T1"."PVDIDT"<>2)                                 
   6 - filter(("PTAAA"."PVDTITCOD" IS NULL OR "PTAAA"."PVDTITCOD"= (SELECT MAX("PVDTITCOD") FROM                      
              "REFVL"."PVDTIT" "T1" WHERE "T1"."TITIDT"=:B1 AND "T1"."PVDIDT"=2)) AND ("PTBBB"."PVDTITCOD" IS NULL OR 
              "PTBBB"."PVDTITCOD"= (SELECT MAX("PVDTITCOD") FROM "REFVL"."PVDTIT" "T2" WHERE "T2"."TITIDT"=:B2 AND    
              "T2"."PVDIDT"<>2)))                                                                                     
   8 - filter("CRSAAA"."CRSVLO" IS NOT NULL OR "CRSBBB"."CRSVLO" IS NOT NULL)                                         
  11 - access("PTBBB"."PVDIDT"(+)="from$_subquery$_017"."PVDIDT" AND                                                  
              "PTBBB"."TITIDT"(+)="from$_subquery$_017"."TITIDT")                                                     
  12 - access("PTAAA"."PVDIDT"(+)="ILAAA"."PVDIDT" AND "PTAAA"."TITIDT"(+)="SAAA"."TITIDT")                           
  15 - access("SAAA"."INVLIGIDT"(+)="from$_subquery$_011"."INVLIGIDT")                                                
  16 - access("SBBB"."INVLIGIDT"(+)="ILBBB"."INVLIGIDT")                                                              
  17 - access("ILBBB"."PVDIDT"(+)="R"."PVD_PVDIDT" AND "ILBBB"."INVLIGIDT"(+)="LRLBBB"."INVLIGIDT")                   
  19 - access("LRLBBB"."LIGRPMIDT"(+)="LR"."LIGRPMIDT")                                                               
  20 - access("ILAAA"."INVLIGIDT"(+)="LRLAAA"."INVLIGIDT")                                                            
  21 - access("LRLAAA"."LIGRPMIDT"(+)="LR"."LIGRPMIDT")                                                               
  24 - filter("R"."DATVL"=TO_DATE(' 2011-12-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                                  
  25 - access("LR"."RPMIDT"="R"."RPMIDT")                                                                             
  27 - filter("ILAAA"."PVDIDT"(+)=2 AND "ILAAA"."FLGLIQ"(+)='F')                                                      
  29 - filter("ILBBB"."FLGLIQ"(+)='F')                                                                                
  34 - access("CRSAAA"."CRSIDT"(+)="from$_subquery$_017"."QCSJ_C000000001700022")                                     
  36 - access("CRSBBB"."CRSIDT"(+)="from$_subquery$_017"."QCSJ_C000000001700023")                                     
  40 - filter("STALIGCOD"=CASE  WHEN ("from$_subquery$_017"."QCSJ_C000000001700004">0) THEN 'RECU' ELSE               
              'VERSE' END )                                                                                           
  41 - access("PVDIDT"="from$_subquery$_017"."PVD_PVDIDT")                                                            
  42 - access("TAAA"."TITIDT"="from$_subquery$_017"."QCSJ_C000000001500001" AND                                       
              "TAAA"."STALIGIDT"="STALIGIDT")                                                                         
       filter("TAAA"."STALIGIDT" IS NOT NULL)                                                                         
  44 - filter("T1"."TITIDT"=:B1 AND "T1"."PVDIDT"=2)                                                                  
  46 - filter("T2"."TITIDT"=:B1 AND "T2"."PVDIDT"<>2)                                                                 
 
89 rows affected by this transaction.

**Waldar** · 29/12/2011, 16h36

Oui je pense avoir compris l'idée, et justement pour optimiser ce genre de requête, si c'est possible (ce dont je ne peux pas être certain ici sans données), c'est de traiter les deux branches en une seule fois en créant la rupture au niveau du select en utilisant la fonction CASE.

On voit bien dans votre plan d'exécution que beaucoup de temps est utilisé pour lire deux fois les mêmes tables, si on arrive à synthétiser les deux branches en une (technique du pivot, faites une recherche sur le forum vous trouverez pas mal d'exemples) on divise quasiment la durée par deux.

**Mohamed.Houri** · 29/12/2011, 16h41

C'est très bien comme ça. Pourriez-vous re-faire ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
set linesize 250
set pagesize 250
select /*+ gather_plan_statistics */
distinct
 sAAA.TITIDT as TITAAAIDT, 
 sBBB.TITIDT as TITBBBIDT, 
 nvl(( 
......
 and (crsAAA.CRSVLO is not null or crsBBB.CRSVLO is not null);

et enfin immediatement après la fin de votre requête faites ceci:

Code :

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

1
2
 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

et postez le plan d'exécution obtenu par le select précédent.

**stof** · 29/12/2011, 17h09

SET linesize 250
ou
SET pagesize 250

-->ORA-00922: missing or invalid option

Envoyé par Waldar

technique du pivot

C'est passer d'un affichage colonnes à un affichage lignes, c'est ça?

**Mohamed.Houri** · 29/12/2011, 17h16

Envoyé par stof

SET linesize 250
ou
SET pagesize 250

-->ORA-00922: missing or invalid option

Code :

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

1
2
3
 
mhouri.world> set linesize 250
mhouri.world> set pagesize 250

En attendant votre explain plan, je vois que vous appliquez le distinct sur un select, est ce que votre requête réecrite comme suit redonne le même 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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
select x.*, nvl(( select aaarefext  
                   from aaaref aref 
                   where aref.titidt = taaa.titidt 
                   and aref.aaatitidt = taaa.aaatitidt 
                   and aref.pertpsdatdeb =(select max(t1.pertpsdatdeb) 
                   from aaaref t1 where t1.pvdidt != 2 
                   and t1.aaatitidt = aref.aaatitidt and t1.pertpsdatdeb <= r.datvl) 
                  ), nvl(taaa.aaacod2,ptaaa.pvdtitcod)) as codaaa
from 
(select 
distinct
 saaa.titidt as titaaaidt, 
 sbbb.titidt as titbbbidt,  
 ptbbb.pvdtitcod as codbbb, 
 ptaaa.pvdtitlib as libaaa, 
 ptbbb.pvdtitlib as libbbb, 
 r.datvl, 
 crsaaa.crsvlo as crsaaa, 
 crsbbb.crsvlo as crsbbb, 
 saaa.placotcod as placotaaa, 
 sbbb.placotcod as placotbbb
 from rpm r 
 join ligrpm lr on lr.rpmidt = r.rpmidt
 left join ligrpminvlig lrlaaa on lrlaaa.ligrpmidt = lr.ligrpmidt
 left join ligrpminvlig lrlbbb on lrlbbb.ligrpmidt = lr.ligrpmidt
 left join invlig ilaaa on (ilaaa.invligidt = lrlaaa.invligidt and ilaaa.pvdidt = 2 and ilaaa.flgliq = 'F') 
 left join invlig ilbbb on (ilbbb.invligidt = lrlbbb.invligidt and ilbbb.pvdidt = r.pvd_pvdidt and ilbbb.flgliq = 'F')
 left join stk saaa on saaa.invligidt = ilaaa.invligidt 
 left join stk sbbb on sbbb.invligidt = ilbbb.invligidt 
 left join pvdtit ptaaa on (ptaaa.titidt = saaa.titidt and ptaaa.pvdidt = ilaaa.pvdidt) 
 left join pvdtit ptbbb on (ptbbb.titidt = sbbb.titidt and ptbbb.pvdidt = ilbbb.pvdidt) 
 left join aaatit taaa on (taaa.titidt = saaa.titidt and taaa.staligidt in (select staligidt from stalig where pvdidt = r.pvd_pvdidt and staligcod = case when saaa.qtetit > 0 then 'RECU' else 'VERSE' end))
 left join crs crsaaa on crsaaa.crsidt = saaa.crsidt 
 left join crs crsbbb on crsbbb.crsidt = sbbb.crsidt 
 where r.datvl = to_date('20/12/2011', 'DD/MM/YYYY')
 and (ptaaa.pvdtitcod is null or ptaaa.pvdtitcod = (select max(pvdtitcod) from pvdtit t1 where t1.pvdidt = 2 and t1.titidt = ptaaa.titidt)) 
 and (ptbbb.pvdtitcod is null or ptbbb.pvdtitcod = (select max(pvdtitcod) from pvdtit t2 where t2.pvdidt != 2 and t2.titidt = ptbbb.titidt)) 
 and (crsaaa.crsvlo is not null or crsbbb.crsvlo is not null)
 ) x

**stof** · 29/12/2011, 17h26

set linesize 250
-->ne marche pas chez moi

Sinon ta requête renvoie le même nombre de lignes (mais c'est la même a priori sauf remarque ci-dessous)

Mais on ne peut sortir

Code :

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

1
2
3
4
nvl(( SELECT aaarefext  
                   FROM aaaref aref 
                   ...
                  ), nvl(taaa.aaacod2,ptaaa.pvdtitcod)) AS codaaa

de l'intérieur de la requête à cause des références aux tables taaa et ptaaa

**Mohamed.Houri** · 30/12/2011, 09h02

Votre requête sans la clause distinct génère 976 lignes contre 754 lignes lorsque la clause distinct est utilisée. Ce qui veut dire que si vous arrivez à faire sortir le nvl du select global vous arriverez à éliminer 222 appels à ces différents selects qui se trouvent dans le nvl. Surtout que les "inputs" de ce nvl sont déjà soumis à la clause distinct.

Pour cela vous pouvez réécrire votre requête comme suit:

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
 
 select
        x.titaaaidt, 
        x.titbbbidt, 
        x.codbbb, 
        x.libaaa, 
        x.libbbb, 
        x.r_datvl, 
        x. crsaaa, 
        x.crsbbb, 
        x.placotaaa, 
        x. placotbbb
       ,nvl( 
             (select 
	         aaarefext  
 	 from   aaaref aref 
 	 where aref.titidt          = x.taaa_titidt 
 	 and   aref.aaatitidt       = x.taaa_aaatitidt 
 	 and   aref.pertpsdatdeb = (select 
		                           max(t1.pertpsdatdeb) 
		                       from   aaaref t1 
                                                  where t1.pvdidt  != 2 
                                                  and    t1.aaatitidt= aref.aaatitidt 
                                                  and   t1.pertpsdatdeb <= x.r_datvl	
		                        ) 
	         ), nvl(x.taaa_aaacod2,x.ptaaa_pvdtitcod)
           ) as codaaa, 
 from 
     (select 
	 distinct
	 saaa.titidt as titaaaidt, 
	 sbbb.titidt as titbbbidt, 
	 ptbbb.pvdtitcod as codbbb, 
	 ptbbb.pvdtitlib as libbbb
	 ptaaa.pvdtitlib as libaaa, 		   
	 ptbbb.pvdtitcod as ptaaa_pvdtitcod, -- ici 
	 r.datvl         as r_datvl,         -- ici alias modifié
	 crsaaa.crsvlo as crsaaa, 
	 crsbbb.crsvlo as crsbbb, 
	 saaa.placotcod as placotaaa, 
	 sbbb.placotcod as placotbbb,
	 taaa.titidt as taaa_titidt,           -- ici
	 taaa.aaatitidt as taaa_aaatitidt,  -- ici
	 taaa.aaacod2 as taaa_aaacod2,  -- ici   
    from 
	 RPM R 
	 join LIGRPM LR on   LR.RPMIDT = R.RPMIDT
	 left join LIGRPMINVLIG LRLAAA on LRLAAA.LIGRPMIDT = LR.LIGRPMIDT
	 left join LIGRPMINVLIG LRLBBB on LRLBBB.LIGRPMIDT = LR.LIGRPMIDT
	 left join INVLIG ILAAA on (ILAAA.INVLIGIDT = LRLAAA.INVLIGIDT and ILAAA.PVDIDT = 2 and ILAAA.FLGLIQ = 'F') 
	 left join INVLIG ILBBB on (ILBBB.INVLIGIDT = LRLBBB.INVLIGIDT and ILBBB.PVDIDT = R.PVD_PVDIDT and ILBBB.FLGLIQ = 'F')
	 left join STK SAAA on SAAA.INVLIGIDT = ILAAA.INVLIGIDT 
	 left join STK SBBB on SBBB.INVLIGIDT = ILBBB.INVLIGIDT 
	 left join PVDTIT PTAAA on (PTAAA.TITIDT = SAAA.TITIDT and PTAAA.PVDIDT = ILAAA.PVDIDT) 
	 left join PVDTIT PTBBB on (PTBBB.TITIDT = SBBB.TITIDT and PTBBB.PVDIDT = ILBBB.PVDIDT) 
	 left join AAATIT TAAA on (TAAA.TITIDT = SAAA.TITIDT and TAAA.STALIGIDT in (select STALIGIDT from STALIG where PVDIDT = R.PVD_PVDIDT and STALIGCOD = case when SAAA.QTETIT > 0 then 'RECU' else 'VERSE' end))
	left join CRS CRSAAA on CRSAAA.CRSIDT = SAAA.CRSIDT 
	left join CRS CRSBBB on CRSBBB.CRSIDT = SBBB.CRSIDT 
where 
             R.DATVL = TO_DATE('20/12/2011', 'DD/MM/YYYY')
and       (PTAAA.PVDTITCOD is null or PTAAA.PVDTITCOD = (select max  
         (PVDTITCOD) from PVDTIT T1 where T1.PVDIDT = 2 and T1.TITIDT =
 PTAAA.TITIDT)) 
and (PTBBB.PVDTITCOD is null or PTBBB.PVDTITCOD = (select max(PVDTITCOD) from PVDTIT T2 where T2.PVDIDT != 2 and T2.TITIDT = PTBBB.TITIDT)) 
and (CRSAAA.CRSVLO is not null or CRSBBB.CRSVLO is not null)
 ) x

J'espère que la requête fonctionnera correctement.

**stof** · 30/12/2011, 10h09

Envoyé par Mohamed.Houri

J'espère que la requête fonctionnera correctement.

Bien vu; malheureusement on ne gagne que des pouillèmes

:
10"46 au lieu de 10"49 sur le jeu de données dont j'avais montré l'explain plan plus haut.
25"32 au lieu de 25"35 sur un jeu de données un peu plus conséquent.

**Mohamed.Houri** · 30/12/2011, 10h37

Envoyé par stof

Bien vu; malheureusement on ne gagne que des pouillèmes

:
10"46 au lieu de 10"49 sur le jeu de données dont j'avais montré l'explain plan plus haut.
25"32 au lieu de 25"35 sur un jeu de données un peu plus conséquent.

Alors, donnez le plan d'execution comme indiqué dans une de mes précédentes interventions (même si le set linesize ne fonctionne pas chez vous). On saura alors exactement quelle opération vaudra la peine d'être améliorée

**stof** · 30/12/2011, 10h44

Envoyé par Mohamed.Houri

Alors, donnez le plan d'execution comme indiqué dans une de mes précédentes interventions

J'avais donné le plan d'exécution de ma requête (cf message plus haut).
Voici le nouveau avec ta modification :

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
PLAN_TABLE_OUTPUT                                                                                                         
[CHAR                                                                                                                    ]
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2923835282                                                                                               
 
----------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                                    | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    
----------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                             |               |     1 |   239 |       | 53815   (4)| 00:10:46 |    
|*  1 |  FILTER                                      |               |       |       |       |            |          |    
|*  2 |   TABLE ACCESS FULL                          | AAAREF        |     1 |    32 |       |    20   (5)| 00:00:01 |    
|   3 |   SORT AGGREGATE                             |               |     1 |    16 |       |            |          |    
|*  4 |    TABLE ACCESS FULL                         | AAAREF        |     1 |    16 |       |    20   (5)| 00:00:01 |    
|   5 |  VIEW                                        |               |     1 |   239 |       | 53815   (4)| 00:10:46 |    
|   6 |   HASH UNIQUE                                |               |     1 |   180 |       | 53815   (4)| 00:10:46 |    
|*  7 |    FILTER                                    |               |       |       |       |            |          |    
|   8 |     NESTED LOOPS OUTER                       |               |     1 |   180 |       | 53814   (4)| 00:10:46 |    
|*  9 |      FILTER                                  |               |       |       |       |            |          |    
|  10 |       NESTED LOOPS OUTER                     |               |     1 |   165 |       | 53812   (4)| 00:10:46 |    
|  11 |        NESTED LOOPS OUTER                    |               |     1 |   154 |       | 53810   (4)| 00:10:46 |    
|* 12 |         HASH JOIN OUTER                      |               |     1 |   143 |       | 53808   (4)| 00:10:46 |    
|* 13 |          HASH JOIN RIGHT OUTER               |               |     1 |   102 |  1792K| 53748   (4)| 00:10:45 |    
|  14 |           TABLE ACCESS FULL                  | PVDTIT        | 34526 |  1382K|       |    58   (2)| 00:00:01 |    
|  15 |           VIEW                               |               | 34419 |  2050K|       | 53482   (4)| 00:10:42 |    
|* 16 |            HASH JOIN OUTER                   |               | 34419 |  2789K|  2400K| 53482   (4)| 00:10:42 |    
|* 17 |             HASH JOIN OUTER                  |               | 34022 |  1993K|  1776K| 39216   (4)| 00:07:51 |    
|* 18 |              HASH JOIN OUTER                 |               | 33630 |  1379K|  1416K| 25901   (4)| 00:05:11 |    
|  19 |               VIEW                           |               | 33630 |  1018K|       | 14356   (4)| 00:02:53 |    
|* 20 |                HASH JOIN OUTER               |               | 33630 |  2167K|  1272K| 14356   (4)| 00:02:53 |    
|* 21 |                 HASH JOIN OUTER              |               | 19075 |  1043K|  1064K| 11609   (4)| 00:02:20 |    
|* 22 |                  HASH JOIN OUTER             |               | 19075 |   838K|       |  2024   (4)| 00:00:25 |    
|  23 |                   TABLE ACCESS BY INDEX ROWID| LIGRPM        |    28 |   308 |       |     3   (0)| 00:00:01 |    
|  24 |                    NESTED LOOPS              |               | 10820 |   369K|       |  1120   (1)| 00:00:14 |    
|* 25 |                     TABLE ACCESS FULL        | RPM           |   382 |  9168 |       |   159   (3)| 00:00:02 |    
|* 26 |                     INDEX RANGE SCAN         | RPMLIGRPM_FK  |    28 |       |       |     2   (0)| 00:00:01 |    
|  27 |                   TABLE ACCESS FULL          | LIGRPMINVLIG  |  1688K|    16M|       |   882   (5)| 00:00:11 |    
|* 28 |                  TABLE ACCESS FULL           | INVLIG        |   838K|  9007K|       |  8597   (4)| 00:01:44 |    
|  29 |                 TABLE ACCESS FULL            | LIGRPMINVLIG  |  1688K|    16M|       |   882   (5)| 00:00:11 |    
|* 30 |               TABLE ACCESS FULL              | INVLIG        |  2515K|    26M|       |  8673   (5)| 00:01:45 |    
|  31 |              TABLE ACCESS FULL               | STK           |  3890K|    66M|       |  7604   (3)| 00:01:32 |    
|  32 |             TABLE ACCESS FULL                | STK           |  3890K|    85M|       |  7604   (3)| 00:01:32 |    
|  33 |          TABLE ACCESS FULL                   | PVDTIT        | 34526 |  1382K|       |    58   (2)| 00:00:01 |    
|  34 |         TABLE ACCESS BY INDEX ROWID          | CRS           |     1 |    11 |       |     2   (0)| 00:00:01 |    
|* 35 |          INDEX UNIQUE SCAN                   | PK_CRS        |     1 |       |       |     1   (0)| 00:00:01 |    
|  36 |        TABLE ACCESS BY INDEX ROWID           | CRS           |     1 |    11 |       |     2   (0)| 00:00:01 |    
|* 37 |         INDEX UNIQUE SCAN                    | PK_CRS        |     1 |       |       |     1   (0)| 00:00:01 |    
|  38 |      VIEW                                    |               |     1 |    15 |       |     2   (0)| 00:00:01 |    
|  39 |       NESTED LOOPS                           |               |     1 |    31 |       |     4   (0)| 00:00:01 |    
|  40 |        TABLE ACCESS BY INDEX ROWID           | AAATIT        |     1 |    18 |       |     3   (0)| 00:00:01 |    
|* 41 |         INDEX RANGE SCAN                     | STALIG_UNIQUE |     1 |       |       |     2   (0)| 00:00:01 |    
|* 42 |        TABLE ACCESS BY INDEX ROWID           | STALIG        |     1 |    13 |       |     1   (0)| 00:00:01 |    
|* 43 |         INDEX UNIQUE SCAN                    | PK_STALIG     |     1 |       |       |     0   (0)| 00:00:01 |    
|  44 |     SORT AGGREGATE                           |               |     1 |    19 |       |            |          |    
|* 45 |      TABLE ACCESS FULL                       | PVDTIT        |     1 |    19 |       |    58   (2)| 00:00:01 |    
|  46 |       SORT AGGREGATE                         |               |     1 |    19 |       |            |          |    
|* 47 |        TABLE ACCESS FULL                     | PVDTIT        |     1 |    19 |       |    58   (2)| 00:00:01 |    
----------------------------------------------------------------------------------------------------------------------    
 
Predicate Information (identified by operation id):                                                                       
---------------------------------------------------                                                                       
 
   1 - filter("AREF"."PERTPSDATDEB"= (SELECT /*+ */ MAX("T1"."PERTPSDATDEB") FROM "REFVL"."AAAREF" "T1" WHERE             
              "T1"."AAATITIDT"=:B1 AND "T1"."PERTPSDATDEB"<=:B2 AND "T1"."PVDIDT"<>2))                                    
   2 - filter("AREF"."AAATITIDT"=:B1 AND "AREF"."TITIDT"=:B2)                                                             
   4 - filter("T1"."AAATITIDT"=:B1 AND "T1"."PERTPSDATDEB"<=:B2 AND "T1"."PVDIDT"<>2)                                     
   7 - filter(("PTAAA"."PVDTITCOD" IS NULL OR "PTAAA"."PVDTITCOD"= (SELECT /*+ */ MAX("PVDTITCOD") FROM                   
              "REFVL"."PVDTIT" "T1" WHERE "T1"."TITIDT"=:B1 AND "T1"."PVDIDT"=2)) AND ("PTBBB"."PVDTITCOD" IS NULL OR     
              "PTBBB"."PVDTITCOD"= (SELECT /*+ */ MAX("PVDTITCOD") FROM "REFVL"."PVDTIT" "T2" WHERE "T2"."TITIDT"=:B2 AND 
              "T2"."PVDIDT"<>2)))                                                                                         
   9 - filter("CRSAAA"."CRSVLO" IS NOT NULL OR "CRSBBB"."CRSVLO" IS NOT NULL)                                             
  12 - access("PTBBB"."PVDIDT"(+)="from$_subquery$_018"."PVDIDT" AND                                                      
              "PTBBB"."TITIDT"(+)="from$_subquery$_018"."TITIDT")                                                         
  13 - access("PTAAA"."PVDIDT"(+)="ILAAA"."PVDIDT" AND "PTAAA"."TITIDT"(+)="SAAA"."TITIDT")                               
  16 - access("SAAA"."INVLIGIDT"(+)="from$_subquery$_012"."INVLIGIDT")                                                    
  17 - access("SBBB"."INVLIGIDT"(+)="ILBBB"."INVLIGIDT")                                                                  
  18 - access("ILBBB"."PVDIDT"(+)="R"."PVD_PVDIDT" AND "ILBBB"."INVLIGIDT"(+)="LRLBBB"."INVLIGIDT")                       
  20 - access("LRLBBB"."LIGRPMIDT"(+)="LR"."LIGRPMIDT")                                                                   
  21 - access("ILAAA"."INVLIGIDT"(+)="LRLAAA"."INVLIGIDT")                                                                
  22 - access("LRLAAA"."LIGRPMIDT"(+)="LR"."LIGRPMIDT")                                                                   
  25 - filter("R"."DATVL"=TO_DATE(' 2011-12-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                                      
  26 - access("LR"."RPMIDT"="R"."RPMIDT")                                                                                 
  28 - filter("ILAAA"."PVDIDT"(+)=2 AND "ILAAA"."FLGLIQ"(+)='F')                                                          
  30 - filter("ILBBB"."FLGLIQ"(+)='F')                                                                                    
  35 - access("CRSAAA"."CRSIDT"(+)="from$_subquery$_018"."QCSJ_C000000001800006")                                         
  37 - access("CRSBBB"."CRSIDT"(+)="from$_subquery$_018"."QCSJ_C000000001800007")                                         
  41 - access("TAAA"."TITIDT"="from$_subquery$_018"."QCSJ_C000000001600001")                                              
       filter("TAAA"."STALIGIDT" IS NOT NULL)                                                                             
  42 - filter("PVDIDT"="from$_subquery$_018"."PVD_PVDIDT" AND "STALIGCOD"=CASE  WHEN                                      
              ("from$_subquery$_018"."QCSJ_C000000001800002">0) THEN 'RECU' ELSE 'VERSE' END )                            
  43 - access("TAAA"."STALIGIDT"="STALIGIDT")                                                                             
  45 - filter("T1"."TITIDT"=:B1 AND "T1"."PVDIDT"=2)                                                                      
  47 - filter("T2"."TITIDT"=:B1 AND "T2"."PVDIDT"<>2)

**Mohamed.Houri** · 30/12/2011, 11h20

Il faut procéder exactement comme dans l'exemple ci-dessous

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
 
 
mhouri.world> select /*+ gather_plan_statistics */
  2  ename, sal
  3  from emp
  4  where deptno = 20;
 
ENAME             SAL                                                           
---------- ----------                                                           
allen             815                                                           
jones            2975                                                           
scott            3000                                                           
adams            1100                                                           
ford             3000                                                           
 
mhouri.world> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  1qhgb20f8adgk, child number 0                                           
-------------------------------------                                           
select /*+ gather_plan_statistics */ ename, sal from emp where deptno = 20      
 
Plan hash value: 2872589290                                                     
 
---------------------------------------------------------------------------------------------                                                                                                                                                   
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                                                                                                                                  
---------------------------------------------------------------------------------------------                                                                                                                                                   
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      5 |      5 |00:00:00.01 |      16 |     14 |                                                                                                                                                   
---------------------------------------------------------------------------------------------      
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
 
   1 - filter("DEPTNO"=20)                                                      
 
 
17 rows selected.

**stof** · 30/12/2011, 11h23

Envoyé par Mohamed.Houri

Il faut procéder exactement comme dans l'exemple ci-dessous

Ah OK; désolé, j'avais pas accès à ton site.

Par contre, bad news : User has no SELECT privilege on V$SESSION
(je contacte mes DBA mais ça va prendre du temps)

EDIT : j'ai désormais les droits...mais doit manquer un paramétrage de cache plus large sans doute...

Code :

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

1
2
3
4
5
6
7
8
9
10
PLAN_TABLE_OUTPUT                                                                   
[CHAR                                                                              ]
------------------------------------------------------------------------------------
SQL_ID  7vw7tt90kadw1, child number 0                                               
 
begin dbms_output.enable ( :buffer_size ); end;                                     
 
NOTE: cannot fetch plan for SQL_ID: 7vw7tt90kadw1, CHILD_NUMBER: 0                  
      Please verify value of SQL_ID and CHILD_NUMBER;                               
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

EDIT2 : apparemment c'est une question de requêteur parce que mon DBA y arrive avec TOAD (que je n'ai pas...)

**stof** · 30/12/2011, 12h07

Euh... avec SQL Developper j'ai ça :

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
SQL_ID  18fz7yg2y4n0s, child number 0
-------------------------------------
/** TESTING CONNECTION IS ALIVE */ SELECT 1 FROM DUAL
 
Plan hash value: 1546270724
 
------------------------------------
| Id  | Operation  | Name | E-Rows |
------------------------------------
|   1 |  FAST DUAL |      |      1 |
------------------------------------
 
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

**stof** · 30/12/2011, 12h50

Transmis par mes DBA :

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
Plan
SELECT STATEMENT  CHOOSECost: 53,815  Bytes: 239  Cardinality: 1  																				
	4 FILTER  																			
		1 TABLE ACCESS FULL TABLE REFVL.AAAREF Cost: 20  Bytes: 32  Cardinality: 1  																		
		3 SORT AGGREGATE  Bytes: 16  Cardinality: 1  																		
			2 TABLE ACCESS FULL TABLE REFVL.AAAREF Cost: 20  Bytes: 16  Cardinality: 1  																	
	47 VIEW PREVL. Cost: 53,815  Bytes: 239  Cardinality: 1  																			
		46 HASH UNIQUE  Cost: 53,815  Bytes: 180  Cardinality: 1  																		
			45 FILTER  																	
				40 NESTED LOOPS OUTER  Cost: 53,814  Bytes: 180  Cardinality: 1  																
					33 FILTER  															
						32 NESTED LOOPS OUTER  Cost: 53,812  Bytes: 165  Cardinality: 1  														
							29 NESTED LOOPS OUTER  Cost: 53,810  Bytes: 154  Cardinality: 1  													
								26 HASH JOIN OUTER  Cost: 53,808  Bytes: 143  Cardinality: 1  												
									24 HASH JOIN RIGHT OUTER  Cost: 53,748  Bytes: 102  Cardinality: 1  											
										5 TABLE ACCESS FULL TABLE REFVL.PVDTIT Cost: 58  Bytes: 1,415,566  Cardinality: 34,526  										
										23 VIEW SYS. Cost: 53,482  Bytes: 2,099,559  Cardinality: 34,419  										
											22 HASH JOIN OUTER  Cost: 53,482  Bytes: 2,856,777  Cardinality: 34,419  									
												20 HASH JOIN OUTER  Cost: 39,216  Bytes: 2,041,320  Cardinality: 34,022  								
													18 HASH JOIN OUTER  Cost: 25,901  Bytes: 1,412,460  Cardinality: 33,630  							
														16 VIEW SYS. Cost: 14,356  Bytes: 1,042,530  Cardinality: 33,630  						
															15 HASH JOIN OUTER  Cost: 14,356  Bytes: 2,219,580  Cardinality: 33,630  					
																13 HASH JOIN OUTER  Cost: 11,609  Bytes: 1,068,200  Cardinality: 19,075  				
																	11 HASH JOIN OUTER  Cost: 2,024  Bytes: 858,375  Cardinality: 19,075  			
																		9 TABLE ACCESS BY INDEX ROWID TABLE PREVL.LIGRPM Cost: 3  Bytes: 308  Cardinality: 28  		
																			8 NESTED LOOPS  Cost: 1,120  Bytes: 378,700  Cardinality: 10,820  	
																				6 TABLE ACCESS FULL TABLE PREVL.RPM Cost: 159  Bytes: 9,168  Cardinality: 382  
																				7 INDEX RANGE SCAN INDEX PREVL.RPMLIGRPM_FK Cost: 2  Cardinality: 28  
																		10 TABLE ACCESS FULL TABLE PREVL.LIGRPMINVLIG Cost: 882  Bytes: 16,887,150  Cardinality: 1,688,715  		
																	12 TABLE ACCESS FULL TABLE PREVL.INVLIG Cost: 8,597  Bytes: 9,223,907  Cardinality: 838,537  			
																14 TABLE ACCESS FULL TABLE PREVL.LIGRPMINVLIG Cost: 882  Bytes: 16,887,150  Cardinality: 1,688,715  				
														17 TABLE ACCESS FULL TABLE PREVL.INVLIG Cost: 8,673  Bytes: 27,671,710  Cardinality: 2,515,610  						
													19 TABLE ACCESS FULL TABLE PREVL.STK Cost: 7,604  Bytes: 70,032,474  Cardinality: 3,890,693  							
												21 TABLE ACCESS FULL TABLE PREVL.STK Cost: 7,604  Bytes: 89,485,939  Cardinality: 3,890,693  								
									25 TABLE ACCESS FULL TABLE REFVL.PVDTIT Cost: 58  Bytes: 1,415,566  Cardinality: 34,526  											
								28 TABLE ACCESS BY INDEX ROWID TABLE PREVL.CRS Cost: 2  Bytes: 11  Cardinality: 1  												
									27 INDEX UNIQUE SCAN INDEX (UNIQUE) PREVL.PK_CRS Cost: 1  Cardinality: 1  											
							31 TABLE ACCESS BY INDEX ROWID TABLE PREVL.CRS Cost: 2  Bytes: 11  Cardinality: 1  													
								30 INDEX UNIQUE SCAN INDEX (UNIQUE) PREVL.PK_CRS Cost: 1  Cardinality: 1  												
					39 VIEW SYS. Cost: 2  Bytes: 15  Cardinality: 1  															
						38 NESTED LOOPS  Cost: 4  Bytes: 31  Cardinality: 1  														
							35 TABLE ACCESS BY INDEX ROWID TABLE REFVL.AAATIT Cost: 3  Bytes: 18  Cardinality: 1  													
								34 INDEX RANGE SCAN INDEX (UNIQUE) REFVL.STALIG_UNIQUE Cost: 2  Cardinality: 1  												
							37 TABLE ACCESS BY INDEX ROWID TABLE REFVL.STALIG Cost: 1  Bytes: 13  Cardinality: 1  													
								36 INDEX UNIQUE SCAN INDEX (UNIQUE) REFVL.PK_STALIG Cost: 0  Cardinality: 1  												
				42 SORT AGGREGATE  Bytes: 19  Cardinality: 1  																
					41 TABLE ACCESS FULL TABLE REFVL.PVDTIT Cost: 58  Bytes: 19  Cardinality: 1  															
				44 SORT AGGREGATE  Bytes: 19  Cardinality: 1  																
					43 TABLE ACCESS FULL TABLE REFVL.PVDTIT Cost: 58  Bytes: 19  Cardinality: 1

**Mohamed.Houri** · 30/12/2011, 13h59

Ce n'est pas le plan que je veux. Vos DBA peuvent faire ceci quand même sous sqlplus ou sous une fenêtre SQL de TOAD

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
 
 select /*+ gather_plan_statistics */ 
     distinct
       saaa.titidt as titaaaidt, 
       sbbb.titidt as titbbbidt, 
 nvl( 
       ( select 
		       aaarefext  
 	     from  aaaref aref 
 	     where aref.titidt = taaa.titidt 
 	     and   aref.aaatitidt = taaa.aaatitidt 
 	     and   aref.pertpsdatdeb =(select max(t1.pertpsdatdeb) 
		                           from  aaaref t1 
								   where t1.pvdidt != 2 
                                   and   t1.AAATITIDT = aref.AAATITIDT 
								   and   t1.PERTPSDATDEB <= r.DATVL
								   ) 
	   ), 
       nvl(taaa.aaacod2,ptaaa.pvdtitcod)
	) as codaaa, 
       ptbbb.pvdtitcod as codbbb, 
       ptaaa.pvdtitlib as libaaa, 
       ptbbb.pvdtitlib as libbbb, 
       r.datvl, 
       crsaaa.crsvlo as crsaaa, 
       crsbbb.crsvlo as crsbbb, 
       saaa.placotcod as placotaaa, 
       sbbb.placotcod as placotbbb
from 
 RPM R 
....

et tout de suite après la fin de la requête ceci

Code :

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

1
2
 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

**stof** · 30/12/2011, 15h10

Envoyé par Mohamed.Houri

et tout de suite après la fin de la requête ceci

Code :

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

1
2
 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Mes DBA ont lutté parce qu'en fait ça ne marche pas quand on fait
1°/La requête à étudier
2°/La commande que tu me donnes
-->il faut exécuter les 2 en même temps (comme un script quoi).

Bref :

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
PLAN_TABLE_OUTPUT
SQL_ID  ftw2j7fm6zchu, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS  */  x.titAAAidt,          x.titBBBidt,          x.codBBB,          x.libAAA,          x.liBBBb,
x.r_datvl,          x. crsAAA,          x.crsBBB,          x.placotAAA,          x. placotBBB        ,nvl(               (SELECT
AAArefext                 FROM   AAAref aref                WHERE aref.titidt          = x.tAAA_titidt                AND   aref.AAAtitidt       =
x.tAAA_AAAtitidt                AND   aref.pertpsdatdeb = (SELECT                                             max(t1.pertpsdatdeb)
FROM   AAAref t1                                        WHERE t1.pvdidt  != 2                                        AND
t1.AAAtitidt= aref.AAAtitidt                                        AND   t1.pertpsdatdeb <= x.r_datvl
)               ), nvl(x.tAAA_AAAcod2,x.ptAAA_pvdtitcod)            ) AS codAAA   FROM       (SELECT      DISTINCT      sAAA
 
Plan hash value: 2923835282
 
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                                      |               |     17 |        |     16 |00:00:00.07 |    3036 |      0 |       |       |          |
|*  2 |   TABLE ACCESS FULL                          | AAAREF        |     17 |      1 |     16 |00:00:00.04 |    1564 |      0 |       |       |          |
|   3 |   SORT AGGREGATE                             |               |     16 |      1 |     16 |00:00:00.03 |    1472 |      0 |       |       |          |
|*  4 |    TABLE ACCESS FULL                         | AAAREF        |     16 |      1 |     16 |00:00:00.03 |    1472 |      0 |       |       |          |
|   5 |  VIEW                                        |               |      1 |      1 |    710 |00:00:10.01 |     284K|      4 |       |       |          |
|   6 |   HASH UNIQUE                                |               |      1 |      1 |    710 |00:00:10.01 |     284K|      4 |   745K|   745K| 1238K (0)|
|*  7 |    FILTER                                    |               |      1 |        |   1060 |00:00:09.32 |     284K|      4 |       |       |          |
|   8 |     NESTED LOOPS OUTER                       |               |      1 |      1 |   1060 |00:00:07.79 |     158K|      4 |       |       |          |
|*  9 |      FILTER                                  |               |      1 |        |   1060 |00:00:07.78 |     157K|      4 |       |       |          |
|  10 |       NESTED LOOPS OUTER                     |               |      1 |      1 |   1389 |00:00:07.78 |     157K|      4 |       |       |          |
|  11 |        NESTED LOOPS OUTER                    |               |      1 |      1 |   1389 |00:00:07.77 |     155K|      4 |       |       |          |
|* 12 |         HASH JOIN OUTER                      |               |      1 |      1 |   1389 |00:00:07.76 |     153K|      4 |   807K|   807K| 1264K (0)|
|* 13 |          HASH JOIN RIGHT OUTER               |               |      1 |      1 |   1389 |00:00:06.76 |     153K|      4 |  2905K|  1162K| 3480K (0)|
|  14 |           TABLE ACCESS FULL                  | PVDTIT        |      1 |  34526 |  34526 |00:00:00.01 |     248 |      0 |       |       |          |
|  15 |           VIEW                               |               |      1 |  39620 |   1389 |00:00:06.72 |     152K|      4 |       |       |          |
|* 16 |            HASH JOIN OUTER                   |               |      1 |  39620 |   1389 |00:00:06.72 |     152K|      4 |   909K|   909K| 1282K (0)|
|* 17 |             HASH JOIN OUTER                  |               |      1 |  39620 |   1389 |00:00:05.02 |     119K|      4 |   963K|   963K| 1301K (0)|
|* 18 |              HASH JOIN OUTER                 |               |      1 |  39620 |   1389 |00:00:04.70 |   85635 |      3 |   963K|   963K| 1295K (0)|
|  19 |               VIEW                           |               |      1 |  39620 |   1389 |00:00:02.60 |   46965 |      3 |       |       |          |
|* 20 |                HASH JOIN OUTER               |               |      1 |  39620 |   1389 |00:00:02.60 |   46965 |      3 |   963K|   963K| 1231K (0)|
|* 21 |                 HASH JOIN OUTER              |               |      1 |  22545 |    575 |00:00:02.10 |   43186 |      3 |   971K|   971K| 1231K (0)|
|* 22 |                  HASH JOIN OUTER             |               |      1 |  22545 |    575 |00:00:00.50 |    4516 |      0 |  1048K|  1048K| 1231K (0)|
|  23 |                   TABLE ACCESS BY INDEX ROWID| LIGRPM        |      1 |     32 |    281 |00:00:00.01 |     737 |      0 |       |       |          |
|  24 |                    NESTED LOOPS              |               |      1 |  12829 |    302 |00:00:00.12 |     732 |      0 |       |       |          |
|* 25 |                     TABLE ACCESS FULL        | RPM           |      1 |    404 |     20 |00:00:00.01 |     690 |      0 |       |       |          |
|* 26 |                     INDEX RANGE SCAN         | RPMLIGRPM_FK  |     20 |     32 |    281 |00:00:00.01 |      42 |      0 |       |       |          |
|  27 |                   TABLE ACCESS FULL          | LIGRPMINVLIG  |      1 |   1697K|   1697K|00:00:00.01 |    3779 |      0 |       |       |          |
|* 28 |                  TABLE ACCESS FULL           | INVLIG        |      1 |    867K|   2597K|00:00:00.01 |   38670 |      3 |       |       |          |
|  29 |                 TABLE ACCESS FULL            | LIGRPMINVLIG  |      1 |   1697K|   1697K|00:00:00.01 |    3779 |      0 |       |       |          |
|* 30 |               TABLE ACCESS FULL              | INVLIG        |      1 |   2602K|   4031K|00:00:00.01 |   38670 |      0 |       |       |          |
|  31 |              TABLE ACCESS FULL               | STK           |      1 |   4031K|   4031K|00:00:00.01 |   33570 |      1 |       |       |          |
|  32 |             TABLE ACCESS FULL                | STK           |      1 |   4031K|   4031K|00:00:00.01 |   33570 |      0 |       |       |          |
|  33 |          TABLE ACCESS FULL                   | PVDTIT        |      1 |  34526 |  34526 |00:00:00.01 |     248 |      0 |       |       |          |
|  34 |         TABLE ACCESS BY INDEX ROWID          | CRS           |   1389 |      1 |    677 |00:00:00.01 |    2032 |      0 |       |       |          |
|* 35 |          INDEX UNIQUE SCAN                   | PK_CRS        |   1389 |      1 |    677 |00:00:00.01 |    1355 |      0 |       |       |          |
|  36 |        TABLE ACCESS BY INDEX ROWID           | CRS           |   1389 |      1 |    694 |00:00:00.01 |    2084 |      0 |       |       |          |
|* 37 |         INDEX UNIQUE SCAN                    | PK_CRS        |   1389 |      1 |    694 |00:00:00.01 |    1390 |      0 |       |       |          |
|  38 |      VIEW                                    |               |   1060 |      1 |     32 |00:00:00.01 |     788 |      0 |       |       |          |
|  39 |       NESTED LOOPS                           |               |   1060 |      1 |     32 |00:00:00.01 |     788 |      0 |       |       |          |
|  40 |        TABLE ACCESS BY INDEX ROWID           | AAATIT        |   1060 |      1 |     64 |00:00:00.01 |     722 |      0 |       |       |          |
|* 41 |         INDEX RANGE SCAN                     | STALIG_UNIQUE |   1060 |      1 |     64 |00:00:00.01 |     686 |      0 |       |       |          |
|* 42 |        TABLE ACCESS BY INDEX ROWID           | STALIG        |     64 |      1 |     32 |00:00:00.01 |      66 |      0 |       |       |          |
|* 43 |         INDEX UNIQUE SCAN                    | PK_STALIG     |     64 |      1 |     64 |00:00:00.01 |       2 |      0 |       |       |          |
|  44 |     SORT AGGREGATE                           |               |    284 |      1 |    284 |00:00:01.23 |   70432 |      0 |       |       |          |
|* 45 |      TABLE ACCESS FULL                       | PVDTIT        |    284 |      1 |    284 |00:00:01.23 |   70432 |      0 |       |       |          |
|  46 |       SORT AGGREGATE                         |               |    226 |      1 |    226 |00:00:00.98 |   56048 |      0 |       |       |          |
|* 47 |        TABLE ACCESS FULL                     | PVDTIT        |    226 |      1 |    308 |00:00:00.97 |   56048 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
1 - filter("AREF"."PERTPSDATDEB"=)
2 - filter(("AREF"."AAATITIDT"=:B1 AND "AREF"."TITIDT"=:B2))
4 - filter(("T1"."AAATITIDT"=:B1 AND "T1"."PERTPSDATDEB"<=:B2 AND "T1"."PVDIDT"<>2))
7 - filter((("PTAAA"."PVDTITCOD" IS NULL OR "PTAAA"."PVDTITCOD"=) AND ("PTBBB"."PVDTITCOD" IS NULL OR "PTBBB"."PVDTITCOD"=)))
9 - filter(("CRSAAA"."CRSVLO" IS NOT NULL OR "CRSBBB"."CRSVLO" IS NOT NULL))
12 - access("PTBBB"."PVDIDT"="from$_subquery$_018"."PVDIDT" AND "PTBBB"."TITIDT"="from$_subquery$_018"."TITIDT")
13 - access("PTAAA"."PVDIDT"="ILAAA"."PVDIDT" AND "PTAAA"."TITIDT"="SAAA"."TITIDT")
16 - access("SAAA"."INVLIGIDT"="from$_subquery$_012"."INVLIGIDT")
17 - access("SBBB"."INVLIGIDT"="ILBBB"."INVLIGIDT")
18 - access("ILBBB"."PVDIDT"="R"."PVD_PVDIDT" AND "ILBBB"."INVLIGIDT"="LRLBBB"."INVLIGIDT")
20 - access("LRLBBB"."LIGRPMIDT"="LR"."LIGRPMIDT")
21 - access("ILAAA"."INVLIGIDT"="LRLAAA"."INVLIGIDT")
22 - access("LRLAAA"."LIGRPMIDT"="LR"."LIGRPMIDT")
25 - filter("R"."DATVL"=TO_DATE(' 2011-12-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
26 - access("LR"."RPMIDT"="R"."RPMIDT")
28 - filter(("ILAAA"."PVDIDT"=2 AND "ILAAA"."FLGLIQ"='F'))
30 - filter("ILBBB"."FLGLIQ"='F')
35 - access("CRSAAA"."CRSIDT"="from$_subquery$_018"."QCSJ_C000000001800006")
37 - access("CRSBBB"."CRSIDT"="from$_subquery$_018"."QCSJ_C000000001800007")
41 - access("TAAA"."TITIDT"="from$_subquery$_018"."QCSJ_C000000001600001")
filter("TAAA"."STALIGIDT" IS NOT NULL)
42 - filter(("PVDIDT"="from$_subquery$_018"."PVD_PVDIDT" AND "STALIGCOD"=CASE  WHEN ("from$_subquery$_018"."QCSJ_C000000001800002">0) THEN 'RECU'
ELSE 'VERSE' END ))
43 - access("TAAA"."STALIGIDT"="STALIGIDT")
45 - filter(("T1"."TITIDT"=:B1 AND "T1"."PVDIDT"=2))
47 - filter(("T2"."TITIDT"=:B1 AND "T2"."PVDIDT"<>2))

**Mohamed.Houri** · 30/12/2011, 15h53

Merci. ça c'est un vrai plan d'exécution!!!

Je pense que le problème de performance vient de cette partie du plan d'exécution où 6,72 secondes sur 10 y sont consommées.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | 
--------------------------------------------------------------------------------------------------------------
|  15 |           VIEW                               |               |      1 |  39620 |   1389 |00:00:06.72 |     
|* 16 |            HASH JOIN OUTER                   |               |      1 |  39620 |   1389 |00:00:06.72 |     
|* 17 |             HASH JOIN OUTER                  |               |      1 |  39620 |   1389 |00:00:05.02 |     
|* 18 |              HASH JOIN OUTER                 |               |      1 |  39620 |   1389 |00:00:04.70 |   
|  19 |               VIEW                           |               |      1 |  39620 |   1389 |00:00:02.60 |   
|* 20 |                HASH JOIN OUTER               |               |      1 |  39620 |   1389 |00:00:02.60 |   
|* 21 |                 HASH JOIN OUTER              |               |      1 |  22545 |    575 |00:00:02.10 |   
|* 22 |                  HASH JOIN OUTER             |               |      1 |  22545 |    575 |00:00:00.50 |

Où l'on voit aussi l'enorme différence de E-Rows par rapport à A-Rows pour chaque opération. Mais ceci dit je pense bien que vos statistiques sont grosso-modo bonnes.

Quel sont les paramètres de votre optimizateur?

Code :

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

1
2
3
 
mhouri.world> show parameter optimizer
mhouri.world> show parameter multi%

Il n'est pas normal également de sélectionner 20 lignes de la table RPM (quel est le nombre de lignes de cette table) en faisant un FULL TABLE SCAN. Avez-vous un index sur la table RPM sur le champs(DATVL) ?

**stof** · 30/12/2011, 16h08

Envoyé par Mohamed.Houri

Quel sont les paramètres de votre optimizateur?

Code :

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

1
2
3
 
mhouri.world> show parameter optimizer
mhouri.world> show parameter multi%

ORA-00900: invalid SQL statement

Envoyé par Mohamed.Houri

Il n'est pas normal également de sélectionner 20 lignes de la table RPM (quel est le nombre de lignes de cette table) en faisant un FULL TABLE SCAN. Avez-vous un index sur la table RPM sur le champs(DATVL) ?

66186 lignes en DEV (où j'ai exécuté la requête qui prend 10 secondes) et bien plus dans les environnements de recette et production.

Pour les index, eh, eh... j'ai justement fait l'étude des index mis pour en débattre avec mon équipe mais des index sur RPM, j'en ai à la pelle :
sur INVIDT + sur INV_INVIDT + sur {FDSIDT, DATVL} + sur PVDIDT + sur PVD_PVDIDT + sur STARPMIDT + sur TYPRPMIDT
-->après analyse, je propose de supprimer tous ces index en apparence anarchiques et de créer {DATVL, TYPRPMIDT, FDSIDT, ISDERRPM} (ça montre que mon étude globale qui a aboutit à cette proposition sera entre autres utile pour notre requête).
EDIT : cela dit ça ne prend rien du tout comme temps dans la requête cette partie

Jointure : question de syntaxe ou performance

Oracle

Discussions similaires

Partager

Partager