Besoin d'avis sur une requête

**VincentR** · 03/10/2008, 11h51

Bonjour,

J'ai une requete oracle qui me pose probleme, et j'aimerai bien en trouver la source!! Voici la requete

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT
	((to_timestamp(to_char(pr_time,'DD/MM/RRRR HH24:MI'),'DD/MM/RRRR HH24:MI') - to_timestamp('11/02/2008 00:00','DD/MM/RRRR HH24:MI'))+to_timestamp('01/01/1900 00:00:00.00000','dd/mm/RRRR hh24:mi:ss.ff')) pr_time,
    	a.VALUE,
	a.MODULE,
	b.COLUMN_1
FROM TABLE_1 subpartition (SUB_PART_W0817) a, 
    TABLE 2 b 
WHERE a.CAR_ID = b.CAR_ID  
    and a.DEVICE_ID = 270382     
ORDER BY pr_time;

Pour le contexte

TABLE_1 est une tres grosse table, contenant plusieurs miliards de lignes. Une souspartition contient environ 4millions de lignes.
TABLE_2 est une petite table, contenant environ 1000 lignes.
Le filtrage sur le DEVICE_ID nous retourne en gros 9000 lignes.
La requete prend +/- 3 secondes et on amerait obtenir un temps de reponse de l'ordre de la seconde

Pour mes interrogations

Dans TABLE_1, j'ai un index local sur (dans l'ordre) DEVICE_ID et CAR_ID. Quand je fais un explain plain à ma grande surprise l'index n'est pas utilisé. Si je force l'utilisation avec un hint, les perf ne sont effectivement pas meilleures et le cost retourné par l'explain plain est bien supérieur... etrange non? Pourtant l'analyse sur l'index a été effectué il y a 2 semaines et les données sur lesquelle je requete ont plusieurs mois d'ancienneté.
J'ai essayé de créer un nouvel index qui ne soit que pour DEVICE_ID et il est effectivement utilisé et diminue le cost (de 6718 à 80 tout de meme). Cependant, en terme de temps d'execution je ne vois pas forcement de grande difference... voire c'est pire (une dizaine de sec)... Normal ou pas?
Est que le fait que le order by se fasse sur une colonne calculé peut poser pb?
De facon générale, je trouve difficile de pouvoir être sur qu'une modification apportée à me requete apporte une amélioration du temps d'execution. Et ce parce que Oracle fait sa cuisine derriere, prepare ses plans etc, si bien que la meme requete s'execute parfois en 10ms et d'autre en 3 sec... dur d'avoir des perfs fiables... Comment s'y prendre pour etre sur de ce que je fais? Le temps depend peut etre de trop de parametres volatiles et seul le cost est fiable?
Est ce que le hardware peut poser probleme avec une telle requete? Ou comment savoir s'il pose probleme? Je ne connais pas la config du serveur pour l'instant.

Le probleme que je rencontre est qu'il n'est pas evident de trifouiller, bouger les index, les recréer etc car ces taches peuvent prendre plusieurs heures compte tenu de la taille de TABLE_1... Il faut donc que je choississe avec precaution ce que je souhaite tenter.

Des suggestions?

**McM** · 03/10/2008, 12h52

Est que le fait que le order by se fasse sur une colonne calculé peut poser pb

Non le order by se fait sur la colonne pr_time
Tu ne peux pas utiliser d'alias dans le order by.
Si tu veux trier la première colonne , faut faire un ORDER BY 1.

Edit, il manquait le 1 dans mon order by...

**VincentR** · 03/10/2008, 13h53

Ok c'est deja ca en moins

**skuatamad** · 03/10/2008, 14h01

Bonjour MCM
Je ne comprends pas trop ce que tu veux dire par :

Envoyé par McM

Tu ne peux pas utiliser d'alias dans le order by.

Pour moi le test ci-dessous montre que le tri se fait sur l'alias c et non sur la colonne c :

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
SQL> with t as (select 1 as c1,sysdate as c from dual
  2            union select 2 as c1,sysdate-5 as c from dual
  3            union select 3 as c1,sysdate+3 as c from dual
  4            union select 4 as c1,sysdate+1 as c from dual)
  5  select c1,c+trunc(dbms_random.value(1,10)) as c from t
  6  order by c;
 
        C1 C
---------- -------------------
         2 30/09/2008 13:44:42
         4 07/10/2008 13:44:42
         1 08/10/2008 13:44:42
         3 12/10/2008 13:44:42
 
SQL> /
 
        C1 C
---------- -------------------
         2 06/10/2008 13:44:47
         1 07/10/2008 13:44:47
         4 08/10/2008 13:44:47
         3 12/10/2008 13:44:47
 
SQL> /
 
        C1 C
---------- -------------------
         4 06/10/2008 13:46:03
         1 07/10/2008 13:46:03
         2 07/10/2008 13:46:03
         3 10/10/2008 13:46:03

