Discussion :

[tropiko]

Bonjour à tous,

Suite une régression de perfs constatée sur 3 requêtes SQL , le DBA m’a envoyé 1 fichier issu de la console OEM pour les analyser(voir fichier joint).
Dans cette analyse je suis confronté à 2 problèmes majeurs :
1/ Comprendre les graphiques fournis pour chaque requête notamment les notions suivantes :
a/ DB time = CPU time + Active Wait Time ? % de chacun dans un cas normal ?
Le lien entre Active Wait time et User I/O ?
b/ Le lien entre User I/O et db file scattered read ou db file sequential read ?
2/ Analyser les constats suivants :
a/ Que veut dire une opération de TABLE FULL SCAN qui prend 80% CPU ? Est-ce normal ? solution ?
b/ Que veut dire une opération de INDEX FULL SCAN qui prend 70% de Wait Activity (db file sequential read) ? Est-ce normal ? solution ?
c/ Que veut dire une opération de INDEX RANGE SCAN qui prend plus de 80% de CPU ?
Est-ce normal ? solution ?
Enfin sur quelles informations dois-je focaliser (% CPU Activity ? % Waits Activity ? I/O Requests ? …) ?

Merci par avance pour votre aide précieuse.

[pachot]

Bonjour,

a/ DB time = CPU time + Active Wait Time ? % de chacun dans un cas normal ?

Oui. Le temps total passé en base de donnée (DB time) est le temps passé en CPU (CPU time) plus le temps passé à attendre sur un appel système (Active Wait Time )

Le lien entre Active Wait time et User I/O ?

User I/O est un wait parmi d'autre. Lorsque le process doit faire un I/O, il fait l'appel système correspondant et sors de la CPU en attendant le retour de l'I/O.

b/ Le lien entre User I/O et db file scattered read ou db file sequential read ?

'db file scattered read' et 'db file sequential read' sont 2 wait events de la classe User I/O. C'est le détail. Les différents wait events sont regroupés en classes (comme 'User I/O').

a/ Que veut dire une opération de TABLE FULL SCAN qui prend 80% CPU ? Est-ce normal ? solution ?

Un full scan effectué souvent sur une table pas trop grosse a des chances de trouver ses données en cache - donc peu d'I/O.

Dans le cas du 3ème plan dans le fichier word, ce full scan est exécuté 6049 fois dans la requête (col Executions). C'est probablement un problème de stats au niveau des prédicats du FILTER: estimation 1 ligne mais Actual Rows=6049

La première chose que je regarde sur une opération qui prends du temps est le nombre de fois où elle est exécutée ('Executions') et si c'est beaucoup, je remonte pour voir d'où vient le nombre de ligne de l'opération NESTED LOOP qui l'exécute plusieurs fois, et enfin je compare avec le nombre de lignes estimées.

Cordialement,
Franck.

[tropiko]

Bonjour Franck ,

Un full scan effectué souvent sur une table pas trop grosse a des chances de trouver ses données en cache - donc peu d'I/O.

Comment expliquer un temps CPU elevé sur des opérations de ce type (FULL SCAN) sans pour autant avoir beaucoup d'I/O ?

Dans le cas du 3ème plan dans le fichier word, ce full scan est exécuté 6049 fois dans la requête (col Executions). C'est probablement un problème de stats au niveau des prédicats du FILTER: estimation 1 ligne mais Actual Rows=6049

Afin de vérifier le comportement de l'optimiseur et sans recalculer les stats pour l'instant , je vais "setter" un num_rows issue d'un vrai select count(*) from la table en question puis dbms_stats.set_table_stats ...votre avis ?

La première chose que je regarde sur une opération qui prends du temps est le nombre de fois où elle est exécutée ('Executions') et si c'est beaucoup, je remonte pour voir d'où vient le nombre de ligne de l'opération NESTED LOOP qui l'exécute plusieurs fois, et enfin je compare avec le nombre de lignes estimées.

Comment vérifier si un index est sélectif ou pas sur un plan ?
Existe-t-il une subtilité concernant les INDEX RANGE SCAN ?
Comment expliquer un % Wait Activity élevé dans une opération INDEX RANGE SCAN ou INDEX UNIQUE SCAN (comme dans le premier plan du fichier joint).

Enfin comment diminuer les I/O ? forcer des objets à être en cache ?
comment ? risques et contraintes ?
Merci

[pachot]

Comment expliquer un temps CPU elevé sur des opérations de ce type (FULL SCAN) sans pour autant avoir beaucoup d'I/O ?

Si les blocs sont encore en buffer cache depuis le dernier full scan, il n'y a que des logical reads, pas d'i/o
Si les blocs ne sont plus en buffer cache, mais sont encore en filesystem cache au niveau de l'os on a des i/o mais très rapides. Donc le temps passé en i/o est négligeable apra rapport au temps passé en CPU.

Afin de vérifier le comportement de l'optimiseur et sans recalculer les stats pour l'instant , je vais "setter" un num_rows issue d'un vrai select count(*) from la table en question puis dbms_stats.set_table_stats ...votre avis ?

Ou simplement utiliser les hints CARDINALITY ou OPT_ESTIMATE pour forcer une estimation de cardinalité. Ou DYNAMIC_SAMPLING_EST_CDN pour forçer le dynamic sampling.

