Discussion :

[bigbull]

Bonjour à tous,

Grand débutant et en autoformation, j'ai la responsabilité d'une base oracle 10g sur un serveur windows 2003 64bits sur la quelle se connecte une centaine d'utilisateurs et on me demande d'analyser les performances de la base car certains utilisateur se plaignent de lenteurs. Le serveur ne montre aucune activité particulière suspecte.

En farfouillant un peu de partout j’ai trouvé des commandes du style :
@?\RDBMS\ADMIN\awrrpt ou @?\RDBMS\ADMIN\sqltrpt

Mais je ne sais pas les exploiter, quelqu’un pourrait ils me donner une ou deux clefs pour savoir quoi regarder pour faire une première analyse et savoir quoi transmettre a un vrais BDA pour correction.

Par avance merci.

[Mohamed.Houri]

Commencer par poster ici les parties suivantes de votre rapport AWR

1. Load Profile
2. Instance Efficiency Percentages (Target 100%)
3. Top 5 Timed Foreground Events

Et on vous dira alors, peut-être, sur quoi il faut vous concentrer

[bigbull]

Et voilà

Par avance merci.

[ojo77]

Visiblement ça passe du temps à lire des trucs,

Voire le top sql by reads et les objets les plus accédés en lecture

[bigbull]

revoilà

Quels sont les lignes à surveiller et l'ordre de grandeur pour des valeurs significatives ?

[Mohamed.Houri]

Et voilà

Par avance merci.

Quelle est la durée de ce rapport?

Code :

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

1
2
3
4
5
6
7
 
           Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     32371 04-Jun-12 09:00:55       113       2.9
  End Snap:     32372 04-Jun-12 10:00:57       112       3.0
   Elapsed:               60.04 (mins)
   DB Time:               68.95 (mins)

Quelle est le nombre de CPU de la machine?

Est-ce que ce rapport a été pris lorsque la base de données souffrait d'un problème de performance?

Quelle est aussi la valeur du paramètre DB_FILE_MULTIBLOCK_READ_COUNT

Code :

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

1
2
3
4
5
6
 
SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_file_multiblock_read_count        integer     71

Et enfin, quelle type de base de données : OLTP, DWH, OLAP?
Sur la base de ces informations, je vous donnerai quelques pistes à exploiter

[bigbull]

Quelle est la durée de ce rapport?

5 jours, du coup en lisant la question je me demande si je ne devrais pas en relancer 1 sur une période plus courte... on m'a signalé que les lenteurs durait depuis cet intervalle.

Quelle est le nombre de CPU de la machine?

8 cœurs

Est-ce que ce rapport a été pris lorsque la base de données souffrait d'un problème de performance?

Oui c’est celà

Quelle est aussi la valeur du paramètre DB_FILE_MULTIBLOCK_READ_COUNT ?

db_file_multiblock_read_count integer 16

Et enfin, quelle type de base de données : OLTP, DWH, OLAP?

OLTP, la base stocke les dossiers administratifs de clients et est mise à jours tout les jours.

[Mohamed.Houri]

Quelle est la durée de ce rapport?
5 jours

5 jours !!!!

Vous comprenez maintenant pourquoi il faut poser des questions avant de sauter aux conclusions?

Toute conclusion faite sur la base de ce rapport AWR de 5 jours n'est pas fiable.

Générez un rapport de 30 minutes pendant la période où la base de données n'est pas performante et postez le de nouveau ici

[bigbull]

Bonjour,

Merci j'ai au moins appris à cibler mes rapports.

Après enquête auprès des utilisateurs voici un rapport du 11 juin de 11h à 12h, je n'ai pas intervalle plus court lors de la sélection des snap ID

[Mohamed.Houri]

Quelques remarques :

Vous avez presque 1million de lecture en db file scattered read (FULL TABLE SCAN ou INDEX FAST FULL SCAN) et 340,692 lecture de db file sequential read (INDEX SCAN). Votre base de données étant du type OLTP la lecture par mutli-block (db file scattered read) ne doit pas être aussi grande lorsqu’elle est rapportée à la lecture par single-block (db file sequential read) ; c’est donc la première grosse anomalie que je peux relever dans votre rapport.

Le deuxième point sombre est la lecture moyenne par block à savoir 10ms pour le multi-block et 11 ms pour le single block. Ce n’est pas un bon temps moyen de lecture.

