Discussion :

[neo.51]

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]

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]

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]

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]

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]

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]

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é ?

[orafrance]

l'index n'est pas utilisé visiblement

[bouyao]

C'esi bizare que l'index n'est pas utilisé.

Pour moi :

| 0 | SELECT STATEMENT | | 1 | 35 | 3 (67)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 35 | 3 (67)| 00:00:01 |
| 2 | HASH UNIQUE | | 1 | 35 | 2 (50)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| POINTGPS | 1 | 35 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INDEXDATEVHC | 1 | | 1 (0)| 00:00:01 |

Je pense que les stats sont fausse.

Code :

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

1
2
 
ANALYZE TABLE POINTGPS COMPUTE STATISTICS;

Même si tu a un seul disque essaye toujours de créer un tablespace pour les indexes.

[fatsora]

il faut analyzer les index ...

[neo.51]

Bon j'ai fais :

Code :

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

ANALYZE TABLE POINTGPS COMPUTE STATISTICS;

pareil pour l'index INDEXGPSDATE.

Après j'ai refais un explain plan

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

TABLE ACCESS FULL| POINTGPS

Bon au moins maintenant je sais que c'est ça qui me pose des problèmes de perf. par contre je comprend pas pourquoi oracle s'obstine à ne pas utiliser l'index. Je vous rappelle la définition de l'index :

Code :

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

1
2
3
4
  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)

L'index est marqué valide et a aussi été rebuildé pour voir.

Pour info cette table est constamment alimenté en pointgps. est ce que ça peut avoir une incidence ?

[fatsora]

peux tu changer la requete ou pas ...

si au lieu de

filter("DATETIME"<=39655.000000 AND "DATETIME">=39654.000000)

tu fais between 39654.000000 and 39655.000000

autre piste

utiliser dbms_stats avec cascade true
au lieu de analyze pour les stat

[bouyao]

Ajouter le hint pour une vérification.

Code :

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

1
2
3
SELECT /*+ index(INDEXDATEVHC) */ DISTINCT(IDVHC),IMMAT FROM POINTGPS 
WHERE (DATETIME>=39654.000000) AND (DATETIME<=39655.000000) ORDER BY IMMAT,IDVHC;

[neo.51]

C'est bizzare avec le between ça 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
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                 
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
|   0 | SELECT STATEMENT            |              |  1833 |   141K|  1211   (1)| 00:00:15 |                                                                                                                                                                                                                 
|   1 |  TABLE ACCESS BY INDEX ROWID| POINTGPS     |  1833 |   141K|  1211   (1)| 00:00:15 |                                                                                                                                                                                                                 
|*  2 |   INDEX RANGE SCAN          | INDEXDATEVHC |  1833 |       |   204   (1)| 00:00:03 |                                                                                                                                                                                                                 
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   2 - access("DATETIME">=39654.000000 AND "IDVHC"=187 AND "DATETIME"<=39655.000000)                                                                                                                                                                                                                         
       filter("IDVHC"=187)                                                                                                                                                                                                                                                                                   
 
15 rows selected

Donc c'est bon mais :
-Pourquoi ça marche avec le between et pas avec ">" "<" ?
-pourquoi il me parle de "filter("IDVHC"=187)" ?
-Pourquoi il utilise INDEXDATEVHC et non INDEXGPSDATETIME ?

Là ou ça devient "marrant" c'est que si je fais :

Code :

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

SELECT /*+ index(INDEXDATEVHC) */ DISTINCT(IDVHC),IMMAT FROM SYNOPTIS.POINTGPS WHERE (DATETIME>=39654.000000) AND (DATETIME<=39655.000000) ORDER BY IMMAT,IDVHC;

