Discussion :

[maximepht]

Bonjour,

Je poste ce message afin de vous soumettre un problème de performance sur une base Oracle (9.2.0.7 ) utilisé par un soft nommé Aspentech.

Les requetes sont réalisés via un appel WebService, proprietaire de la solution Aspentech, et nous n'avons pas la main sur le code source et des appels réalisés, nous sommes donc dans l'impasse de ce côté.
Nous devons donc optimiser les indexs et les paramètres oracle (mémoire,..) pour répondre au mieux sur les appels effectués.

J'ai recuperé une requete qui est réalisée par ce soft (non modifiable et la plus souvent exécutée):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT batch_id FROM batches WHERE area_id = 3 AND  ( exists ( SELECT 
batch_id FROM CHAR_BATCH_DATA WHERE CHAR_BATCH_DATA.batch_id = batches.batch_id 
AND CHAR_BATCH_DATA.subbatch_id = 1203 AND parent_char_inst_path_id = 0 AND 
char_id = 107 AND char_inst > 0 AND (latest_action < 3 OR latest_action > 4) 
AND char_value = '19522')) AND  ( exists ( SELECT batch_id FROM CHAR_BATCH_DATA 
WHERE CHAR_BATCH_DATA.batch_id = batches.batch_id AND 
CHAR_BATCH_DATA.subbatch_id = 1203 AND parent_char_inst_path_id = 0 AND 
char_id = 103 AND char_inst > 0 AND (latest_action < 3 OR latest_action > 4) 
AND char_value = '038-CS100')) AND  ( exists ( SELECT batch_id FROM CHAR_BATCH_DATA 
WHERE CHAR_BATCH_DATA.batch_id = batches.batch_id AND CHAR_BATCH_DATA.subbatch_id = 1203 
AND parent_char_inst_path_id = 0 AND char_id = 98 AND char_inst > 0 AND 
(latest_action < 3 OR latest_action > 4) AND char_value = '2012')) 
ORDER BY batches.batch_id   ;

Nous aimerions créer un ou des indexs qui pourraient nous permettre de gagner en performance, pour cela je vous fournit quelques informations complémentaires:

- La table CHAR_BATCH_DATA comprend 36 millions de lignes (environ)et la table BATCHES environ 850000 lignes
- Voici l'occurence des valeurs pour les champs appelés sur la table char_batch_data :

batch_id - 36000000 (environ)
char_value - 871147
subbatch_id - 16697
char_id - 392
char_inst - 47
latest_action - 2
parent_char_inst_path_id - 0

Quelqu'un aurait une idée sur le type d'indexd et les champs utilisés pour obtenir de meilleures performances?

Aujourd'hui la situation est compliquée car beaucoup de traitement tournent au quotidien et les applications clientes ne repondent plus dans des delais acceptables
Si vous désirez des infos supplémentaires, je vous les fournit avec plaisir.

[pachot]

Bonjour,

Si vous désirez des infos supplémentaires, je vous les fournit avec plaisir.

Il faudrait voir le plan d'exécution.

D'une manière générale, un index optimal est celui qui:
- ne va chercher que les infos dont on a besoin (selectivité)
- fournit toutes les colonnes nécessaires sans aller voir la table
- et les fournit dans l'ordre désiré.

Mais ce sera plus facile à voir avec le plan d'exécution (complet - avec les prédicats - telle que le fournit dbms_xplan)

Cordialement,
Franck.

[maximepht]

Bonjour,

Merci pour ce retour.
Veuillez trouver les informations dans le fichier en pièce jointe.

Cdlt,

[pachot]

L'optimiseur pense ne ramener que très peu de lignes. Est-ce que les stats sont à jour, ou ont-elles été calculées sur des tables vides ?

[maximepht]

Bonjour,

Non les stats ont justement été calculées recemment sur l'ensemble du schema concerné.
Qu'est ce qui vous fait dire cela "L'optimiseur pense ne ramener que très peu de lignes" ? Comment puis-je y remédier?

Cdlt,

[McM]

Tu as essayé en modifiant la requête, peut être que les 3 accès à char_batch_data sont longs.

Faut voir le plan et la durée d'une requête comme celle ci.
Par contre il faudrait avoir un index différent (batch_id, subbatch_id, parent_char_inst) Les autres colonnes, peut être pas, faut tester, ça dépend des données.

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
SELECT DISTINCT batch_id 
FROM BATCHES b 
WHERE area_id = 3 
AND EXISTS ( SELECT 1 
		FROM CHAR_BATCH_DATA d
              WHERE d.batch_id = b.batch_id 
		AND d.subbatch_id = 1203 
              AND d.parent_char_inst_path_id = 0 
              AND d.char_inst > 0 
              AND d.latest_action NOT BETWEEN 3 AND 4 
		AND ((d.char_id = 107  AND d.char_value = '19522')
              	OR (d.char_id = 103 AND d.char_value = '038-CS100' )
                  OR (d.char_id = 98 AND d.char_value = '2012') 
                  ))
ORDER BY batch_id

Petite précision : Attention, le coût d'un explain plan supérieur ne veut pas dire que ça va aller moins vite.

[pachot]

Bonjour,

Qu'est ce qui vous fait dire cela "L'optimiseur pense ne ramener que très peu de lignes" ? Comment puis-je y remédier?

Dans le plan d'exécution, la colonne 'Rows': 1 ligne, c'est peu...
On dirait que les stats ont été calculés avec peu ou pas de données.

Cordialement,
Franck.

[mnitu]

Qu'est ce qui vous fait dire cela "L'optimiseur pense ne ramener que très peu de lignes" ? Comment puis-je y remédier?

Si vous regarder votre plan vous allez trouver dans la colonne Rows seulement la valeur 1 qui signifie que à chaque étape du plan l’optimiseur estime qu’un seul enregistrement passera. Or cella n’est pas le cas ! Or des estimations erronées dans le plan d’exécution égal mauvais plan d’exécution. Le plus souvent cela est le signe des statistiques incorrectes mais des autres causes peuvent intervenir également (données avec des distributions non uniformes, données corrélées, etc.)
Pour remédier (sur votre version 9.2.X) il n’y a pas des miracles : il faut comprendre l’origine du problème.

1. Commencer par vous assurer que l’optimiseur travail en mode coût. Quelle est le paramétrage de l’optimiseur ?
2. Assurez-vous que les statistiques sont à jour. Comment sont les statistiques calculées, avec quelle commande et quels paramètres ? Y a-t-il des histogrammes ou pas ? Si oui ces histogrammes sont nécessaires ou pas ?
3. Exécutez la requête avec une trace sql étendue. Attention si la requête utilise des variables de liaison faite la trace avec des variables de liaison. Extrayez via tkprof le plan réel d’exécution de la requête et comparez les cardinalités réels avec les estimations.
4. Cherchez les « premières » étapes du plan où la différence entre les cardinalités estimés et ceux réels est importante. Trouvez une explication à cette différence.
5. Eventuellement, essayez d’imaginer le « bon plan d’exécution » et d’aiguiller l’optimiseur vers ce plan pour le valider vos idées. Utilisez des hints sql !
6. Très rarement : faite une trace de l’optimiseur pour voir les calculs qu’il utilise pour prendre ses décisions.

Bon courage