6s execute / 277s fetch et changement de plan d'exécution

Version imprimable

26/12/2017, 17h19
stof

6s execute / 277s fetch et changement de plan d'exécution
Hello,

J'ai une requête qui me pose pas mal de soucis de performances depuis un moment.
Au début j'étais en Hibernate et puis à un moment donné les performances se sont dégradées (sans que le code ait changé), j'ai tenté différents "patchs" et au final j'ai été carrément vers une solution d'appel de la requête en SQL natif.

Code:

getConnexion().prepareStatement(sqlRequest).executeQuery();

L'application utilise le diriver odbc com.oracle.jdbc.odbc.11-5.1-JDK5.jar pour parler à la base Oracle11.
- Ce matin, les DBA me disaient que ce qui était long c'était pas l'exécution mais la partie fetch (6s / 277s).
- Cet après-midi, tout est de nouveau rapide sans qu'aucune configuration n'ait changé (6s au total ==> 0s / 6s)
Note : personne d'autre que moi ne travaille sur la base pour expliquer cette différence et dès ce matin on avait tenté un recalcul d'index sans que ça n'influe

Du coup je recontacte les DBA qui me disent que la requête (toujours la même) utilise cet après-midi des plan d'exécution différents que ce matin.
(mais là je comprends pas car ce qui prend le plus de temps c'est le fetch donc si je ne me trompe pas le plan d'exécution influence le temps...d'exécution).

Comment ça se fait que ça switche de plan d'exécution?
Ils me disent qu'on peut forcer l'utilisation d'un plan d'exécution mais qu'ils auiment pas trop car c'est un peu moche comme solution.
27/12/2017, 08h31
OButterlin

Que dire sinon "change de DB ou de DBA" :mrgreen:
En tout cas, il semble que la cause ne soit pas dans la couche JDBC...
Si déjà on avait une idée de la requête et de la structure de la DB, il serait envisageable de t'aider...
27/12/2017, 09h46
stof

Alors la requête ça va pas être possible car elle est évidemment pas simple (et elle utilise aussi des vues, ce qui peut être le plus intéressant à signaler car je suis pas sûr que les vues utilisent correctement les index de tables).

La structure de la DB ==> quelles infos seraient utiles?

PS : ce matin ça marche toujours (alors que depuis plusieurs semaines c'était quasiment en permanence 100 fois trop long)
27/12/2017, 16h41
dinobogan

Peut-être un problème de volumétrie des tables utilisées ? Si par exemple les tables sont juste assez remplies pour toutes tenir en RAM, alors la requête sera rapide.
Par contre, si une ou plusieurs tables est trop grosse pour tenir en RAM, alors Oracle va supprimer des données de la RAM. S'il en a besoin, il fera des accès disques.
As-tu contrôlé la taille des tables et de l'espace RAM restant pour Oracle ? Il ne faut pas oublier le stockage temporaire créé par Oracle pour répondre à ta requête, par exemple un produit cartésien entre deux tables.
Si tu es le seul utilisateur et que ton programme ne change pas, il est probable que se sont les données dans Oracle qui changent et influence le temps de réponse, ce qui pourrait expliquer également le changement de plan d'exécution.
28/12/2017, 15h37
stof

Pour l'instant on est passé de 4Go à 8Go ==> on n'observe pas d'amélioration.
Par contre je pensais que c'était différent via l'application ou requêteur mais en testant dans les mêmes conditions (flush de la mémoire avant test) c'est pareil.
Le problème est donc bien purement dans la base.

Sinon l'advisor d'Oracle nous indique environ 10 index (dont 6 sur des tables de moins de 500 lignes donc je ne suis pas sûr que ça soit pertinent) et d'autres sur lesquels fonctionnellement j'ai des doutes par rapport à la requête qui pose problème mais bon...(j'ai essayé ==> idem)

MAIS LE GROS SOUCI : 6s pour exécuter la requête et 200s pour sortir les données ==>comment on agit sur ça?

PS : à propos du buffer ==> augmenter le buffer de SQLPlus n'a rien changé non plus
28/12/2017, 16h45
dinobogan

Je comprends mal. Des barrettes de RAM ont été ajoutées dans la machine et le temps d'exécution de la requête n'a pas changé ?
Il ne faut pas oublier de modifier la config d'Oracle pour lui dire d'utiliser plus de RAM. Cette modif a été faite aussi ?

Quel est le volume de données lues par le programme Java pour cette requête ?
La base Oracle est en local ou sur une machine distante ?
Si la base est en local, est-ce qu'Oracle est configuré pour passé par le loopback et non la carte réseau ?
Si c'est en réseau, que disent les statistiques des différentes cartes réseaux (volume, nombre de messages...) ?
Les 200 secondes, est-ce réellement en "sortie d'Oracle" ou en fin de traitement du programme Java ? Peut-être est-ce un accès disque très lent effectué par le programme Java ? Le chronométrage calcule le temps global de la requête ou du programme complet ? Et si, dans le programme Java, il n'y a que la lectures des données issues d'Oracle et rien d'autre, que di tle chronométrage ?
Et cette même requête lancée depuis une console Oracle, est-ce aussi long ?
28/12/2017, 17h19
stof

Citation:

Envoyé par dinobogan

Je comprends mal. Des barrettes de RAM ont été ajoutées dans la machine et le temps d'exécution de la requête n'a pas changé ?
Il ne faut pas oublier de modifier la config d'Oracle pour lui dire d'utiliser plus de RAM. Cette modif a été faite aussi ?

En fait la machine a plusieurs bases et il restait de la RAM disponible pour être allouée à la base dont je me sers.
Donc on n'a pas ajouté de barrettes mais alloué 8Go à ma base au lieu des 4 Go.

Citation:

Envoyé par dinobogan

Quel est le volume de données lues par le programme Java pour cette requête ?
La base Oracle est en local ou sur une machine distante ?

Volume je sais pas (comment mesurer?) mais c'est bien un serveur distant du serveur de l'application (par contre quand mon DBA a fait sa requête SQLPlus je suppose que c'était sur le serveur de la base). En tous cas le serveur est en France comme ce qui l'interroge.

Citation:

Envoyé par dinobogan

Si c'est en réseau, que disent les statistiques des différentes cartes réseaux (volume, nombre de messages...) ?

Je crains que ça soit une autre équipe à solliciter...

Citation:

Envoyé par dinobogan

Les 200 secondes, est-ce réellement en "sortie d'Oracle" ou en fin de traitement du programme Java ?

C'est la partie "FETCH" quand on regarde les statistiques. A noter que le DBA m'a dit qu'il n'y avait que de la CPU utilisé, pas d'I/O.
Tu peux oublier la partie Java en fait, on a pu reproduire dans un requêteur (je pensais que c'était que par l'application, sans doute mis sur une fausse piste par les données en cache qui répondaient plus vite quand j'exécutais hors de mon appli).

Pour l'instant comme on n'a pas didée, je crois qu'on va utiliser la méthode "profil" qui force l'utilisation d'un plan d'exécution qui marche (apparemment il existe aussi du "sql plan baseline" mais on n'utilise pas trop ça ici et il y a eu une mauvaise expérience dans le passé d'après ce que j'ai compris).

EDIT : effectivement pour l'instant dans ce mode "profil" les perf sont raisonnables et stables. Faute de mieux...
04/01/2018, 20h30
JeitEmgie

Citation:

Envoyé par stof

C'est la partie "FETCH" quand on regarde les statistiques. A noter que le DBA m'a dit qu'il n'y avait que de la CPU utilisé, pas d'I/O.

Pistes :

- Taille des rows / page size et ORDER BY dans les vues
- Swaps de contexte : vos vues comprennent-elles des fonctions PL/SQL ?
05/01/2018, 03h02
tchize_

je vais être méchant, mais quand une chose pareille se produit sur oracle, il n'y a malheureusement qu'une chose que tu peux faire à ton niveau: foutetter le DBA jusqu'à ce qu'il réponde à tes question et te donne une solution.

C'est ta requête / tes tables qui pour une raison X ou Y sont pourraves. Ce n'est pas un reproche, ça arrive. Les DBA oracle, c'est payé en général cher et méchant pour une bonne raison: leur boulot au delà de la maintenance, c'est de t'aider à optimiser tes tables, tes requêtes, etc. C'est à eux d'analyser les plans d'exécution, les tables, les index pour expliquer

1) pourquoi cette requête met 6 satanées secondes à répondre
2) pourquoi elle brasse tant de données
3) établir un plan pour une solution qui peut se résumer à 4 options
= ta requête commet des erreurs qui peuvent être évitée, à eux de te la corriger
= ce que tu veux faire est impossible avec oracle, il faut envisager d'autres technologies, d'autres méthodologie ou carrément abandonner la feature
= la base a simplement grandi avec le temps et les ressources ne sont plus adaptée, il faut agrandir l'infrastructure
= le hardware est dans le choux et un truc est occupé de lacher sur le serveur, amenant la confusion dans l'analyzeur de la DB.

PS: pour avoir utilisé oracle, ce n'est pas inhabituel qu'il ne prenne pas à chaque fois le même plan d'exécution. En général ça n'a pas trop d'influence

Au niveau du forum, si c'est une connerie dans une tables / vue / requête, il va falloir nous donner du grain si tu veux qu'on t'aide (structure des tables, index, taille de données concernée, construction des vues, requête)

Par contre, ce qui me chiffonne, c'est que tu n'aie pas d'idée du volume concerné. Ca devrait être la base avant même d'écrire la requête. On ne se permet pas les mêmes requêtes sur une table de 500 lignes ou sur une table de 500 millions.
De la même manière on ne va pas écrire de la même manière une méthode qui retourne 10 lignes ou une qui en retourne 10.000