Le dernier ''event'' ( read by other session) signifie que quelqu’un voulait lire un block, il ne l’a pas trouvé dans le buffer cache, il a voulu le lire du disque et là il s’est rendu compte qu’un autre est en train de le lire du disque pour le mettre dans le cache. En soit, quelqu’un est en train de faire un travail que l’autre devait le faire. Au lieu que les deux sessions attendent sous l’event db file sequential ou scattered read, l’un se met en attente sous ''read by other session''. Autant dire que les 3 events sont liés.

Vous devez réduire les FULL TABLE (ou INDEX) SCAN. Allez à la partie SQL ordered by Reads et observez les SQL qui sont montrés en premier. Vous pouvez aussi consulter la partie Segments by Table Scan à la recherche des segments accédés via une lecture multi-block .

La valeur de db_file_multiblock_read_count ,à partir de la version 10.2.0.1, ne devrait plus être définie. Il faut laisser Oracle décider dynamiquement de la valeur à utiliser. Dans votre cas, c’est peut-être que votre base de données a été créée en utilisant DBCA (Database Configuration Assistant), qui, lui, attribue un db_file_multiblock_read_count à 16. Une valeur de 128 déterminée dynamiquement semble être la valeur qui convient le plus dans votre cas.

[mnitu]

Il y a deux approches différentes pour traiter les problèmes de performance d’une base Oracle. Celui que vous utilisez vous permet d’identifier les problèmes mais il ne vous permet pas de cibler facilement les traitements qui les provoquent. Néanmoins, cella ne signifie pas que cet approche est moins utile.

L’autre approche prend comme point de départ le constat des utilisateurs via une approche métier. L’utilisateur X signale que pendant l’exécution de la tâche Y les temps de réponse est dégradé. Dans ce cas activez la trace SQL et analysez ce qui se passe éventuellement en utilisant en outil de profilage (tkprof, tvdxstat, oracle profiler, etc.). De cette façon vous pouvez cibler très précisément les endroits où vous devez intervenir et évaluer les divers méthodes d’intervention envisageables.

[bigbull]

"Le deuxième point sombre est la lecture moyenne par block à savoir 10ms pour le multi-block et 11 ms pour le single block. Ce n’est pas un bon temps moyen de lecture."

Qu'est ce qu'un bon temps moyen de lecture ?

"Une valeur de 128 déterminée dynamiquement semble être la valeur qui convient le plus dans votre cas"

je n'ai pas trouvé la valeur à mettre pour que ce soit dynamique. Dois je modifier directement la valeur avec alter system ou est il préférable de modifier le spfile.ora ?

"Dans ce cas activez la trace SQL et analysez ce qui se passe éventuellement en utilisant en outil de profilage"

Je vais me documenter sur ça et tester merci.

[Mohamed.Houri]

"Qu'est ce qu'un bon temps moyen de lecture ?

1 ms .. 5 ms.

10 ms n'est pas catastrophique mais il commence à le devenir

je n'ai pas trouvé la valeur à mettre pour que ce soit dynamique. Dois je modifier directement la valeur avec alter system ou est il préférable de modifier le spfile.ora ?

Je n'ai pas dit qu'il faut la mettre à 128. J'ai dit qu'au dela d'une version 10.2.0.1 il faut laisser ce paramètre "unset". Pas 0 mais "unset" sans valeur. Quelque chose comme ceci

Code :

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

1
2
 
ALTER SYSTEM RESET DB_FILE_MULTIBLOCK_READ_COUNT SCOPE=SPFILE SID='*';

Il me semble qu'il faut un stop/restart de la base de données pour que cela prenne effet.

Mais avant de commencer à changer ce paramètre, regardez les requêtes qui sont clasées dans le top des parties "SQL ordered by" et essayer d'avoir leur plan d'execution afin d'améliorer leur performance

[Mohamed.Houri]

Il y a deux approches différentes pour traiter les problèmes de performance d’une base Oracle. Celui que vous utilisez vous permet d’identifier les problèmes mais il ne vous permet pas de cibler facilement les traitements qui les provoquent. Néanmoins, cella ne signifie pas que cet approche est moins utile.

L’autre approche prend comme point de départ le constat des utilisateurs via une approche métier. L’utilisateur X signale que pendant l’exécution de la tâche Y les temps de réponse est dégradé. Dans ce cas activez la trace SQL et analysez ce qui se passe éventuellement en utilisant en outil de profilage (tkprof, tvdxstat, oracle profiler, etc.). De cette façon vous pouvez cibler très précisément les endroits où vous devez intervenir et évaluer les divers méthodes d’intervention envisageables.

Il y a deux approches de traitement des problèmes de performance:

