[Oracle 9iR2][PL/SQL] Session bizarre

**mainecoon** · 12/02/2007, 17h07

J'exécute une procédure stockée, et ma PS tourne en rond... Je vois par contre deux autres sessions sous mon identifiant :

Avec comme current statement :

/* Formatted on 2007/02/12 17:05 (Formatter Plus v4.8.5) */
DECLARE
program DBMS_DEBUG.program_info;
BEGIN
program.namespace := :namespace;
program.NAME := :NAME;
program.owner := :owner;
program.dblink := :dblink;
program.libunittype := :libunittype;
program.entrypointname := :entrypointname;
:retval :=
DBMS_DEBUG.get_line_map (program,
:maxline,
:numentrypts,
:linemap
);
END;

Et

/* Formatted on 2007/02/12 17:05 (Formatter Plus v4.8.5) */
DECLARE
program DBMS_DEBUG.program_info;
BEGIN
program.namespace := :namespace;
program.NAME := :NAME;
program.owner := :owner;
program.dblink := :dblink;
program.libunittype := :libunittype;
program.entrypointname := :entrypointname;
:retval :=
DBMS_DEBUG.get_line_map (program,
:maxline,
:numentrypts,
:linemap
);
END;

C'est quoi ça ?

C'est fou, j'arrive à lancer ma requête SQL à la main, mais quand je lance la PS qui contient la requête, ça semble ne rien faire...

**mainecoon** · 12/02/2007, 17h23

J'ai remarqué qu'en modifiant la procédure stockée, en remplaçant les binds variables, par des valeurs arbitraires en dure, ça fonctionne !!!

Je ne comprends pas là...

J'ai d'autres PS qui ressemble à celle-ci et ça marche...

**orafrance** · 12/02/2007, 18h52

tu serais pas sous PL/SQL par hasard ? T'es session reste active ? C'est quoi le wait dans v$session_wait ?

**Yorglaa** · 12/02/2007, 19h20

tu ne serais pas par hasard sous TOAD et en train d'effectuer un debug "en live" de ta procédure ?

dans ce cas Toad communique avec des sessions séparées pour pouvoir te fournir les valeurs des variables et autres infos pendant que ta procédure est suspendue (mode pas-à-pas)...

j'imagine que les autres logiciels de développement qui proposent ce mode de débug fontionnent de manière similaire.

**mainecoon** · 13/02/2007, 07h42

Bonjour,

