Requête sur grosse table ne veut pas utiliser l'index approprié

**neo.51** · 25/11/2008, 15h03

Bonjour,

Voilà je fais appel à vos conseil car je suis assez embêté sur les performances d'une table oracle assez grosse.

Bref j'ai une table qui récupère des points gps pour une flotte de véhicules. Cette table est considérée comme isolée : pas de jointure ou de liens avec d'autres tables possibles. Coté volumétrie on va dire une dizaine de millions d'enregistrements.

Voilà la description de la table et de ses 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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE TABLE "POINTGPS" 
   (	"IDVHC" NUMBER(10,0), 
	"LAT" FLOAT(76), 
	"LON" FLOAT(76), 
	"DATETIME" FLOAT(76), 
	"NBSAT" NUMBER(10,0), 
	"NUMTRAJ" NUMBER(10,0), 
	"POIDS" NUMBER(10,0), 
	"VOLUME" NUMBER(10,0), 
	"ODOMETRE" NUMBER(10,0), 
	"COMPAS" NUMBER(10,0), 
	"TEMPERAT" NUMBER(10,0), 
	"BAC" NUMBER(10,0), 
	"RIPEUR" NUMBER(10,0), 
	"CAP1" VARCHAR2(255 BYTE), 
	"CAP2" NUMBER(10,0), 
	"CAP3" NUMBER(10,0), 
	"TYPCOL" NUMBER(10,0), 
	"PORTEDEV" NUMBER(10,0), 
	"VITESSE" FLOAT(76), 
	"NUMADR" NUMBER(10,0), 
	"CLEACCESRESEAU" NUMBER(10,0), 
	"POIDSDYN" NUMBER(10,0), 
	"CLETOURMODELE" NUMBER(10,0), 
	"IMMAT" VARCHAR2(15 BYTE), 
	"LIBEQUIPE" VARCHAR2(25 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 5242880 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TB1";
 
 
  CREATE INDEX "INDEXDATEVHC" ON "POINTGPS" ("DATETIME", "IDVHC") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TB1" ;
 
  CREATE INDEX "INDEXGPSDATE" ON "POINTGPS" ("DATETIME") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TB1" ;
 
  CREATE INDEX "INDEXIDVHCIMMAT" ON "POINTGPS" ("IDVHC", "IMMAT") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TB1" ;

N'étant pas de grands connaisseurs oracle dans ma boite on a créé tout ça avec les options "par défaut" d'oracle hein

Bon on va dire que globalement les performances sont acceptables. Mais y a une requête qui fait mal c'est :

Code :

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

SELECT DISTINCT(IDVHC),IMMAT FROM POINTGPS WHERE (DATETIME>=39654.000000) AND (DATETIME<=39655.000000) ORDER BY IMMAT,IDVHC

Celle là c'est une 15 aine de secondes à chaque fois et ça fait mal

La base tourne sur un windows server 2003 avec oracle 10g entreprise édition et la machine c'est un bi xeon avec 4Go de RAM dédié à cette base. Je précise que j'ai 15 sec de réponse sur cette requete quand je suis tout seul sur le serveur et que je lance QUE cette requete.

Je me dis que quand même il doit y avoir moyen d'optimiser tout ça

Voici les pistes que j'ai envisagé :
->Optimiser les type number de certainnes colonnes (IDVHC ne représente que des chiffres de 1 à 999 par exemple).
->Paramétrer mes indexes différemment : là j'y pipe que dalle je sais pas par où commencer.
->Changer ma table et mes indexes dans différents tablespace : n'est utile que si on a 2 disque physiques séparé non ? Moi c'est du raid et la charge sur les disques est géré par le controleur raid.
->Optimiser ma requete : là je vois pas, y a bien le group by à la place du distinct mais j'ai lu que ça changeait rien...
->Changer les params de mon instance oracle : là aussi c'est une install par défaut en serveur dédié mais je sais pas par où commencer.

Bref voilà je m'attends pas à une solution miracle (enfin si elle existe je la prend quand même hein

) mais surtout à des piste et savoir dans quelle ordre de priorité procéder.

**couak** · 25/11/2008, 17h35

une piste (mais pas forcément la meilleure) : un index bitmap sur ta colonne IDVHS
http://www.dba-oracle.com/oracle_tip...ed_indexes.htm
http://www.oracle.com/technology/pub...a_indexes.html

**13thFloor** · 25/11/2008, 17h38

En premier : poster un plan d'exécution de ta requête :

Code :

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

1
2
3
4
truncate table plan_table;
explain plan for 
SELECT DISTINCT(IDVHC),IMMAT FROM POINTGPS WHERE (DATETIME>=39654.000000) AND (DATETIME<=39655.000000) ORDER BY IMMAT,IDVHC;
select * from table(dbms_xplan.display());

**13thFloor** · 25/11/2008, 17h48

En Second : vérifier que les statistiques sont à jour (en principe oui par défaut avec la 10g).

Piste possible : distinct et group consomment de la PGA. Si elle est trop petite, le trie se fait sur disque (donc plus consommateur que de s'effectuer en mémoire) => augmentation du paramètre PGA_AGGREGATE_TARGET à prévoir.

PS : le plan d'exécution tu peux également l'obtenir avec un autotrace sous sql+.
Exemple :

Code :

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

1
2
set autotrace on
select...

Ou set autotrace traceonly pour n'avoir que le plan sans le résultat.
pour désactiver l'autotrace : set autotrace off

**neo.51** · 25/11/2008, 18h32

Bonjour,

Pour le 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
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 3526807079                                                                                                                                                                                                                                                                                  
 
--------------------------------------------------------------------------------                                                                                                                                                                                                                             
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                             
--------------------------------------------------------------------------------                                                                                                                                                                                                                             
|   0 | SELECT STATEMENT    |          |   440 |  7040 | 35799   (3)| 00:07:10 |                                                                                                                                                                                                                             
|   1 |  SORT ORDER BY      |          |   440 |  7040 | 35799   (3)| 00:07:10 |                                                                                                                                                                                                                             
|   2 |   HASH UNIQUE       |          |   440 |  7040 | 35786   (3)| 00:07:10 |                                                                                                                                                                                                                             
|*  3 |    TABLE ACCESS FULL| POINTGPS | 92740 |  1449K| 35280   (3)| 00:07:04 |                                                                                                                                                                                                                             
--------------------------------------------------------------------------------                                                                                                                                                                                                                             
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   3 - filter("DATETIME"<=39655.000000 AND "DATETIME">=39654.000000)                                                                                                                                                                                                                                         
 
15 rows selected

Pour le PGA je vais regarder ça mais ça semble une bonne piste car de mémoire le graphique de l'entreprise manager montrait pas mal d'I/O disque.

merci

**orafrance** · 25/11/2008, 20h57

apparemment elle n'est pas indexée c'est bien ça ? Si c'est bien le cas alors commence par ajouter un index sur datetime

Pour les IO, tu fais du FTS (lecture complète de la table), donc même sans problème de tri ça pose forcément problème :/

T'es sensé retourner plusieurs IDVHC différent ? Le DISTINCT est bien nécessaire ? Et le ORDER BY ?

couak

pourquoi indexer IDVHC ?

**neo.51** · 25/11/2008, 21h44

Envoyé par orafrance

apparemment elle n'est pas indexée c'est bien ça ? Si c'est bien le cas alors commence par ajouter un index sur datetime

ça c'est fait

T'es sensé retourner plusieurs IDVHC différent ? Le DISTINCT est bien nécessaire ? Et le ORDER BY ?

oui pour tout : j'ai x véhicules avec idvhc différents. Je veux savoir la liste des véhicules qui ont émis.

couak

pourquoi indexer IDVHC ?

Parce qu'un fois que j'ai ma liste d'IDVHC pour la journée ben je peux charger les pointsgps par véhicule et par datetime. (cette requete ne pose pas de problème.

Pourquoi je me retrouve à faire un TABLE ACCESS FULL| POINTGPS alors que je filtre par datetime champ indexé ?

Requête sur grosse table ne veut pas utiliser l'index approprié

Administration Oracle

Vue hybride

Discussions similaires

Partager

Partager