Temps de requête trop long [Résolu]

[cedrich]

Bonjour,

Quand j'exécute une requête SQL, son temps d'exécution est (trop) long. J'ai effectué une trace et j'obtiens le résultat (uniquement le cas pertinent) suivant :

________________________________________________

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT  DATE_BON, 
 AS_PROD_VEHI_ID, 
 BON_NUMERO, 
 BON_SEQUENCE, 
 DB_ELEMENT_ID, 
 ID, 
 DO_LOT_ENT_ID ,
 rowid 
FROM DO_LOT_BON 
WHERE DATE_BON =  :1 AND 
 AS_PROD_VEHI_ID =  :2    AND 
 BON_NUMERO =  :3 AND 
 BON_SEQUENCE =  :4  AND 
 DB_ELEMENT_ID =  :5    
ORDER BY rowid  ASC

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 2 0.00 0.00 0 0 0 0
Execute 32 0.00 0.00 0 0 0 0
Fetch 32 1.68 9.86 78656 80576 0 32
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 66 1.68 9.86 78656 80576 0 32

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Misses IN library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25  (IDINFO)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT ORDER BY 
     10   TABLE ACCESS FULL DO_LOT_BON 
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     10   SORT (ORDER BY)
     10    TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'DO_LOT_BON'

_____________________________________________________

J'ai déjà modifié (augmenté) le cache tampon (SGA) et la mémoire PGA.

Le résultat obtenu est :
_____________________________________________________

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------
Parse 2 0.00 0.00 0 0 0 0
Execute 32 0.00 0.00 0 0 0 0
Fetch 32 2.00 8.07 28027 82804 0 32
------- ------ -------- ---------- ---------- ---------- ---------- -------
total 66 2.00 8.07 28027 82804 0 32

_____________________________________________________
Le temps "elapsed" a diminué, par contre le temps "CPU" a légérement augmenté ainsi que le nombre de "query".

Pourriez-vous m'indiquer ce que je dois faire pour obtenir un temps plus bas? Encore augmenter les 2 paramètres SGA et PGA??

Auriez-vous des références à des documents, forums, ... pour optimiser les requêtes depuis une trace? Car je vois bien qu'il y a un problème et je ne sais pas sur quoi je dois agir pour le corriger.

En vous remerciant d'avance.

Cédric

[orafrance]

Tu ne prends pas le problème dans le bon sens, on ne connait pas l'explain plan, les waits ni rien... comment pourrait-on t'aider ? Pourquoi avoir augmenter la SGA (à noter que la SGA n'est pas le cache tampon) et PGA ?

PS : c'est pas la 1° qu'on te demande de mettre les balises

[cedrich]

Désolé, c'est mon deuxième poste et la dernière fois c'était au mois de septembre! J'y ferais plus attention.

Précision : J'ai augmenter le paramètre du cache tampon (paramète dynamique) qui dépend de la mémoire allouer à SGA afin de diminuer le nombre d'accès disque. Pour le paramètre PGA, j'avais un pourcentage de succès en mémoire cache de ~64% et il devrait être aux alentours de 95%, non?

J'ai ajouté l'explain plan dans le post de départ. Une trace pour une session n'indique pas d'autres informations. Je dois faire un "snapshot" pour avoir plus d'informations, n'est-ce pas?

Désolé de ces questions de débutants et ce sont des éléments que je découvre, car avant j'utilisais la trace de session que pour les indexes car les paramètres par défaut d'Oracle suffisait.

Merci pour les liens!

[nuke_y]

Table Access full ?

Tu n'as aucun index sur la table ? Combien de ligne fait-elle ? Combien en ramènes-tu avec ta requête ?

[orafrance]

Envoyé par cedrich

Précision : J'ai augmenter le paramètre du cache tampon (paramète dynamique) qui dépend de la mémoire allouer à SGA afin de diminuer le nombre d'accès disque. Pour le paramètre PGA, j'avais un pourcentage de succès en mémoire cache de ~64% et il devrait être aux alentours de 95%, non?

c'est typique d'un mauvais tuning. Avant de modifier quoi que ce soit à la base il faut d'abord comprendre ce qui se passe. Pour le cache, il me semble qu'en FTS les blocs ne sont pas mis en cache et quand bien même, si tu n'as pas clairement détecté un problème sur les accés disque le cache n'a pas à être touché.

Ceci étant, le problème c'est pas la base mais la requête... FTS c'est pas bon, vérifie index et stat.

Tu peux aussi enlevé le ORDER BY rowid

[cedrich]

Les deux index que j'ai sont :

• la clef primaire (id)
• la clef étrangère sur son parent (do_lot_bon_entete_id)

Le nombre de ligne dans la table est de 116854, le nombre de ligne retourné est de 151.

En exécutant le requête seul, le résultat est quasi instantané. Tandis que dans l'application c'est le contraire.

Je n'ai pas directement la main sur la requête. C'est l'application (Magic) qui se charge de convertir les demandes de condition (filtre) des développeurs en requêtes SQL. Par contre, il doit être possible de modifier les paramètres du language Magic. Car le problème ne vient peut-être pas directement d'oralce.

[orafrance]

bah voila... je crois que c'est clair, il manque un index au moins sur une partie des colonnes limitant le résultat

[cedrich]

D'accord!

Mais je ne vois pas comment vous êtes arrivé à cette conclusion.

Et c'est quoi un FTS???

Promi je ferme le post après!

Merci!

[orafrance]

FTS = FULL TABLE SCAN = parcourt de toutes la table ce qui signifie qu'il n'y a pas d'index sur les colonnes de la clause WHERE

[nuke_y]

Ce qui m'étonne c'est que la requête exécutée toute seule soit quasi instantannée. Dans ce cas là le problème vient soit du tuyau entre Oracle et l'application (mais bon pour 150 lignes...) soit de l'application elle même et là c'est grave.

Est-ce que tu es SUR que la requête exécutée par l'application est la même que celle que tu exécutes à la main ?

[cedrich]

Pas tous a fait!

Apparament il parcours une boucle dans laquelle, il va chercher X fois des informations pour chaque élément de la boucle. Ce n'est donc pas la même chose.

J'ai ajouté deux index sur la table DO_LOT_BON et le traitement c'est effectué nettement plus vite.

Merci bcp et à l'avenir je crérai un index sur un des éléments de la clause where!

[nuke_y]

Un des éléments oui, mais le plus sélectif si possible (ou alors le plus utile, m'enfin on va partir en débat là).