Requete sql longue a executer

**locs** · 07/05/2009, 14h55

voila le plan d'execution de la requete sans le where:

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
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
42 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 2137789089
 
---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------
 
Note
-----
   - star transformation used for this statement
 
 
Statistiques
----------------------------------------------------------
         37  recursive calls
         12  db block gets
        181  consistent gets
          0  physical reads
          0  redo size
       3705  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

**locs** · 07/05/2009, 14h56

est le plan d'execution pour la requete avec le where :

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
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
44 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 2137789089
 
---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------
 
Note
-----
   - star transformation used for this statement
 
 
Statistiques
----------------------------------------------------------
         25  recursive calls
         12  db block gets
         61  consistent gets
          0  physical reads
          0  redo size
       3828  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         44  rows processed

**locs** · 07/05/2009, 15h01

le autotrace de la requete avec le where :

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
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 2781769152
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     1 |   213 |    29   (7)| 00:00:01 |
|   1 |  SORT UNIQUE                     |                   |     1 |   213 |    28   (4)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER             |                   |     1 |   213 |    27   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER            |                   |     1 |   200 |    27   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                   |     1 |    64 |    27   (0)| 00:00:01 |
|   5 |      MERGE JOIN CARTESIAN        |                   |     6 |   192 |     8   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| CRIT_AXES_CONTENU |     1 |    23 |     3   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | CAC_AXE_CONTENU   |     1 |       |     2   (0)| 00:00:01 |
|   8 |       BUFFER SORT                |                   |    70 |   630 |     5   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL         | P_PDES            |    70 |   630 |     5   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS BY INDEX ROWID | P_DESG            |     1 |    32 |     9   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN           | DSG_DOMAINE       |    24 |       |     2   (0)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN             | DSG_DOCDES        |     1 |   136 |     0   (0)| 00:00:01 |
|* 13 |    INDEX UNIQUE SCAN             | DOC_PK            |     1 |    13 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - filter("CRIT_AXES_CONTENU"."CAC_GRPE_CRITERE"=' ' AND
              "CRIT_AXES_CONTENU"."CAC_SGRPE_CRITERE"=' ')
   7 - access("CRIT_AXES_CONTENU"."CAC_ID_AXE"=13)
   9 - filter("P_PDES"."PDS_DOMAINE_APPLI"='Prosp')
  10 - filter("P_DESG"."DSG_NO_PERS"=3 AND "P_DESG"."DSG_NO_ETAB_NAISS"=1)
  11 - access("P_DESG"."DSG_AG_GESTION"=1 AND "DSG_DOMAINE_APPLI"='Prosp' AND
              "PDS_NIU"="DSG_NIU")
       filter("CAC_NIU"="P_DESG"."DSG_NIU")
  12 - access("DCC_AG_GESTION"(+)=1 AND "DCC_DOMAINE_APPLI"(+)='Prosp' AND
              "P_DESG"."DSG_NO_CPTE_GENERAL"="DCC_NO_CPTE_GENERAL"(+) AND "DCC_NO_ETAB_NAISS_PERS"(+)=1 AND
              "DCC_NO_PERS"(+)=3 AND "P_DESG"."DSG_NO_CPTE_AUXIL"="DCC_NO_CPTE_AUX"(+) AND
              "P_DESG"."DSG_NO_ETAB_NAIS"="DCC_NO_ETAB_NAISS_IMM"(+) AND
              "P_DESG"."DSG_NO_IMMEUBLE"="DCC_NO_IMM"(+) AND "P_DESG"."DSG_NIU"="DCC_NIU"(+) AND
              "P_DESG"."NO_DOSS_TRX"="DCC_NO_DOSS_TRX"(+))
       filter("P_DESG"."NO_DOSS_TRX"="DCC_NO_DOSS_TRX"(+) AND "P_DESG"."DSG_NIU"="DCC_NIU"(+))
  13 - access("B_DOCDESC"."DCC_CODE_DOCUMENT"="DOC_NO_CHRONO"(+))
 
Note
-----
   - star transformation used for this statement
 
 
