Discussion :

[dubitoph]

Bonjour,

Je débute plus ou moins avec Oracle et je reste perplexe par rapport au temps d'une requête qui, lorsqu'elle n'est pas en cache, peut prendre plus d'une minute.

Voici ma requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select pat.nom || ' ' || pat.prenom as patient, to_char(pat.dtenai, 'dd/mm/YYYY') dtenaiss, pat.numnat, pat.numpat, o1.numobj, to_char(o1.dtedeb, 'dd/mm/YYYY') dtedebut  
from patients pat, objets o1 left join objets o2 on o2.numdescription = 50210 and 
                                                              o2.dtedeb >= o1.dtedeb and 
                                                              o2.objdeleted = 0 and 
                                                              o2.numobjpere = o1.numobj 
where o1.numdescription = 20028 and 
          o1.dtedeb between to_date('01/01/2013', 'dd/mm/YYYY') and to_date('22/02/2013', 'dd/mm/YYYY') and 
          o1.objdeleted = 0 and 
          o2.numdescription is null and 
          pat.numpat = o1.numpat 
order by o1.dtedeb

Voici l'explain plan :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Plan
SELECT STATEMENT  ALL_ROWSCost: 115  Bytes: 86  Cardinality: 1  						
	10 SORT ORDER BY  Cost: 115  Bytes: 86  Cardinality: 1  					
		9 NESTED LOOPS  Cost: 114  Bytes: 86  Cardinality: 1  				
			6 FILTER  			
				5 NESTED LOOPS OUTER  Cost: 112  Bytes: 48  Cardinality: 1  		
					2 TABLE ACCESS BY INDEX ROWID TABLE DOSMED.OBJETS Cost: 31  Bytes: 729  Cardinality: 27  	
						1 INDEX SKIP SCAN INDEX DOSMED.I_OBJETS_CDEOBJDTEDEB Cost: 10  Cardinality: 67  
					4 TABLE ACCESS BY INDEX ROWID TABLE DOSMED.OBJETS Cost: 4  Bytes: 21  Cardinality: 1  	
						3 INDEX RANGE SCAN INDEX DOSMED.I_OBJETS_NUMOBJPERE Cost: 2  Cardinality: 2  
			8 TABLE ACCESS BY INDEX ROWID TABLE DOSMED.PATIENTS Cost: 2  Bytes: 38  Cardinality: 1  			
				7 INDEX UNIQUE SCAN INDEX (UNIQUE) DOSMED.PK_PATIENTS Cost: 1  Cardinality: 1

Quelqu'un aurait une idée sur cette extrême lenteur?

En vous remerciant d'avance.

[spdev666]

Bonjour,

Pour commencer, je modifierais légèrement la requete (meme si cela ne doit rien changer aux perf)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT pat.nom || ' ' || pat.prenom AS patient, to_char(pat.dtenai, 'dd/mm/YYYY') dtenaiss, pat.numnat, pat.numpat, o1.numobj, to_char(o1.dtedeb, 'dd/mm/YYYY') dtedebut  
FROM patients pat 
  join objets o1 on pat.numpat = o1.numpat 
  LEFT JOIN objets o2 ON o2.numdescription = 50210 
                             AND o2.dtedeb >= o1.dtedeb 
                             AND o2.objdeleted = 0 
                             AND o2.numobjpere = o1.numobj 
WHERE o1.numdescription = 20028
   AND o1.dtedeb BETWEEN to_date('01/01/2013', 'dd/mm/YYYY') AND to_date('22/02/2013', 'dd/mm/YYYY')
   AND o1.objdeleted = 0 
   AND o2.numdescription IS NULL 
ORDER BY o1.dtedeb

Ensuite, vérifier si les stat de la base sont à jour pour l'optimisation de la requete.

[skuatamad]

Peux tu fournir le plan comme indiqué dans ce post de Mohamed avec le hint /*+ gather_plan_statistics */ et la méthode display_cursor :
http://www.developpez.net/forums/d11...x/#post6186140

[dubitoph]

Merci beaucoup pour l'aide apportée...

