problème d'optimisation avec un ensemble de requêtes SQL

Version imprimable

26/04/2006, 19h46
marti

problème d'optimisation avec un ensemble de requêtes SQL

Bonjour,
Mon application 3-tiers est interactive. Un utilisateur appelle une de ses fonctions, qui execute successivement une séries de requêtes SQL UPDATE vers une même table sous Oracle, puis reçoit la réponse de l'application.
Dans un premier temps, la table en question contient 7000 libellés et le temps de réponse est 2 secondes. Puis, la table contient 25000 libellés et le temps de réponse augmente à 25 secondes( intolérable pour une fonction interactive !!!! ).
Comment expliquer cette brutale augmentation ? et surtout comment peut-on faire pour le ramener à une valeur convenable ? merci pour votre aide
26/04/2006, 20h23
pifor

C'est vraiment difficile de répondre sans connaître:
- votre version d'Oracle
- la description de la table (CREATE TABLE complet)
- les index sur la tables (CREATE INDEX complet)
- la requête complète
- le plan d'exécution de la requête
- comment vous calculez les statistiques pour la table/les index
- une trace SQL de l'exécution analysée par tkprof.
- la requête est-elle executée avec un peu, pas mal, beaucoup d'activités concurrente sur le même système, la même base, la même table ?
- ce temps d'exécution est-il reproductible ou variable ?

Je trouve que mettre à jour des milliers de lignes dans une table n'est pas une fonctionnalité interactive très courante voire souhaitable ...
26/04/2006, 20h50
marti

A chaque appel interactif, on met a jour seulement 14 lignes( et non pas un millier de lignes....). De plus, qu'il y ait ou non un index sur la table, les temps de réponse ne changent pas. Je peux fournir ci-dessous un plan d'execution d'une des requêtes de mises à jour( avec ou sans index ) :

------- AVEC INDEX -----------
SQL> update /*+ INDEX(valva, valva_pk) */ valva set valva_sor = 'sdqdq' where upper(idt_valva_enq) =
'PRHTEST' and upper(idt_valva_lot) = 'PCS1' and idt_valva_gpur = 0 and idt_valva_ur = 2 and idt_val
va_lib = 1 and idt_valva_va = 12 ;
1 ligne mise à jour.
Ecoulé : 00 :00 :01.09
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=27 Card=1 Bytes=69)
1 0 UPDATE OF 'VALVA'
2 1 INDEX (FULL SCAN) OF 'VALVA_PK' (UNIQUE) (Cost=26 Card=1
Bytes=69)

Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
1684 consistent gets
1684 physical reads
388 redo size
868 bytes sent via SQL*Net to client
765 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
------ SANS INDEX ---------
SQL> update valva set valva_sor = 'sdqdq' where upper(idt_valva_enq) = 'PRHTEST' and upper(idt_valva
_lot) = 'PCS1' and idt_valva_gpur = 0 and idt_valva_ur = 2 and idt_valva_lib = 1 and idt_valva_va =
12 ;
1 ligne mise à jour.
Ecoulé : 00 :00 :01.06
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'VALVA'
2 1 TABLE ACCESS (FULL) OF 'VALVA'

Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
5064 consistent gets
5055 physical reads
388 redo size
868 bytes sent via SQL*Net to client
735 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
26/04/2006, 21h10
dyvim

Je vais tenter quelques postulats...

- Ton application affiche les données d'une table de 27000 lignes
- Elle charge complètement cette table via un sélect
- Elle permet de faire des update sur la table après avoir modifié des valeurs que tu as affiché (mettons par exemple 10 ou 250 ou encore 2000 update d'un coup) (les update ne résultent pas d'un process/calcul métier mais de saisies "manuelles")
- Après l'update elle recharge la table intégralement.

Une solution simple est d'utiliser un "tampon" qui stocke les données de ta table sur le serveur et les modifie sur le serveur (système du dataset en .net) et les rapatrie vers la base quand on l'estime nécessaire.

autre possibilité:
- ton client lance un process/calcul métier qui update massivement la table/base et il veut pouvoir voir le résultat très vite.

Alors là soit tu applique la première solution et tu applique le processus métier sur les données du dataset avant de les rapatrier vers la base.
(ca ne me semble pas top mais pourquoi pas)

