Discussion :

[lauraned]

Bonjour,

Je travaille sous une base Oracle 7 et je crée dans une fonction stockée en PL/SQL une requête en dynamique

Code :

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

1
2
3
4
5
6
7
 
V_Qry := 'Insert into toto (chp1, chp2, etc)' ;
V_Qry := V_Qry || ' select /*+ index(pos POS_STAT_PDSKM_IDX) */ ';
V_Qry := V_Qry || ' chp1, chp2, etc from titi where 1=1 '
if A <> 'Non' then
    V_Qry := V_Qry || ' and chp1 = ' || Param
end if;

jusque là tout va bien, si je fais afficher ma requête, elle est bien construite, etc.
Comme elle est un tantinet consommatrice (genre plus d'une heure pour passer), je l'ai optimisée en créant les indexes adéquat et en mettant donc un hint dans ma requête pour forcer le passage par cet index.
Quand je la lance "à la main", tout va bien, mon hint est bien interprété, je passe par l'index, j'ai la réponse en moins de 3 minutes.
Quand je passe par ma fonction stockée avec le code suivant :

Code :

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

1
2
3
4
 
C_DbmsSql := dbms_sql.open_cursor;
dbms_sql.parse (C_DbmsSql, V_Qry,2);
R_DbmsSql :=dbms_sql.execute (C_DbmsSql);

Alors là, rien ne va plus, le plan d'exécution redeviens celui pourris que j'avais avant de mettre le hint ...
Donc on dirais bien que dbms_sql n'interprète pas les hints et les fait donc sauter ???
Du coup je ne sais pas quoi faire, car je n'ai pas d'autre moyen d'optimiser ...
J'ai chercher un peu partout notamment sur métalink, mais rien trouvé.

Alors je croise les doigts et je lance ici ma bouteille à la mer.

Merci de me répondre, même un petit début de piste ou d'idée est la bienvenue.

[mnitu]

Bref, je n'ai pas toutes les détailes mais peut être que tu peut créer une vue select /*+hint */ from titi et utiliser cette vue dans ton select dynamique.
Mais il reste des questions:
pour exemple l'optimiseur est en mode règle ou pas ?

[lauraned]

pour exemple l'optimiseur est en mode règle ou pas ?

Bonne question, je ne sais pas.
Je vais tenter de poser la question à mon DBA quand je pourrais (mais de toute façon je ne pourrais pas faire changer le mode, il ne faut pas rêver).

Sinon ce qui me fait penser que le problème viens de dbms_sql, c'est que la même requete passé en direct passe rapidement et via le dbms_sql met 2 heures (en fait surrment plus).

[mnitu]

...
Je vais tenter de poser la question à mon DBA quand je pourrais (mais de toute façon je ne pourrais pas faire changer le mode, il ne faut pas rêver).
...

De toute façon il est bon à savoir parce que en fonction du réponse la suite à donner n'est pas la même.
Est-ce que ma proposition (la vue) est applicable ou pas ?

[lauraned]

mnitu, tout d'abord merci de vous penchez sur mon problème,
mais non malheureusement une vue ne changerais rien car pour que ma vue contienne tous les éléments dont les utilisateurs auront besoin, elle serait la réplique des tables de prod.
Donc la requete que je lancerais sur la vue serait aussi longue a passer, non ??
Même si la réponse est non, je ne pourrais pas réserver une telle place sur la base, donc je n'aurais pas de solutions, mais j'aimerais savoir pour ma culture personnelle.

[mnitu]

Une vue c'est juste une requête stockée dans la base il n'y pas de réplication des données, ça ne prends pas de place vraiment.
L'idée est que le hint pourrait être prise en compte pendant l'exécution de la requête sur la vue.