1) traitement d'un problème de performance général (Instance wide) que l'on n'arrive pas à localiser très précisement

2) traitement d'un problème de performance localisé : batch job prenant plus de temps que d'habitue, requête très lente, etc...

Je ne vais pas utiliser un rapport AWR pour traiter le point 2).

[mnitu]

Juste pour polémiquer un peu : il n’y a que des problèmes des performances qui se manifestent à l’utilisation du système. Un utilisateur enclenchant une action et trouvant que le temps de réponse du système est peu satisfaisant, signale le problème de performance.
Le

problème de performance général (Instance wide)

c’est juste quand tous les utilisateurs gueulent en même temps.

Par la suite c’est la décision de la personne qui analyse ce problème quelle méthode et quel outil employer pour comprendre les causes du problème. Cette décision dépende de divers facteurs parmi lesquels la facilité d’activer une trace SQL par exemple.

[pachot]

Bonjour,

Je ne vais rien dire de plus que Mohamed, mais c'est juste pour insister et faire quelques calculs en plus afin de montrer l'importance de la méthode d'approche par AWR.

56.6 + 22.1 + 11.5 = 90% du temps où les utilisateurs attendent la base de donnée est une attente sur des lectures disque (attente de l'i/o ou attente de la lecture par un autre). C'est là qu'il faut commencer.

Sur cette heure, 60.25 * 60 * 3 798.94 = 13 733 168 blocks ont été lus sur disque. Il y a 340 692 i/o single blocks, et 944 361 i/o multiblocks.

(60.25*60*3798.94-340692)/944361=15 blocks par i/o multiblock. On retrouve le db_file_multiblock_read_count.

Donc déjà en l'augmentant (ou en laissant faire Oracle), on devrait pouvoir diviser par 8 le nombre d'i/o (je suppose des blocks de 8k ici donc une lecture de 1Mo lit 128 blocks donc 8 fois plus que 15 blocks)
Ce qui va faire pas loin de 8 fois plus rapide sur 56.6 % du temps de réponse

-> 'quick fix' on peut déjà espérer un gain de 4x en changeant db_file_multiblock_read_count

Mais toutes ces lectures multiblock, c'est peut-être des full scan inutiles. Il suffit de retrouver les requêtes concernées.
Dans SQL ordered by reads, on cherche celles qui sont responsables de 13 millions de physical reads.

Et on peut aussi regarder Segments ordered by physical reads pour retrouver les tables ou index responsables de ces 13 millions de physical reads.

-> Ensuite, plan d'exécution de ces requêtes et voir comment supprimer ces full scans. Là c'est un gain énorme en temps de réponse (éviter ces 90% de lectures disque), et en scalabilité (on fait 3798 i/o par seconde: c'est déjà pas mal et si les données doublent, ce sera difficile de suivre pour le système). Sans parler de tous les autres systèmes qui sont sur la même baie de stockage et qui sont pénalisés par tous ces i/o inutiles.

Cordialement,
Franck.

[bigbull]

Tout d'abord un grand merci à tous pour vos réponses.

Si j'ai tout bien compris :

Pour des problèmes de performance générales je m’appuie sur un rapport awr d'une heure maximum (au fait comment je peux réduire l'intervalle ?) et je surveille les points suivants :

- le db file scattered read dont la valeur ne doit pas atteindre des sommets et dont la lecture moyenne par block ne doit pas dépasser 5ms sous peine de devoir investiguer

- le db file sequential read avec les mêmes recommandation

- je verifie ensuite dans le top five les requêtes qui ont générés ces pics.

- je laisse oracle gérer le standard block size, qui devrait se mettre automatiquement aux alentours de 128k

Je vais donc prendre contacte avec notre éditeurs et lui transmettre les requêtes qui posent problème et leur faire valider le changement que je vais effectuer.

Je vais aussi jeter un oeil sur le tuto "initialisation à l'optimisation de requêtes SQL sous ORACLE" et me lancer dans les plans d’exécutions. Et trouver un utilitaire sympa pour tracer les transactions.

Vraiment merci pour tout le temps que vous y avez passez.

[13thFloor]

Bonjour,

Pour des problèmes de performance générales je m’appuie sur un rapport awr d'une heure maximum (au fait comment je peux réduire l'intervalle ?) et je surveille les points suivants :

Soit tu les génères :
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT
soit tu modifies la fréquence :
exec dbms_workload_repository.modify_snapshot_settings(interval => 15);

[bigbull]

Merci beaucoup.

Cordialement,