Tuning d'une requête SQL en 11g

Hello ,
Après 3 mois d’optimisation, je n’arrive pas à trouver la bonne démarche pour analyser et tuner une requête compliquée (10 tables , sous-select ,merge , insert …).
Aujourd’hui j’arrive à détecter plus facilement la requête qui pose des problèmes de perfs en 11g dans un traitement compliqué de plusieurs requêtes.
Mais il va falloir détecter le problème et tuner la requête et c’est là où je m’arrache les cheveux.:mur:
Ma démarche une fois la requête trouvée est :
1/Vérifier le plan d’exécution : vérifier l’utilisation ou pas des indexes ou existence de produits cartésiens et c’est tout !
2/ Noter la valeur du COST final !
3/ Vérifier les types de jointure : je ne peux que constater !!! merge hash …
4/ Je pars dans un scénario de recherche dans tous les sens qui me fais perdre énormément de temps …SANS AUCUNE GARANTIE DE TROUVER LE PROBLEME :
- Regarder le rapport AWR si je peux.
- Essayer de rajouter des hints PARALLEL USE_HASH ,…
5/ Parfois j’arrête la recherche dans cette piste et je pars dans une piste :
- Vérifier les stats si à jour ou pas …
- rebuilder les indexes mais quand faut-il le faire ? dans quel cas ?
Je me dis parfois : faut-il vérifier aussi les clauses de stockage de chaque table utilisée dans la requête.
Faut-il vérifier les paramètres de la base Oracle …je suis un simple analyste !!!
Faut-il utiliser les Events ? 10046 … ? Comment détecter les attentes CPU ? cela veut dire quoi ?
J’ai entendu parler d’Advisors Oracle permettant de nous aider à solutionner le problème ?
…et la journée passe …voilà un vrai voyage dans l’espace des hypothèses sans démarche précise …
Enfin ma question, quelle est la bonne démarche à suivre pour un débutant ? et quels outils SIMPLES et FIABLES pourront m’aider à sortir de ce tunnel ! Toad ? Sql Developer ? … ?
Help please ! :cry:
Z.

Vous ne pouvez optimiser ce que vous ne comprenez pas ! L’optimisation signifie éliminer le travail inutile qui est fait actuellement !

Par conséquence la bonne démarche consiste à identifier le travail inutile, à comprendre pourquoi ce travail est fait et à trouver une alternative pour accomplir la tâche sans gaspillage.

Autre remarque importante : il faut optimiser le traitement et ne pas se focaliser sur une seule requête ! L’utilisateur appuie sur un bouton il ne lance pas une requête ! C’est l’algorithme codé sur l’action d’appuyer sur le bouton qui est lent et les causes peuvent être très diverses !

La démarche que je vous propose, mais gardez à l’esprit qu’il y a plusieurs façons d’écorcher un chat :

• Etape 1 : Identifier le traitement qui pose des problèmes de temps d’exécution. Ca c’est l’utilisateur qui va vous aider. Demandez lui d’être précis : quelle action s’effectue quand les choses commence à mal tourner.
• Etape2 : Collectez les informations nécessaires à l’analyse du problème. Vous devez comprendre où le temps passe !
  
  • Etape 2.1 : Notez la version d’Oracle. Chercher les valeurs des divers paramètres de l’optimiseur. Vérifiez la présence des statistiques. Vérifiez que votre environnement de diagnostique est similaire à celui qui pose des problèmes.
  • Etape 2.2 : Activez la trace SQL étendue. C’est ma préféré mais des autres méthodes peuvent être employées: statspack, awr, etc.
  • Etape 2.3 : Faite un profil du traitement qui va vous indiquer : quelles parties prennent le plus des temps. Et encore plus important qu’elle soit la proportion du temps consumé dans le traitement final. Exemple : vous avez deux requête qui sort en top de consumation du temps : mais une prends 85 % du temps et l’autre 10%. Sur laquelle faut-il se focaliser ?
• Etape 3 : Analysez le profil du traitement essayez de comprendre où se situe le problème : les requêtes sont le problème ? Il y a trop de parsing ? Le réseau rame ? On attente quelqu’un d’autre ? Les événements de wait sont la pour vous indiquer qu’est ce que on est en train d’attendre! Faite des hypothèses et essayez de trouver une explication à la réalité.
  
  • Etape 3.1 : Si vous avez identifié que les requêtes sont la cause des problèmes, utilisez dbms_xplan pour analyser les plans d’exécution. Ce qui vous intéresse sont les étapes où des écarts importants existent entre les estimations de l’optimiseur et la réalité de l’exécution. Deuxième point d ‘intérêt : les étapes du plan ou beaucoup des enregistrements lus précédemment sont écartés sans justification (rappelez-vous : éliminer le travail inutile).
• Etape 4 : Vous avez trouvé l’explication du mauvais temps de réponse. Maintenant imaginez et cherchez des solutions. Vous cherchez toujours à éliminer la partie du travail inutile qui est fait. Vous cherchez d’accomplir la même tâche mais avec moins de ressources !
• Etape 5 : Testez et faire validez vos hypothèses. Arrêtez-vous de que vous avez trouvé une solution qui rends le temps de réponse acceptable, sinon l’optimisation par définition c’est une tâche à faire jusqu’à la retraite.

Merci pour la démarche à suivre :ccool:

SQL Trace étendue ? je connais que SQL Trace.

Sinon voici le paramétrage de l'optimizer :

Dois-je faire confiance à ce paramétrage par défaut notamment :

optimizer_index_cost_adj 100