Statistiques
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      22533  consistent gets
          0  physical reads
          0  redo size
        468  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          5  rows processed

**locs** · 07/05/2009, 15h03

le autotrace de la requete sans le where :

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
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 4116296751
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     1 |   202 |    39   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                     |                   |     1 |   202 |    39   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                   |                   |     1 |   202 |    38   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER            |                   |     1 |   193 |    37   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER           |                   |     1 |   180 |    37   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                   |     1 |    44 |    37   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| CRIT_AXES_CONTENU |     1 |    12 |     3   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | CAC_AXE_CONTENU   |     1 |       |     2   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| P_DESG            |     1 |    32 |    34   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | DSG_DOMAINE       |   122 |       |     2   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN            | DSG_DOCDES        |     1 |   136 |     0   (0)| 00:00:01 |
|* 11 |     INDEX UNIQUE SCAN            | DOC_PK            |     1 |    13 |     0   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS BY INDEX ROWID   | P_PDES            |     1 |     9 |     1   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN            | NIU               |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("CRIT_AXES_CONTENU"."CAC_ID_AXE"=13)
   8 - filter("P_DESG"."DSG_NO_PERS"=3 AND "P_DESG"."DSG_NO_ETAB_NAISS"=1)
   9 - access("P_DESG"."DSG_AG_GESTION"=1 AND "DSG_DOMAINE_APPLI"='Prosp' AND
              "CAC_NIU"="P_DESG"."DSG_NIU")
  10 - access("DCC_AG_GESTION"(+)=1 AND "DCC_DOMAINE_APPLI"(+)='Prosp' AND
              "P_DESG"."DSG_NO_CPTE_GENERAL"="DCC_NO_CPTE_GENERAL"(+) AND "DCC_NO_ETAB_NAISS_PERS"(+)=1 AND
              "DCC_NO_PERS"(+)=3 AND "P_DESG"."DSG_NO_CPTE_AUXIL"="DCC_NO_CPTE_AUX"(+) AND
              "P_DESG"."DSG_NO_ETAB_NAIS"="DCC_NO_ETAB_NAISS_IMM"(+) AND
              "P_DESG"."DSG_NO_IMMEUBLE"="DCC_NO_IMM"(+) AND "P_DESG"."DSG_NIU"="DCC_NIU"(+) AND
              "P_DESG"."NO_DOSS_TRX"="DCC_NO_DOSS_TRX"(+))
       filter("P_DESG"."NO_DOSS_TRX"="DCC_NO_DOSS_TRX"(+) AND "P_DESG"."DSG_NIU"="DCC_NIU"(+))
  11 - access("B_DOCDESC"."DCC_CODE_DOCUMENT"="DOC_NO_CHRONO"(+))
  12 - filter("P_PDES"."PDS_DOMAINE_APPLI"='Prosp')
  13 - access("PDS_NIU"="DSG_NIU")
 
Note
-----
   - star transformation used for this statement
 
 
Statistiques
----------------------------------------------------------
          8  recursive calls
          0  db block gets
      16402  consistent gets
          0  physical reads
          0  redo size
        468  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

**mydb_server** · 07/05/2009, 16h04

Pour générer les plans d'exécution, tu utilises les commandes suivantes :

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
 
SQL> explain plan for
select * from dual; -- <-------- mets ta requête ici
 
SQL> @?/rdbms/admin/utlxpls
 
-- et tu avoir un truc de ce genre
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
8 rows selected.

**mydb_server** · 07/05/2009, 16h07

oups, je viens de voir que tu as posté les bons plans d'exécution

**locs** · 07/05/2009, 16h50

tu as pu voir ce qui ne tourne pas rond au niveau de mes requetes? Parce que je commence a désespérer lol.
Merci beaucoup en tout cas du temps que tu passes à m'aider.

**locs** · 08/05/2009, 11h27

Pas d'idée?

**Waldar** · 08/05/2009, 12h39

Quand vous faites votre requête avec le where, vous avez l'opération suivante :

Code :

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

|*  9 |        TABLE ACCESS FULL         | P_PDES            |    70 |   630 |     5   (0)| 00:00:01 |