je travaille avec Toad (version 8 je crois) et des procédures stockées PL/SQL dans Oracle 9iR2.
(En prod, j'attaque avec une application .NET (FWK 1.1) avec la classe OracleClient.)

Le problème c'est que je n'ai pas lancé de Debug...

Par contre, avant de quitter le boulot, j'ai remarqué quelque chose.

Si je mets des valeurs en dur dans la requête SQL INSERT de la procédure (plus aucun intérêt pour la procédure, mais c'est juste pour le test !), ça marche, et en plus j'ai remarqué qu'il ne construit pas le même plan d'exécution !
Pour info, ma requête récupère les données d'une vue basée sur deux tables en UNION ALL.

Valeurs en dure : il fait un FULL TABLE SCAN des deux tables, et là dans Toad, je vois bien la barre de progression évoluer dans l'onglet LONG OPERATION.

Par contre en laissant des variables ou binds variables en passant par un EXECUTE IMMEDIATE (je pensais que ça venait de là, mais non), il me change le plan d'exécution et utilise un INDEX (sur la date), et là plus de barre de progression, et ça ne bouge pas (ou alors faut attendre longtemps ?? Mais ça dépasse le temps normal du test avec les valeurs en dur).

J'ai essayé de forcer le FULL TABLE SCAN en ajoutant le HINT FULL TABLE dans ma requête INSERT en pointant sur la vue. Et là, j'ai eu les barres de progression ! Mais faut que je vois ce matin, car je n'ai pas pu rester pour voir la fin.
Ah oui, ma requête est un INSERT qui fait un agrégat sur toute une période par exemple sur 6 MOIS, donc faut bien qu'il fasse un FULL TABLE, non ? Car je ne recherche pas des valeurs précises de dates.

Pour le v$session_wait, je regarderai ce matin si ça le fait encore.
Sinon à quoi correspond ce paramètre ?

Merci pour votre aide.

**mainecoon** · 13/02/2007, 08h56

Voici précisément le hint utilisé : FULL(ma vue)

Ce qui donne :

SELECT /*+ FULL(PSCM.VPAMPVENTES) */ a.champ1, a.champ2 ...

Ainsi, il évite de se taper toutes les lignes de l'index sur_date et parcourt directement les tables, vu que 50% des enregistrements correspondent aux critères (sur une période de 12 mois glissants - sur 24 mois de la vue).
Par contre, j'avais un gros doute sur son emplacement vu (sans jeux de mots

) qu'il s'agit d'une vue basée sur deux tables.

Deux choix :
1°/ Je le place dans ma requête qui appelle la vue (ce que j'ai fais).
2°/ Je place deux fois le hint pour chaque table constituant la vue directement dans le script de création de la vue. Mais problème, car parfois, je peux utiliser cette vue pour chercher une date (enfin, je pense, mais dans le doute...), alors si je fais un FULL pour chercher une ligne...

Je ne pensais pas que le 1°/ marchait, mais on dirait que oui... Je viens de réessayer, et ça semble bien marcher... Après pourquoi Oracle passe par un FULL TABLE quand je lançe directement la requête et par les index sur date quand j'utilise des paramètres ou bind variables, je ne sais pas !

**mainecoon** · 13/02/2007, 10h04

Argh ! Non, ça ne marche pas en fait !!! M...

J'ai bien toujours le même problème...

Quand j'utilise des paramètres/bind variables, j'ai comme plain d'exécution :

Nom : explainplanAx.JPG
Affichages : 99
Taille : 32,5 Ko

Nom : explainplanAx.JPG
Affichages : 99
Taille : 32,5 Ko

Et Quand je mets des valeurs en dur dans la requête INSERT de ma procédure PL/SQL, j'ai comme plan :

Nom : explainplanBx.JPG
Affichages : 82
Taille : 30,2 Ko

Le premier plan, ça ne marche pas (ou alors ça va mettre x heures ????), et le deuxième en 2 minutes chrono, c'est terminé !
(Pour info, chacun des deux tables de la vue font environ 50 millions lignes.)

J'ai essayé de mettre des hint FULL(matable) directement dans la création de la vue... mais on dirait qu'il s'en fiche !!!! Il passe par l'index sur_date (cf. plan A).

Je ne comprends plus là...

**remi4444** · 13/02/2007, 11h28

plusieurs éléments en vrac

- est tu sur qu'il n'y a pas une confusion entre le nom de ta variable et une colonne d'une de tes table ?

- Pour forcer le FULL, il faut que le HINT porte sur la table cible, ou sur l'alias (si alias il y a).

- Pour forcer le FULL de manière laide mais efficace tu peux "invalider" le passage par l'index en faisant porter la clause sur les date, sur un "calcul" de la colonne: " .... WHERE (COLONNE_DATE + 0) = :variable "

- S'il n'y a pas de problème de nom de variable, il se peut que la différence des plans provienne d'un mauvais histogramme sur la colonne date.

- Il se peut aussi qu'a un moment dans ta session le paramètre OPTIMIZER_INDEX_COST_ADJ ait été redefini à une valeur trop faible.

**mainecoon** · 13/02/2007, 11h58

Rebonjour,

Bon, il y a bien un problème sur le plan d'exécution...

J'ai laissé tourner quand ça bloquait... et en fait, ça tournait bien...

Mais ça met 23mn au lieu des 2mn quand il ne passe pas par les index sur_date !!!

Ce qui est surprenant, c'est qu'à l'instant t, je fais un test ça passe, et quand je relance, ça marche plus ! C'est assez irritant...

remi4444 : je vais vérifier/tester tes conseils.

**mainecoon** · 13/02/2007, 13h54

J'ai ajouté dans la requête INSERT dans la sous-requête SELECT, la condition TO_CHAR(b.DATE_VENTE)=''2006'' mais il continue de prendre les index sur_date !

Sinon, j'ai remarqué que parfois je peux lancer la procédure dans Toad et je vois le plan d'exécution avec une utilisation des index, mais ça tourne, je vois la barre de progression.... Et donc, une durée correcte...
Je comprends plus rien...

**mainecoon** · 13/02/2007, 14h00

J'ai peut-être trouvé autre chose !

Au lieu de faire DATEVENTE > :2

Je fais TO_CHAR(DATEVENTE) > TO_CHAR(:2),

(perf ???)

Et là, je vois bien l'utilisation en FULL TABLE dans le plan d'exécution...

Bon je lance la procédure depuis le début et je regarde !

**remi4444** · 13/02/2007, 14h28

/!\ Attention /!\

Pas TO_CHAR tu va dégrader le résultat!
si c'est un champ de type date (vérifie...) fait plutot "(DATEVENTE+0) > :2 " il faut faire un pseudo-"calcul" qui ne modifie rien.

Je commence à m'en vouloir de t'avoir donné cette méthode...

**mainecoon** · 13/02/2007, 15h06

Oui, et puis ce que j'ai marqué est faux !!!

Faire une comparaison entre deux dates et une comparaison entre deux dates converties en caractères, ça donne pas la même chose !!!

Je vais essayer le +0.

**mainecoon** · 13/02/2007, 15h15

remi4444 : ton astuce semble marcher ! Merci.

Le plan d'exécution est bien changé et il fait un TABLE ACCESS FULL sur les deux tables année et année-1.

Je suis en train de suivre la progression, j'ai l'impression que c'est plus lent que quand ça marche ! 6mn au lieu de 3mn
MAIS quand même bcp plus rapide que quand ça passait par l'index sur_date (25mn) !

C'est quand même bizarre tout ça... Pourquoi des fois ça marchait avec une durée normale en utilisant les index ?

Et une minute après en relançant, ça ne marche plus !

Par contre, je n'ai jamais réussi à utiliser les HINT FULL & NO_INDEX... Dommage ! C'est comme s'il s'en fichait royalement.

**mainecoon** · 13/02/2007, 15h16

Je marque RESOLU, mais toutes nouvelles suggestions sont la bienvenue !!!

Parce que c'est pas encore ça !

**Yorglaa** · 13/02/2007, 15h16

il n'y aurait pas eu par hasard un recalcul des statistiques ?

**mainecoon** · 13/02/2007, 16h45

Bonjour,

Non, je n'ai pas lancé d'ANALYZE sur les tables, ni reconstruction table ou index.

**remi4444** · 13/02/2007, 18h13

Envoyé par mainecoon

...
Non, je n'ai pas lancé d'ANALYZE sur les tables
...

C'est peut etre bien ça le problème justement...

**mainecoon** · 13/02/2007, 19h06

Heu... En fait, je voulais dire qu'il n'y a pas eu d'ANALYZE entre mes derniers essais, mais j'ai bien fait des ANALYZE après chargement de ces deux grosses tables.

Il est vrai qu'un ANALYZE sur table & sur index peut changer dramatiquement les performances ! 90mn -> 3mn sur une table de 150 millions de lignes !

J'ai lancé l'intégralité de mon process (30 procédures stockées) via une appli .NET, et tout marche ! 1h40... Avec le coup de date+0 pour éviter l'utilisation de l'index.

J'aimerais néanmoins descendre le temps total... Si je pouvais parvenir à retrouver ce temps quand je met des valeurs en dur (ou quand ça marche comme ça sans savoir) et que ça met 2mn au lieu de 6mn ou des 35mn avec index sur date !

Merci à chacun pour votre aide ! Le tuning, c'est pas évident du tout...

**remi4444** · 14/02/2007, 09h46

Normalement, on s'en sort avec les HINT mais c'est vrai que le gros défaut d'oracle est qu'il ne te prévient pas si tu a fait une erreur de syntaxe.

Il faut que tu donne ta requête si tu veux un avis plus précis...

[Oracle 9iR2][PL/SQL] Session bizarre

SQL Oracle

Discussions similaires

Partager

Partager