Soit tu travailles sur la base et tu essaie seulement d'améliorer la rapidité d'obtention des données donc d'affichage.
Pour cela plusieurs possibilités:
- améliorer la pertinence des index. peut être qu'un index sur les colonnes utilisées dans le sélect suffira ou même qu'il n'y a aucun index ;)
- rafraichir les statistiques ou les fixer si tu sais ce que tu auras dans la table. Ceci si tu utilises le CBO (optimiseur de requete d'oracle basé sur le cout et pas sur les index) c'est le cas par défaut depuis la 8i.
- préciser à Oracle que tu veux ramener rapidement les 1/10/100/1000 premières lignes pour que ton client voit quelque chose rapidement (cela se fait via un "hint" FIRST_ROWS).

Plus nous aurons d'info et plus nous pourrons t'aider.;)
26/04/2006, 21h59
pifor

Pour résoudre un problème, il faudrait qu'on ait plus de données !

Vous parlez de temps de réponse de 2 et 25 secondes et ceux affichés sont de 1 seconde ? Il faudrait qu'on ait la trace SQL de tout ce qui est exécuté.

Vous dites que 14 lignes sont mises à jour mais dans vos traces il n'y en a qu'une seule ?
Un détail frappant dans les résultats retournés par "set autotrace" est que vous faites systématiquement des entrées/sorties puisque physical reads = consistent gets. Ces chiffres proviennent-ils d'un environnement de test ou de production ? Il faudrait peut-être agrandir le cache de la base de la donnée à moins qu'il ne soit intentionnellement vidé avant chaque exécution de votre test ?
26/04/2006, 23h14
marti

Les 14 update en question sont appelés dans une classe java( l'update ci-dessous en exemple est répété 14 fois avec i variant de 1 à 14 ). Qu'il y ait un index posé sur toutes les colonnes de la clause Where ou non( les 14 colonnes en question étant l'identifiant de chaque ligne de la table valva ), cela ne change rien :

update valva set valva_sor = 'sdqdq' where upper(idt_valva_enq) = 'PRHTEST' and upper(idt_valva
_lot) = 'PCS1' and idt_valva_gpur = 0 and idt_valva_ur = 2 and idt_valva_lib = 1 and idt_valva_va = i ;

Je comprends que c'est difficile d'analyser de loin mon problème mais je recherche n'importe quelles piste de solution

Merci
27/04/2006, 07h59
orafrance

merci d'utiliser les balises CODE

C'est pas possible de faire un IN avec les 14 valeurs plutôt 14 fois l'update ?
27/04/2006, 08h56
dyvim

Tu as des UPPER sur tes colonnes dans la partie gauche de tes clauses WHERE...

A moins que tu ais créé des Index de fonction cela invalide tes INDEX...
même si tu spécifie que tu les utilise (via le HINT) ils ne te servent à rien...

Enlève les UPPER ou créee des INDEX sur la valeur UPPERisée de tes colonnes.
27/04/2006, 09h45
marti
Bonjour,

J'ai changé l'index en y mettant des UPPER sur les colonnes en question :
Code:

1 2 3 4 5 create index valva_idx on valva(upper(idt_valva_enq), upper(idt_valva_lot),idt_valva_gpur, idt_valva_ur, idt_valva_lib, idt_valva_va); commit;
Les temps de réponses n'ont pas changé. Par contre, comment pourrait-on regrouper les update en un seul passage dans une requête avec le IN ? que signifie l'info physical reads = consistent gets ?( je crois que le mieux est d'avoir physical reads = 0 ? )

Désolé pour ma grande ignorance sur le sujet, merci pour votre aide

Editer par bouyao : :tagcode:
27/04/2006, 15h02
pifor

Citation:

que signifie l'info physical reads = consistent gets ?( je crois que le mieux est d'avoir physical reads = 0 ? )

Cela signifie que Oracle n'a trouvé aucun bloc dans le buffer cache et qu'il a systématiquement fait une lecture disque pour lire les données.

Vérifiez que les paramètres db_block_buffers ou db_cache_size sont assez grands dans le init.ora de votre base.