**McM** · 03/10/2008, 14h05

Houla.. Au temps pour moi..

Code :

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

SELECT dummy as a from Dual order by a

marche bien..

Désolé..

**Waldar** · 03/10/2008, 14h33

C'est dans le group by qu'on ne peut pas utiliser d'alias, je me fais parfois avoir également.

Code :

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

SELECT dummy as A, count(*) b FROM Dual group by A

**VincentR** · 03/10/2008, 14h37

Apres test, que je trie sur la colone calculée ou sur pr_time, en terme de cout il n'y a pas de changement, et en terme de temps d'execution non plus

**skuatamad** · 03/10/2008, 14h40

Salut VincentR,
Pour en revenir à ton problème :

Envoyé par VincentR

Et ce parce que Oracle fait sa cuisine derriere, prepare ses plans etc, si bien que la meme requete s'execute parfois en 10ms et d'autre en 3 sec

As tu essayé en utilisant des bind variables ?
Quand tu dis 10ms puis 3sec, c'est en utilisant les mêmes constantes ?
Je ne suis pas sûr de pouvoir t'aider réellement mais ce sont je pense des précisions utiles à apporter.

[EDIT] et sans ORDER BY, ça donne quoi le temps d'exécution (juste pour savoir si c'est lui qui consomme.)

**VincentR** · 03/10/2008, 14h56

Merci pour ton aide.

Pour tes questions, non dans l'appli je n'utilise pas de bind variables ca peut etre une piste mais ca n'empeche pas que la 1ere execution sera toujours aussi longue... de plus il est rare que l'on execute 2 fois la meme requete sur la meme souspartition avec seulement un different numéro de device. A moins que le plan qu'enregistre Oracle soit valable pour toutes les sous partitions mais j'en doute... A vérifier cependant.

Pour le order by, j'ai eu la meme idée au début mais non, le probleme ne semble pas venir de la, le supprimer ne change rien au probleme.

**dragon74** · 03/10/2008, 16h25

De quel type est ton index local sur DEVICE_ID lorsque tu ne fais que sur un seul champ? Est-ce un bitmap?
Avez-vous aussi fait des stats globales sur la table où seulement sur les partitions?

**VincentR** · 03/10/2008, 16h44

Mon index est un BTREE sur le device... effectivement un Bitmap pourrait etre interressant.

Et pour les stats c'est pas moi qui les ai lancées... je pensais qu'une analyse sur la table me sortait les stats pour ses partitions et sous partitions mais peut etre que je me plante.

Je vais lancer des créations d'index pour ce week end et je verrai ce que ca donne lundi.

**VincentR** · 08/10/2008, 18h47

Bon bein la re création des index n'a pas changé grand chose... j'ai aussi lancé une analyse complete de la table qui m'a pris plus d'un jour mais sans resultat.

Bref ma query ne se sert pas des index et apparement elle a bien raison car c'est plus rapide en faisant un full tablescan.

Pour moi ca me semblait logique que sur une telle query, l'index sur le DEVICE_ID et celui sur CAR_ID de ma jointure auraient pu améliorer les perfs non?

Je me sens un peu dans l'impasse la...

**deadoralive** · 09/10/2008, 09h59

Bonjour,

Pourrais tu décrire tes index et afficher le plan d'exécution ?
Les index bitmap, utiles seulement pour des champs à faible cardinalité, ce qui ne semble pas le cas ?
Sinon le parallélisme me semble une piste à creuser.

**ducho** · 09/10/2008, 17h17

Bonjour,

je ne pourrais pas t' aider par contre je voudrais bien connaitre
la configuration de ton serveur ( cpu et mémoire)
et comment as-tu configuré ta SGA

car 3 secondes pour ramener 9000 lignes en interrogeant une sous-partion
de 4M lignes sur une table aussi grosse me parait déjà un super temps
de réponse ..
(je serais heureux de pouvoir en faire autant lol ) ...

cdlt

**VincentR** · 16/10/2008, 17h25

Re-bonjour, désolé mais j'ai trainé un peu pour trouver des infos dignes d'interet.

Pour la config des serveurs, c'est relativement costaud tout de meme, 8Gb de RAM, 2 x AMD Opteron 2.8 GHz – dual core, et la BD est hébergé dans un SAN de 1To.

Pour le plan, le voici:

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
SELECT STATEMENT  ALL_ROWS
Cost: 556  Bytes: 105,700  Cardinality: 3,020
--12 PX COORDINATOR
----11 PX SEND QC (ORDER) PARALLEL_TO_SERIAL SYS.:TQ10002 :Q1002
       Cost: 556  Bytes: 105,700  Cardinality: 3,020
------10 SORT ORDER BY PARALLEL_COMBINED_WITH_PARENT :Q1002
         Cost: 556  Bytes: 105,700  Cardinality: 3,020
--------9 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1002
          Cost: 555  Bytes: 105,700  Cardinality: 3,020
