Discussion :

[grouhan]

Bonjour,

Cette requête est exécutée dans un traitement quotidien et elle s'exécute en 5 mn environ.
Aléatoirement, lors de son exécution, elle tourne pendant des heures (8h), et lorsque l'on kill le programme et qu'on le relance , la requête avec les mêmes conditions que lorsqu'elle a tournée pendant 8h ne prend plus que 5 mn.

Oracle dans le SQL HC remonte que la requête est exécuté 64 fois alors qu'elle n'est exécutée qu'une seule fois
Dans le SQL HC, le avg row proc est égal à 0, bug Oracle ?
Je vois un nombre important de buffer gets mais je n'explique pas pourquoi cette requête semble figée pendant des heures sans ramener d'enregistrements alors quelle consomme des ressources ?
Et surtout pourquoi lorsqu'elle est relancée elle ne prend plus que 5 mn ?

Avez-vous déjà eu ce comportement ? AVez-vous une idée de ce que je peux vérifier pour analyser ce pb ?

Merci par avance

[dell68]

D'après les informations fournies , il n'y a pas d'instabilité de plan d'exécution sur la requête
Et ça serait mieux que tu nous le plan d'exécution avec toutes les statistiques ???

[grouhan]

Bonjour,

je rajoute les informations complètes que j'ai.

Les stats non à jour remontés par oracle sur la table ZSOLDOS suffisent-elles à expliquer ce comportement ? Je ne suis pas convaincu car la deuxième fois après kill du pgm et relance la requête s'exécute correctement et les stats ne sont à priori toujours pas à jour à ce moment là

[dell68]

C'est un sujet concret de Tuning de requêtes, tu devrais le deplacer dans la section Administration pour que tu ais les chances d'être résolues

[Mohamed.Houri]

Bonjour,

Pourriez-vous dans un premier temps poster ceci:

Code :

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

select * from table(dbms_xplan.display_awr('192xbhg3z12f5'));

Le but étant de savoir si l'exécution rapide utilise un plan différent de celui qui est utilisé par la version la plus lente.

Je ne suis pas très habitué à l’utilisation du SQL HC, je me demande donc si vous savez interpréter la colonne FETCH qui est tout le temps = 0.

Si cette colonne représente ce qu’on appelle le « end_of_fetch » alors le SQL HC montre que votre requête a traversé plusieurs AWR snapshots sans finir. Ce qui tend à prouver les 8 h00 d’exécution. Aussi, il me semble que vous avez un snapshot toutes les 15 min ? est-ce correct ?

Sinon, c’est normal que votre requête dure plus de 8h00 quand elle doit exécuter l’opération n°8 4295 millions de fois et son opération fille n°7 1097K fois. De plus au vu du nombre de lignes perdues entre l’index IDX_ZSOLDOSC et sa table ZSOLDOS0 il est fort possible qu’un autre index serait plus performant et plus précis. Je pourrai voir plus clair si vous postez la partie prédicat du plan 2380551923

Bien à vous
Mohamed Houri

[grouhan]

Bonjour,

merci pour ce retour.

oui, il y a bien un snapshot toutes les 15 mn.

Je vais demander à avoir le retour de la requête pour avoir le plan d'exécution. Comme il s'agit de la prod, et que je n'ai pas la main dessus, il se peut qu'il se passe un peu de temps avant que j'obtienne un retour.

Votre remarque concernant le nombre de lignes perdues entre la table et l'index si je comprends bien, il a parcouru l'index 1 097K pour 4 295M de lignes trouvés et pour ces 4 295M index, il a parcouru 4 295M fois la table pour ne trouver que 427K ?

Il y a une grosse différence entre ce que Oracle estime et ce qui est réellement retourné, non ?

[dell68]

C'est pour ça que seul plan d'execution est important
pour savoir les estimations des cardinalite ...

[Mohamed.Houri]

Bonjour,
oui, il y a bien un snapshot toutes les 15 mn.

Oui c'est ce que j'avais calculé en lisant le SQL HC

Votre remarque concernant le nombre de lignes perdues entre la table et l'index si je comprends bien, il a parcouru l'index 1 097K pour 4 295M de lignes trouvés et pour ces 4 295M index, il a parcouru 4 295M fois la table pour ne trouver que 427K ?

Il y a une grosse différence entre ce que Oracle estime et ce qui est réellement retourné, non ?

Le NLJ BATCHING (double NESTED LOOPS) prête à confusion dans la lecture du plan d 'execution mais, oui, globalement votre explication est correcte. Entre l'index et sa table on perd trop de lignes. Il est vrai aussi que le nombre d'exécution exagère un peu cette grande perte de données en l'index et sa table.

Il y a une grosse différence entre ce que Oracle estime et ce qui est réellement retourné, non ?

Vous pouvez faire vos comparaisons en comparant

Code :

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

Est.Rows x Executions

avec

Code :

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

Rows

Bien à vous
Mohamed Houri

[grouhan]

Bonjour,

tu peux STP détailler le mode de fonctionnement du NLJ BATCHING , c'est lié à l'optimisation des lectures physiques ? Ce que m'indique le plan d'exécution comme chiffre n'est pas réellement ce qui ce produit ?

@dell68, le plan d'exécution dans le document sqlhc_sql_monitor.html n'est pas suffisant selon vous ?

De ma compréhension du plan d'exécution, il lit la table ZMOUANA en passant par l'index IDX_ZMOUANAP et ensuite, il effectue une jointure avec l'index IDX_ZSOLDOSC et pour les enregistrement correspondant (4 295 M), il va requêter la table ZSOLDOS0 4 295 M et probablement effectuer un filter predicate pour au final avoir 427K rows.

Ici à priori, Oracle choisi un index non optimal car les stats ne sont pas à jour (gap important entre ce qu'il estime et ce qu'il récupère réellement)