Qui est je suppose le goulot d'étranglement de vos performances.

Si vous pouvez modifier le code de votre vue, enlever le where et mettez le champ dans le résultat du select.
Vous pourrez filtrer dessus après.

**locs** · 08/05/2009, 17h13

En fait je ne comprend pas une chose, lorsque j'enleve dans la clause where

Code :

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

1
2
 
dsg_ag_gestion=1

les requetes tombent normalement, mais quand je le met j'ai des requetes trés longuent a être executer.
Il n'y aurait pas un probléme au niveau des indes ou au niveau de la vue?

**locs** · 11/05/2009, 10h11

bonjour, je viens a l'instant de tester votre solution, à savoir de retiré les champs de la clause where et les mettre dans le select pour la vue. Ensuite effectué ma condition where sur la requête qui interroge la vue, mais j'ai toujours le même problème à savoir des temps de réponse très long.
J'ai l'impression que le problème viens de la clause dsg_ag_gestion=1, lorsque je ne la met pas les temps sont très correcte, en revanche des qu'elle fait parti de la clause where, les temps d'exécution de la requête sont très long (supérieure à 30seconde), malheureusement j'ai absolument besoin de cette clause where.
Quelqu'un aurait une idée?
merci.

**Waldar** · 11/05/2009, 10h15

Le pourquoi on le voit très bien dans les plans d'exécution, ils sont très différents avec ou sans le where.

Le comment résoudre peut être beaucoup plus complexe.

Savez-vous si vos statistiques sont à jour ? Ca pourrait être une explication.
Regardez dans la vue ALL_TABLES ou USER_TABLES la colonne LAST_ANALYZED.

**locs** · 11/05/2009, 10h17

il me faut regarder pour les tables utilisé dans la vue?

**Waldar** · 11/05/2009, 10h19

Oui pour toutes les tables et leurs index (ALL_INDEXES / USER_INDEXES) également.

**locs** · 11/05/2009, 10h22

voici les résultats pour les statistque :

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
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_DESG                         21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_PDES                         21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
CRIT_AXES_CONTENU              21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCDESC                      21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCUMENT                     21/04/09                  
 
1 rows selected

**locs** · 11/05/2009, 10h24

voici les résultats pour la table all_users :

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
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_DESG                         21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_PDES                         21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
CRIT_AXES_CONTENU              21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCDESC                      21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCUMENT                     21/04/09                  
 
1 rows selected

**locs** · 11/05/2009, 10h27

les résultats pour la table all_indexes :

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
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_DESG                         21/04/09                  
P_DESG                         21/04/09                  
P_DESG                         21/04/09                  
 
3 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_PDES                         21/04/09                  
P_PDES                         21/04/09                  
P_PDES                         21/04/09                  
 
3 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
CRIT_AXES_CONTENU              21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCDESC                      21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCUMENT                     21/04/09                  
 
1 rows selected

**locs** · 11/05/2009, 10h28

est les résultat pour la table user_indexes :

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
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_DESG                         21/04/09                  
P_DESG                         21/04/09                  
P_DESG                         21/04/09                  
 
3 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
P_PDES                         21/04/09                  
P_PDES                         21/04/09                  
P_PDES                         21/04/09                  
 
3 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
CRIT_AXES_CONTENU              21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCDESC                      21/04/09                  
 
1 rows selected
 
TABLE_NAME                     LAST_ANALYZED             
------------------------------ ------------------------- 
B_DOCUMENT                     21/04/09                  
 
1 rows selected

**Waldar** · 11/05/2009, 10h37

ALL_TABLES et USER_TABLES vous donneront le même résultat pas la peine de poster les deux résultats, c'est juste que je ne savais pas où se situent vos objets.

21 avril, ce n'est pas trop vieux sans être récent.
Savez-vous si vos données bougent énormément ?

Si la volumétrie de vos tables n'est pas excessive pouvez vous recalculer les stats (faites une recherche sur le package DBMS_STATS) et reregarder les plans d'exécutions de votre requête ? Pas la peine de le reposter si c'est le même.