Index utilisé sous PL/SQL Developper mais pas par l'appli

[houpli]

Bonjour,

Je suis confronté à un drôle de problème.

Un traitement de purge est lancé par une application. Elle consiste à supprimer les enregistrements de plus d'un an, et cela par paquet de 5000.
Le problème est que cette requête est super longue (cela bloque la purge d'autres tables), et comme c'est par paquet vous imaginez bien que exponentiel..

Voici la requête :

Code :

DELETE FROM nmstrackinglogrcp  WHERE tslog < to_timestamp('20110119025529', 'YYYYMMDDHH24MISS')  AND ROWNUM <= 5000

En regardant le plan d'exécution, on s'aperçoit que l'optimiseur n'utilise pas l'index NMSTRACKINGLOGRCP_LOGDATE sur le champ tsLog. Il fait un TABLE ACCESS (STORAGE FULL).

Par contre, quand je lance la même requête sous PL/SQL Developper, il utilise bien l'index (INDEX FULL SCAN).

J'ai déjà essayé de recréer l'index, cela a fonctionné... 1 fois. Par la suite, l'index fut une nouvel fois laissé de côté.

Je n'ai pas la possibilité de modifier la requête lancé par l'application (boîte noire).

Avez-vous une idée ?

Merci d'avance

[skuatamad]

Quelle version d'oracle ?
C'est une mauvaise pratique (certes très répendue) de supprimer par paquet...
Il est possible que '20110119025529' et/ou 5000 (surtout 5000) soit une bind variable dans l'appli, oracle ne peut donc pas estimer le pourcentage de ligne a supprimer et choisi un FULL SCAN (si version inférieur à 11GR2).
Que donne une trace étendue de l'appli ?

S'il est impossible de modifier l'appli il existe des moyens de stocker un plan d'exécution (en fonction de la version) comme :
Using Plan Stability

Using Stored Outlines

Il faut récupérer le code SQL exacte de l'appli (voir v$sql ou une trace étendue).
Il faut pouvoir forcer l'utilisation de l'index (optimizer mode = FIRST_ROWS pour ce genre de requête devrait le faire)

[Mohamed.Houri]

Code :

1
2
 
Il fait un TABLE ACCESS (STORAGE FULL).

Première question : vous êtes sous exadata; n'est ce pas?

Deuxième question : pourriez vous poster l'explain plan avec sa partie predicate

[pacmann]

Salut !

Skuatamad, je mettrais un petit bémol : en 9i+, tu fais du bind variable peeking.
Donc, dans la mesure où ces valeurs semblent des "constantes" pour une exécution donnée du traitement de purge, l'optimiseur pourrait bien prendre en compte les bonnes valeurs...

Bref, j'irais bien comparer les cardinalités estimées par sql développeur (qui choisit l'index) et celles estimées à l'exécution par la boîte noire.

[houpli]

Envoyé par skuatamad

Quelle version d'oracle ?
C'est une mauvaise pratique (certes très répendue) de supprimer par paquet...
Il est possible que '20110119025529' et/ou 5000 (surtout 5000) soit une bind variable dans l'appli, oracle ne peut donc pas estimer le pourcentage de ligne a supprimer et choisi un FULL SCAN (si version inférieur à 11GR2).
Que donne une trace étendue de l'appli ?

Je ne pense pas que les 20110119025529 et 5000 soit des bind car dans OEM quand c'est la cas, on voit :1 :2 etc. Hors ici OEM indique bien les valeurs. Je vais tout de même me renseigner. Sinon oui c'est une 11GR2.

S'il est impossible de modifier l'appli il existe des moyens de stocker un plan d'exécution (en fonction de la version) comme :
Using Plan Stability

Le problème est que l'appli est un prologiciel et cette modification implique une montée de build, ce qui mettras un certain temps.. et le problème devient urgent.

Je vais remonter cette info à l'éditeur.

Merci !

[houpli]

Envoyé par Mohamed.Houri

Première question : vous êtes sous exadata; n'est ce pas?

Oui

Deuxième question : pourriez vous poster l'explain plan avec sa partie predicate