optimizer_capture_sql_plan_baselines FALSE
optimizer_dynamic_sampling 2
optimizer_features_enable 11.2.0.3
optimizer_index_caching 0
optimizer_index_cost_adj 100
optimizer_mode ALL_ROWS
optimizer_secure_view_merging TRUE
optimizer_use_invisible_indexes FALSE
optimizer_use_pending_statistics FALSE
optimizer_use_sql_plan_baselines FALSE

Si la trace est activé via

Code:

---

alter session set sql_trace = true

---

ou

Code:

---

dbms_session.set_sql_trace

---

vous activez la trace classique.

La trace étendue implique l’affichage dans le fichier trace en plus des informations affichées par la trace classique des événements d’attente et des valeurs des variables de liaison. Cette trace s’active via l’événement 10046

Code:

---

alter session set events '10046 trace name context forever, level 12'

---

où level 1 signifie la trace classique, 8 plus Waits et 12 plus valeurs des variables de liaison. (Actuellement plus des options sont possible Event 10046 – Full List of Levels)
ou

Code:

---

dbms_monitor.session_trace_enable

---

Lisez cette excelent article écrit par Cary Millsap: Mastering Performance with Extended SQL Trace.

Le paramétrage par défaut de l'optimiseur est bon pour la plus part des cases. A part de vous vous trouver dans une situation exceptionnelle il n’y pas raison d’intervenir. Mais, reconnaître que votre situation est différente de celle normale implique une très bonne maitrise du sujet.

Bonjour

Connaissez-vous une documentation SIMPLE et PRATIQUE permettant d'analyser rapidement :

- Le contenu d'un rapport TKPROF

- Le contenu d'un rapport AWR.

Merci pour votre aide :ccool:

Citation:

---

Envoyé par zidane2012

Bonjour

Connaissez-vous une documentation SIMPLE et PRATIQUE permettant d'analyser rapidement :

- Le contenu d'un rapport TKPROF

---

Interprétation d’un fichier TKPROF

Merci Mohamed pour cet article très intéressant.
Avez-vous une analyse du même genre sur le rapport AWR ?
Merci et bravo !

Bonjour Mohamed ,

J’ai essayé d’appliquer vos recommandations sur ma requête et la trace tkprof(fichier ci-joint).

Voici mon analyse . Votre avis ? Merci.
NB : Base Oracle 11g avec statistiques NON calculées (forcées).

1/Execute 1 783.31 875.67 27955 12868858 1214035 593192
Durée = 15 min pour traiter 593192 lignes !
Analyse : Traitement à optimiser vu le nombre de lignes et le temps
2/ 429120120 429120120 429120120 INDEX RANGE SCAN IDX_CODE_ACTIVITY_NO PARTITION: 5 5 (cr=2221611 pr=5 pw=0 time=237637665 us cost=4 size=0 card=164)(object id 4593)
Analyse : 429120120 lignes traitées via l’index en 237637665 us (4 min) => index à revoir !
3/db file sequential read 11288 0.05 0.98
Analyse : Lecture indexée favorisée = 11288 sec ( 3h ???).
4/ Misses in library cache during parse: 1
Analyse : 1 hard parse effectué.
5/ Lecture physique(disk =27955) < Lecture logique (query=12868858)
Analyse : FULL SCAN NON favorisé ( dû à l'utilisation de l'index).
6/ Temps CPU(783.32 ) = environ temps consommé(875.68)
Analyse : Peu d’attentes : Tant mieux.

Conclusion : Index IDX_CODE_ACTIVITE_NO PARTITION à vérifier ? rebuilder ? full scan ?

Votre requête est gouvernée par les Hints que vous avez ajoutés. Le plan d’exécution imposé par cette méthode est un désastre : index IDX_CODE_activity_NO accédé via un range scan d’une manière très inefficace : 429 millions de lignes lu via l’index pour garder 31 millions des lignes dans la table qui sont par la suite réduite à 350 mille après la première jointure. Méfiez-vous des range scan ! En fait vous lisez la même chose dans la boucle gouvernée par la jointure nested loop juste pour jeter la plus part de ce que vous avez lu à la poubelle. Ca c’est votre travail inutile.

Virez les hint c’est clair que vous ne pouvez pas indiquer à l’optimiseur un bon plan d’exécution. Calculez les statistiques et laissez Oracle faire il s’en sortira mieux.

PS. Ouvrez un autre post pour vous même!

Dans un update (ou delete) il y a la partie qui concerne la localisation des données à mettre à jour et la mise à jour elle-même. Dans ce contexte, c’est clair que si vous arrivez à éviter le full table scan sur la table PGP T, l’update de cette table changerait de méthode. S’il utilise un index pour trouver les données à mettre à jour alors il retarderait l’update de tous les indexes (et non pas uniquement de l’index ayant servi à localiser les données à mettre à jour) jusqu’à la fin de l’update. Et comme il fera ces updates (delete/insert) des indexes à la fin de l’update, il le fera en bulk générant moins de redo et moins d’undo qu’il n’est le cas aujourd’hui pour vous. D’ailleurs le fait d’observer l’event ''log file switch'' est un signe d’une activité vigoureuse des redos qui remplissent très rapidement les redo log files conduisant le DBWn à initier un ''Checkpoint '' avant d’opérer un switch d’un redo log file rempli à un redo log file vide. Si votre update essaie de réutiliser le redo log file avant que le DBWn n’ait fini son ''Checkpoint'' vous observerez alors ce genre d’events ''log file switch … incomplete’’.

L’update sur la table partitionnée PGP utilise des where clauses sur les colonnes suivantes
1. no_env
2. id_pgp
3. no_reference
4. cd_activity

Quel sont tous les indexes sur cette table ?