Comment vérifier si un index est sélectif ou pas sur un plan ?

En regardant Actual Rows. Dans le fichier attaché, premier plan, on voit un INDEX RANGE SCAN qui ramène 429 million de lignes, puis en allant voir la table pour chacune d'elle, on en élimine encore pour n'avoir que 31 million de lignes. L'index aurait pu être plus selectif en rajoutant les colonnes qui on filtré sur le TABLE ACCESS

Existe-t-il une subtilité concernant les INDEX RANGE SCAN ?
Comment expliquer un % Wait Activity élevé dans une opération INDEX RANGE SCAN ou INDEX UNIQUE SCAN (comme dans le premier plan du fichier joint).

Le problème c'est qu'on fait le range scan 593000 fois, pour chaque ligne de TIERS semble-t-il.

Code :

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

Enfin comment diminuer les I/O ? forcer des objets à être en cache ? comment ? risques et contraintes ?

Non ! Le cache sert à garder les blocs accédés souvent. La première question à se poser, c'est pourquoi on passe son temps à aller voir toujours les mêmes blocs.

A NOTER

[pachot]

Comment expliquer un temps CPU elevé sur des opérations de ce type (FULL SCAN) sans pour autant avoir beaucoup d'I/O ?

Si les blocs sont encore en buffer cache depuis le dernier full scan, il n'y a que des logical reads, pas d'i/o
Si les blocs ne sont plus en buffer cache, mais sont encore en filesystem cache au niveau de l'os on a des i/o mais très rapides. Donc le temps passé en i/o est négligeable apra rapport au temps passé en CPU.

Afin de vérifier le comportement de l'optimiseur et sans recalculer les stats pour l'instant , je vais "setter" un num_rows issue d'un vrai select count(*) from la table en question puis dbms_stats.set_table_stats ...votre avis ?

Ou simplement utiliser les hints CARDINALITY ou OPT_ESTIMATE pour forcer une estimation de cardinalité. Ou DYNAMIC_SAMPLING_EST_CDN pour forçer le dynamic sampling.

Comment vérifier si un index est sélectif ou pas sur un plan ?

En regardant Actual Rows. Dans le fichier attaché, premier plan, on voit un INDEX RANGE SCAN qui ramène 429 million de lignes, puis en allant voir la table pour chacune d'elle, on en élimine encore pour n'avoir que 31 million de lignes. L'index aurait pu être plus selectif en rajoutant les colonnes qui on filtré sur le TABLE ACCESS

Existe-t-il une subtilité concernant les INDEX RANGE SCAN ?
Comment expliquer un % Wait Activity élevé dans une opération INDEX RANGE SCAN ou INDEX UNIQUE SCAN (comme dans le premier plan du fichier joint).

Le problème c'est qu'on fait le range scan 593000 fois, pour chaque ligne de TIERS semble-t-il.

Code :

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

Enfin comment diminuer les I/O ? forcer des objets à être en cache ? comment ? risques et contraintes ?

Non ! Le cache sert à garder les blocs accédés souvent. La première question à se poser, c'est pourquoi on passe son temps à aller voir toujours les mêmes blocs.

Attention, les screenshots que tu as mis montrent quelle opération passe le plus de temps en CPU et quelle opération passe le plus de temps en I/O. On ne voit pas si le temps total (DB time) est surtout composé de temps CPU ou temps I/O. Il faut regarder au dessus en face de DB Time.

[tropiko]

Bonjour Franck ,

Attention, les screenshots que tu as mis montrent quelle opération passe le plus de temps en CPU et quelle opération passe le plus de temps en I/O. On ne voit pas si le temps total (DB time) est surtout composé de temps CPU ou temps I/O. Il faut regarder au dessus en face de DB Time.

Oui effectivement DB time est composé de CPU time et USER I/O mais comment faire le lien avec les métriques (par opération) que vous avez déjà citées.

Si une opération présente un temps CPU elevé et que le DB time n'est PAS majoritairement du CPU , que dois-je conclure ?

Enfin dans la majorité des cas je constate toujours une % temps CPU pour une opération de type parcours d'index ...est-ce suffisant pour mettre en cause l'utilisation de l'index (et prévoir un FULL SCAN de la table pour remédier ?)
Merci pour votre aide

[pachot]

Si une opération présente un temps CPU elevé et que le DB time n'est PAS majoritairement du CPU , que dois-je conclure ?

Si 20% du DB time est en CPU et que 60% du temps CPU est passé sur une opération X alors C'est que l'opération X n'est responsable que de 12% du temps total. Donc on l'oublie - sauf si notre objectif est de diminuer l'utilisation CPU plutôt que de s’intéresser au temps de réponse.

C'est le seul défaut que je trouve à ces rapports SQL Monitoring, le fait de présenter les waits et la CPU sur 2 colonnes. Et mon petit doigt me dit que ce sera mieux dans la prochaine version.

Enfin dans la majorité des cas je constate toujours une % temps CPU pour une opération de type parcours d'index ...est-ce suffisant pour mettre en cause l'utilisation de l'index (et prévoir un FULL SCAN de la table pour remédier ?)

Non. Ce n'est pas le fait de passer du temps en CPU qui est un problème. Mais c'est peut-être un symptôme qu'on va voir trop de blocs.