Voici donc le 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
SQL_ID  dg6kdaw8131ga, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics*/  pat.nom || ' ' || pat.prenom AS patient, to_char(pat.dtenai, 'dd/mm/YYYY') dtenaiss, pat.numnat, 
pat.numpat, o1.numobj, to_char(o1.dtedeb, 'dd/mm/YYYY') dtedebut   FROM patients pat    join objets o1 on pat.numpat = o1.numpat    LEFT JOIN 
objets o2 ON o2.numdescription = 50210                               AND o2.dtedeb >= o1.dtedeb                               AND 
o2.objdeleted = 0                               AND o2.numobjpere = o1.numobj  WHERE o1.numdescription = 20028    AND o1.dtedeb BETWEEN 
to_date('01/01/2013', 'dd/mm/YYYY') AND to_date('22/02/2013', 'dd/mm/YYYY')    AND o1.objdeleted = 0     AND o2.numdescription IS NULL  ORDER 
BY o1.dtedeb
 
Plan hash value: 2998451043
 
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                  |                       |      1 |      1 |     83 |00:00:13.33 |   41536 |   2836 | 13312 | 13312 |12288  (0)|
|   2 |   NESTED LOOPS                  |                       |      1 |      1 |     83 |00:00:42.64 |   41536 |   2836 |       |       |          |
|*  3 |    FILTER                       |                       |      1 |        |     83 |00:00:42.63 |   41285 |   2836 |       |       |          |
|   4 |     NESTED LOOPS OUTER          |                       |      1 |      1 |    240 |00:00:09.76 |   41285 |   2836 |       |       |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| OBJETS                |      1 |     27 |    239 |00:00:09.61 |   39456 |   2626 |       |       |          |
|*  6 |       INDEX SKIP SCAN           | I_OBJETS_CDEOBJDTEDEB |      1 |     67 |    196K|00:00:02.01 |    1265 |   1244 |       |       |          |
|*  7 |      TABLE ACCESS BY INDEX ROWID| OBJETS                |    239 |      1 |    157 |00:00:00.99 |    1829 |    210 |       |       |          |
|*  8 |       INDEX RANGE SCAN          | I_OBJETS_NUMOBJPERE   |    239 |      2 |   1713 |00:00:00.73 |     481 |    152 |       |       |          |
|   9 |    TABLE ACCESS BY INDEX ROWID  | PATIENTS              |     83 |      1 |     83 |00:00:00.01 |     251 |      0 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN           | PK_PATIENTS           |     83 |      1 |     83 |00:00:00.01 |     168 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("O2"."NUMDESCRIPTION" IS NULL)
   5 - filter(("O1"."NUMDESCRIPTION"=20028 AND "O1"."OBJDELETED"=0))
   6 - access("O1"."DTEDEB">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O1"."DTEDEB"<=TO_DATE(' 2013-02-22 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
       filter(("O1"."DTEDEB">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O1"."DTEDEB"<=TO_DATE(' 2013-02-22 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss')))
   7 - filter(("O2"."NUMDESCRIPTION"=50210 AND "O2"."OBJDELETED"=0 AND "O2"."DTEDEB">="O1"."DTEDEB"))
   8 - access("O2"."NUMOBJPERE"="O1"."NUMOBJ")
  10 - access("PAT"."NUMPAT"="O1"."NUMPAT")

[mnitu]

Connectez-vous comme dba executez la commande et postez le résultat

Code :

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

1
2
 
SQL> show parameter optimizer

Votre problème commence à l'étape 6, cardinalité estimé 67 réele 196 000

[Mohamed.Houri]

Dans un plan d'exécution il faut scruter la première opération. Car généralement, la déviation, si tant est qu'une déviation existe, commence dès le départ. Et dans votre cas la première opération est la suivante:

Code :

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

1
2
3
4
5
6
 
  ---------------------------------------------------------------------------------------------------------
 | Id  | Operation                       | Name                  | Starts | E-Rows | A-Rows |   A-Time   | 
 ----------------------------------------------------------------------------------------------------------
 |*  5 |      TABLE ACCESS BY INDEX ROWID| OBJETS                |      1 |     27 |    239 |00:00:09.61 |   
 |*  6 |       INDEX SKIP SCAN           | I_OBJETS_CDEOBJDTEDEB |      1 |     67 |    196K|00:00:02.01 |

Avec sa partie predicate qui nous informe un peu sur les indexes dont vous disposez

Code :

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

1
2
3
4
5
 
  6 - access("O1"."DTEDEB">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O1"."DTEDEB"<=TO_DATE(' 2013-02-22 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
       filter(("O1"."DTEDEB">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O1"."DTEDEB"<=TO_DATE(' 2013-02-22 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss')))

Il semble donc que vous ne disposez pas d'un index sur la table OBJECTS commençant par la colonne DTEDEB. Et que l'index I_OBJETS_CDEOBJDTEDEB contient cette colonne mais pas en première position. C'est pourquoi il a été choisi en accès SKIP SCAN(très couteux en mémoire par rapport à un accès en RANGE SCAN).

La présence d'une opération INDEX SKIP SCAN (à part quelques rares exceptions(skip scan 2) ,skip scan 1) est un symptôme d'une pauvre indexation dans votre application (au moins pour cette table).

Je reviens à ces premières opérations 5 et 6, où une vraie déviation existe. En effet, le CBO estime que l'opération 6 va générer 67 lignes alors qu'elle en génère 196.000. Bien que cette génération n'est pas assez consommatrice en temps de réponse mais néanmoins c'est elle qui fournit toutes ces lignes à son opération mère (opération 5) qui va passer presque 8 secondes à filtrer ces 196.000 lignes pour n'en laisser que 239 lignes.

1) Créez un index adéquat commençant par DTEDEB. Ou même mieux un index (numdescription,objdeleted,DTEDEB). Si vous disposez déjà de cet index alors les liens que j'ai donnés plus haut traitent de ce cas.
2) Pensez également à avoir des statistiques qui reflètent la réalité du volume de données ainsi que de sa répartition (vos E-Rows sont différentes de vos A-Rows).

Edit : ajout de numdescription dans l'index

[Waldar]

Ça ne devrait pas non plus changer grand-chose, mais votre requête s'écrit naturellement de cette façon :

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
  SELECT pat.nom || ' ' || pat.prenom AS patient
       , to_char(pat.dtenai, 'dd/mm/YYYY') as dtenaiss
       , pat.numnat
       , pat.numpat
       , o1.numobj
       , to_char(o1.dtedeb, 'dd/mm/YYYY') as dtedebut  
    FROM patients pat 
    JOIN objets   o1
      ON o1.numpat = pat.numpat
   WHERE o1.numdescription = 20028
     AND o1.dtedeb BETWEEN to_date('01/01/2013', 'dd/mm/YYYY')
                       AND to_date('22/02/2013', 'dd/mm/YYYY')
     AND o1.objdeleted = 0 
     AND NOT EXISTS (SELECT NULL
                       FROM objets o2
                      WHERE o2.numdescription = 50210 
                        AND o2.objdeleted     = 0 
                        AND o2.numobjpere     = o1.numobj 
                        AND o2.dtedeb        >= o1.dtedeb)
ORDER BY o1.dtedeb ASC;

[dubitoph]

Un tout grand merci pour toutes ces explications, même si j'avoue que j'en perds un peu mon latin devant tant de technicité que je ne maîtrise pas...

Concernant les index, voici ceux dont je pensais qu'ils seraient utilisés :

Code :

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

1
2
3
4
 
            Index name        Unique Loggin Degree Column          Order  Position
I_OBJETS_NUMBOSSDTEDEB	N      YES      1	DTEDEB	          Asc        2
I_OBJETS_NUMDESCRIPTION	N      YES      1   NUMDESCRIPTION  Asc       1

[dubitoph]

Pour répondre à Mohamed Houri, voici l'index dont question :

I_OBJETS_CDEOBJDTEDEB N YES 1 DTEDEB Asc 2 DOSMED

[mnitu]

Connectez-vous sous sqlplus comme dba, executez la commande et postez le résultat

Code :

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

SQL> SHOW parameter optimizer

[dubitoph]

Je travaille avec Toad

[mnitu]

Code :

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

1
2
3
4
5
 
Select name, display_value
  from v$parameter
 where name like '%optimizer%'
/

[dubitoph]

Ok, merci. Voici le résultat :

Code :

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

1
2
3
4
5
6
7
8
 
Name                                       DISPLAY_VALUE
optimizer_features_enable           10.2.0.4
optimizer_mode	                    ALL_ROWS
optimizer_index_cost_adj	       100
optimizer_index_caching	       0
optimizer_dynamic_sampling	       2
optimizer_secure_view_merging    TRUE

[mnitu]

Les paramètres sont OK.
En deuxième analyse le vrai problème ne se trouve pas aux étapes 5 et 6 ; bon ce n’est pas l’idéal mais la bas il n’y a que 9 secondes. Le plus du temps passée est situé à l’étape 3 FILTER ou on passe de 9 secondes à 42 !

[jkofr]

Hello,

Quelles la taille en nombre de bytes de la table objects et de l'index I_OBJETS_CDEOBJDTEDEB?

Code :

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

1
2
 
select segment_name,bytes from dba_segments where segment_name in ('I_OBJETS_CDEOBJDTEDEB','OBJETS');

Le temps passé dans Filter provient sans doute du volume important de données à lire.
Essaye de modifier l'index I_OBJETS_CDEOBJDTEDEB pour éviter le skip scan.

Jko

[tropiko]

où trouver cette colonne colonne A-Rows(Le réel) ?

En général c'est la colonne E-Rows(Estimation) qui s'affiche ...

merci

[Pomalaix]

Code :

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

1
2
3
4
Select name, display_value
  from v$parameter
 where name like '%optimizer%'
/

Et mieux encore (valable depuis la 10g) :

Code :

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

1
2
3
4
select name, value 
from v$ses_optimizer_env 
where sid=sys_context('userenv', 'SID') 
order by name;

[tropiko]

Pomalaix ,

c'est quoi la différence entre les 2 SELECT "optimizer" ?

[pachot]

Bonjour,

L'étape 6 sous-estime beaucoup le nombre de lignes retournées par: o1.dtedeb BETWEEN to_date('01/01/2013', 'dd/mm/YYYY') AND to_date('22/02/2013', 'dd/mm/YYYY')

Les stats sont-elles à jour ? La cardinalité est estimée par rapport aux valeurs min et max de la colonne. Si les stats ont été prises il y a longtemps (et même si le volume n'a pas beaucoup changé) il y a de fortes chances que l'optimiseur pense qu'il y a peu de lignes au delà du 01/01/2013.
Sur ce type de colonne date, il faut avoir des statistiques fraîches, ou forcer la valeur max au delà de la date courante.

Lorsque l'estimation sera correcte, on pourra voir si un index (voire un partitionnement) est nécessaire sur DATEDEB.

Cordialement,
Franck.

[dubitoph]

Bonjour à tous et merci beaucoup pour votre aide...

Bon, j'ai lancé la requête suivante en modifiant la date max pour voir ce que cela donne :

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 pat.nom || ' ' || pat.prenom AS patient
            , to_char(pat.dtenai, 'dd/mm/YYYY') AS dtenaiss
            , pat.numnat
            , pat.numpat
            , o1.numobj
            , to_char(o1.dtedeb, 'dd/mm/YYYY') AS dtedebut  
FROM patients pat 
         JOIN objets   o1
         ON o1.numpat = pat.numpat
WHERE o1.numdescription = 20028
           AND o1.dtedeb BETWEEN to_date('01/01/2013', 'dd/mm/YYYY')
           AND to_date('22/02/2014', 'dd/mm/YYYY')
           AND o1.objdeleted = 0 
           AND NOT EXISTS (SELECT o2.numobj
                                      FROM objets o2
                                      WHERE o2.numdescription = 50210
                                                  AND O2.NUMOBJPERE = o1.numobj )

Le temps d'exécution est de +- 30s, ce qui reste encore important. Je vais donc voir de quelle manière je peux actualiser les statistiques. Cependant, vu que cette bdd est fort utilisée durant la journée, je suppose qu'il est préférable de faire cela durant la nuit?