le plan c'est :

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
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
|   0 | SELECT STATEMENT              |              |   531 |  6903 | 27870   (1)| 00:05:35 |                                                                                                                                                                                                               
|   1 |  SORT ORDER BY                |              |   531 |  6903 | 27870   (1)| 00:05:35 |                                                                                                                                                                                                               
|   2 |   HASH UNIQUE                 |              |   531 |  6903 | 27862   (1)| 00:05:35 |                                                                                                                                                                                                               
|   3 |    TABLE ACCESS BY INDEX ROWID| POINTGPS     | 54999 |   698K| 27591   (1)| 00:05:32 |                                                                                                                                                                                                               
|*  4 |     INDEX RANGE SCAN          | INDEXGPSDATE | 55002 |       |   165   (2)| 00:00:02 |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   4 - access("DATETIME">=39654.000000 AND "DATETIME"<=39655.000000)                                                                                                                                                                                                                                         
 
16 rows selected

Je lui dit de passer par INDEXDATEVHC et il passe par l'index INDEXGPSDATE

On dirait qu'il s'emmêle les pinceaux entre

Code :

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

INDEX "INDEXDATEVHC" ON "POINTGPS" ("DATETIME", "IDVHC")

et

Code :

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

INDEX "INDEXGPSDATE" ON "POINTGPS" ("DATETIME")

En gros le premier sert pour quand je fais : ... WHERE DATETIME>*** and DATETIME<***
Et le deuxième pour quand je fais
... WHERE DATETIME>*** and DATETIME<*** AND IDVHC=***

On dirait qu'oracle n'aime pas trop et ne sait pas lequel choisir...

... d'ailleur les deux ne sont peut-être pas utiles ?

[bouyao]

Est ce que tu peut lancer maintenant la requête original (juste pour un test)

[neo.51]

si je relance la même requete ça donne ç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
18
Plan hash value: 1373022427                                                                                                                                                                                                                                                                                  
 
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
|   0 | SELECT STATEMENT              |              |   531 |  6903 | 27870   (1)| 00:05:35 |                                                                                                                                                                                                               
|   1 |  SORT ORDER BY                |              |   531 |  6903 | 27870   (1)| 00:05:35 |                                                                                                                                                                                                               
|   2 |   HASH UNIQUE                 |              |   531 |  6903 | 27862   (1)| 00:05:35 |                                                                                                                                                                                                               
|   3 |    TABLE ACCESS BY INDEX ROWID| POINTGPS     | 54999 |   698K| 27591   (1)| 00:05:32 |                                                                                                                                                                                                               
|*  4 |     INDEX RANGE SCAN          | INDEXGPSDATE | 55002 |       |   165   (2)| 00:00:02 |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   4 - access("DATETIME">=39654.000000 AND "DATETIME"<=39655.000000)                                                                                                                                                                                                                                         
 
16 rows selected

Donc ok, mais si je change la période de date (DATETIME">=39653.000000 AND "DATETIME"<=39655.000000) de ma requête il refait un TABLE ACCESS FULL| POINTGPS

j'y comprend plus grand chose là

[bouyao]

Moi, je pense que tu peut supprimer l'index INDEXGPSDATE puisque tu a déja un autre.

Il faut vérifier ces paramètres :

Code :

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

1
2
3
4
 
sql> show parameters optimizer_index_cost_adj;
 
sql> show parameters optimizer_index_caching;

puis

Code :

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

1
2
 
sql> select table_name,degree from dba_tables where degree>1;

[neo.51]

Code :

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

sql> SHOW parameters optimizer_index_cost_adj;

100

Code :

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

sql> SHOW parameters optimizer_index_caching;

ça ça retourne rien, je récupère le prompt direct sans rien, même pas une valeur nulle ou 0...

Code :

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

sql> SELECT table_name,degree FROM dba_tables WHERE degree>1;

0 rows selected

C'est grave docteur ?

[bouyao]

jusqu'a la tout est normal, par contre j'ai besoin la valeur de DB_FILE_MULTIBLOCK_READ_COUNT plus il est grand plus oracle choisis le full tables scan.

[neo.51]

DB_FILE_MULTIBLOCK_READ_COUNT 16