----------8 PX SEND RANGE PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001
            Cost: 555  Bytes: 105,700  Cardinality: 3,020
------------7 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1001
              Cost: 555  Bytes: 105,700  Cardinality: 3,020
--------------4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1001
                Cost: 2  Bytes: 4,320  Cardinality: 240
----------------3 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10000 :Q1000
                  Cost: 2  Bytes: 4,320  Cardinality: 240
------------------2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000
                    Cost: 2  Bytes: 4,320  Cardinality: 240
--------------------1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT OWB_WH_TARGET.DDS_PR_CHARACTERISTICS :
                      Cost: 2  Bytes: 4,320  Cardinality: 240
--------------6 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001
                Cost: 553  Bytes: 51,340  Cardinality: 3,020  Partition #: 11
----------------5 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT OWB_WH_TARGET.DDS_PR :Q1001
                  Cost: 553  Bytes: 51,340  Cardinality: 3,020  Partition #: 12  Partitions accessed #8054

Concernant mes recherches, j'ai réussit à créer des indexs qui plaisent à Oracle, des bitmaps qui pointent sur l'ensemble des champs de ma requete. Ceci dis, en terme de perf je ne vois pas de difference notable.

Ensuite voila ce que j'ai entendu, de la part de divers "experts".

Les index ne sont pas forcement la solution compte tenu de la taille d'une ligne qui dans mon cas est relativement petite comparé à la taille de mes blocks du table space qui sont de l'ordre de 8Ko. La justification technique est qu'un index pointe vers le block ou l'info est stocké, et que si mes 9000 lignes sont toutes dans des blocs differents et représentent seulement une partie infime du bloc retourné, le gain apporté par l'index n'est pas vraiment enorme comparé à un lecture complete des données.

Les solutions que nous envisageons sont donc les suivantes:

Etant donné la cardinalité déjà importante de notre partitionage, on ne peut pas envisager de sous sous partitionner. Par contre, j'ai entendu dire qu'il etait possible d'organiser les tables en fonction d'un index et cela pourrait rassembler nos données et limiter le nombre de blocs à lire. Dans ce cas il nous faudrait regrouper nos enregistrement par DEVICE_ID. Mais je ne suis pas sur de la validité de cette info et son cout pourrait etre important.
Diminuer la taille des blocs pour s'approcher de la taille d'une ligne. Ca implique un reprocessing complet de la table mais si les perfs sont augmentées en conséquence pourquoi pas
Créer des vues matérialisées. Je m'explique. Si on stocke dans des vues le résultat de cette requete, il me suffit alors de faire un select * dessus et la forcement les perfs sont correctes. On ajoute simplement un disque séparé sur lequel on fait aucun backup ou quoi que ce soit (histoire de pas bouffer l'espace du SAN), par contre on y met un nouoveau tablespace pour stocker nos vues et on les mets à jour de temps en temps. Pas besoin d'avoir toutes les requetes stokées, on ne marche que sur les 4 dernieres semaines. Ca nous permet d'avoir une quantité acceptable de requete avec des perfs excellente.

Vous en pensez quoi?

**ducho** · 16/10/2008, 17h49

Bonsoir,

en effet dans le livre ( et d' autres articles similaires) "optimisation des performances sous oracle", l' auteur explique que ,si pour ramener des lignes via un index, on lit autant de blocs de données que par un parcours séquentiel
de la table, on ne gagne pas d' I/O donc l' index n' est pas tres utile ...
le tout est de pouvoir faire le calcul correct de blocs de données parcourus
dans les deux cas ...
dans ce même livre, l' auteur déconseille des block size < 8k ...

pour le reste, il faudrait tester, ce qui ne doit pas être évident dans ton cas...

cdlt

**dragon74** · 17/10/2008, 09h57

Je confirme, malheureusement, que ce sera beaucoup de tests et puis regarder ce que cela donne.

Tu peux imaginer un sous-partitionnement sur device_id mais il serait aussi bon de savoir quel répartition tu as au niveau de tes données. Si tu retrouves 90% de tes records sur 2 ou 3 device_id, ca devient inutile comme sous partitionnement. L'idéal serait d'avoir une répartition plus ou moins égale sur ton sous-partitionnement.

Je sais que j'ai déjà eu des problèmes de perf suite à un nombre d'extents trop important. C'est peut-être aussi une piste ...

**VincentR** · 21/10/2008, 11h45

J'ai en fait déjà deux niveaux de partitionnement (1 partiton par semaine et 1 sous partition par caracteristique de valeur), je ne crois pas qu'un troisieme niveau soit possible...

**dragon74** · 21/10/2008, 14h20

En effet, pour moi, impossible de partitioner sur 3 niveaux.

As-tu regarder qu'au niveau de ta sous-partition, la répartition des données est plus ou moins uniforme?

Besoin d'avis sur une requête

SQL Oracle

Discussions similaires

Partager

Partager