Est-ce cela suffit ?
(j'ai du grisé le nom du schéma)

Merci

[Mohamed.Houri]

A partir du moment où vous avez ceci

Code :

1
2
TABLE ACCES (STORAGE FULL)

C'est que vous êtes sur exadata. Je voulais juste que vous vous en rendiez compte. Car en effet, le fonctionnement ou le traitement des requêtes diffère de celui des bases de données traditionnelles. Et puisque vous êtes sur exadata, la version d'oracle qui y est installée et au minimum 11gR2. Avec ce qui précède, il faut maintenant penser exadata et non comme si vous étiez sur une base de données ordinaire si je puis dire. Et lorsqu’on pense exadata on pense systématiquement à bénéficier de smart scan.

La seule information qui est exploitable ici est STORAGE FULL. Puisque cette option y figure dans le plan d’exécution ceci veut dire que l’accès et le filtrage des données peuvent se faire au niveau du disque. Mais ceci ne veut pas dire que vous allez pouvoir bénéficier des smart scans. Si par contre dans la partie predicate du plan d’exécution vous observez l’option storage() ceci veut dire que votre plan d’exécution va essayer d’utiliser les smart scan et appliquer le filtre au niveau des ‘cell’ avant de renvoyer le résultat à la base de données d’une manière extrêmement rapide. Mais la présence de la clause storage() dans la partie predicate n’est pas une garantie que les smart scan et le ‘’offloading’’ du filtre au niveau du disque (storage) va avoir lieu.

Une des solutions de votre problème serait d’utiliser le snapper(http://tech.e2sn.com/oracle-scripts-...ession-snapper) de Tanel Poder pendant l’exécution de votre delete et voir pourquoi les smarts scans ne sont pas (ou sont) utilisées.

Enfin, c’est quand même bizarre que vous travaillez sur exadata et que vous ne vous êtes pas posés ce genre de question.

Bonne chance et profitez bien de la possibilité que vous avez de travailler sur exadata

[houpli]

Envoyé par Mohamed.Houri

A partir du moment où vous avez ceci

Code :

C'est que vous êtes sur exadata. Je voulais juste que vous vous en rendiez compte. Car en effet, le fonctionnement ou le traitement des requêtes diffère de celui des bases de données traditionnelles. Et puisque vous êtes sur exadata, la version d'oracle qui y est installée et au minimum 11gR2. Avec ce qui précède, il faut maintenant penser exadata et non comme si vous étiez sur une base de données ordinaire si je puis dire. Et lorsqu’on pense exadata on pense systématiquement à bénéficier de smart scan.

La seule information qui est exploitable ici est STORAGE FULL. Puisque cette option y figure dans le plan d’exécution ceci veut dire que l’accès et le filtrage des données peuvent se faire au niveau du disque. Mais ceci ne veut pas dire que vous allez pouvoir bénéficier des smart scans. Si par contre dans la partie predicate du plan d’exécution vous observez l’option storage() ceci veut dire que votre plan d’exécution va essayer d’utiliser les smart scan et appliquer le filtre au niveau des ‘cell’ avant de renvoyer le résultat à la base de données d’une manière extrêmement rapide. Mais la présence de la clause storage() dans la partie predicate n’est pas une garantie que les smart scan et le ‘’offloading’’ du filtre au niveau du disque (storage) va avoir lieu.

Une des solutions de votre problème serait d’utiliser le snapper(http://tech.e2sn.com/oracle-scripts-...ession-snapper) de Tanel Poder pendant l’exécution de votre delete et voir pourquoi les smarts scans ne sont pas (ou sont) utilisées.

Enfin, c’est quand même bizarre que vous travaillez sur exadata et que vous ne vous êtes pas posés ce genre de question.

Bonne chance et profitez bien de la possibilité que vous avez de travailler sur exadata

Merci pour cette réponse détaillée !
Je ne suis pas DBA, j'administre l'applicatif d'une plateforme d'emailing de masse.

Je ferai part de votre réponse au DBA.

Ce que j'ai toujours du mal à comprendre c'est pourquoi l'optimiseur n'utilise pas le même plan selon l'endroit où j'exécute la requête...

[skuatamad]

Envoyé par pacmann

Skuatamad, je mettrais un petit bémol : en 9i+, tu fais du bind variable peeking.
Donc, dans la mesure où ces valeurs semblent des "constantes" pour une exécution donnée du traitement de purge, l'optimiseur pourrait bien prendre en compte les bonnes valeurs...

Ouais t'as raison, surtout pour le 5000, mais bon finalement comme c'est du exadata dur de dire quoi que ce soit.

Envoyé par houpli

Le problème est que l'appli est un prologiciel et cette modification implique une montée de build, ce qui mettras un certain temps.. et le problème devient urgent.

Inutile de remonter de l'info, le but des outlines est justement de pouvoir forcer un plan quand on est pas maître du code.

Mais bon comme c'est du exadata c'est probablement très différent, je laisse Mohamed essayer de t'aider.

Franchement je me gourre peut être mais le code ne semble vraiment pas écrit pour votre environnement parce que déjà que sur Oracle supprimmer 5000 lignes par 5000 lignes c'est vraiment bof mais sur exadata...... je ne connais pas mais le but c'est quand même l'EXA !

Bon courrage !

[houpli]

Envoyé par skuatamad

Inutile de remonter de l'info, le but des outlines est justement de pouvoir forcer un plan quand on est pas maître du code.

Je regarderai mais je pense que c'est au DBA d'agir

Franchement je me gourre peut être mais le code ne semble vraiment pas écrit pour votre environnement parce que déjà que sur Oracle supprimmer 5000 lignes par 5000 lignes c'est vraiment bof mais sur exadata...... je ne connais pas mais le but c'est quand même l'EXA !

L'appli n'a pas été écrite pour l'exadata, mais pour Oracle en général.
La table en question est relativement volumineuse (300 millions de lignes) et bouge constamment (c'est la table qui contient des logs d'évènements liés aux emails envoyés aux clients - clics, ouvertures, etc. - les logs remontent en base pratiquement en temps réel). La suppression se fait par paquet peut-être pour éviter les locks ? Le traitement de purge peut devoir supprimer quelques millions de ligne par jours.

Bon courrage !

Merci