Discussion :

[Drizzt [Drone38]]

Bonjour,

J'ai une requete Oracle qui fonctionne comme je le souhaite et avec des temps de réponses de l'ordre de la seconde. Pas de problème de ce coté la.

Maintenant cette requete est utilisée dans du Pro*C et c'est la que ça se gate. La requete met alors une dizaine de minutes à retourner un résultat qui est de plus faux (pas le bon nombre de lignes).

Je sais que la requete est correcte d'un point de vue Oracle vu que je l'ai testée unitairement.
Je sais que le Pro*C utilisant le retour de la requete est correct vu que j'ai testé en remplaçant la requete par un select * from table en ayant remplit "table" avec le résultat de ma requête.

J'ai testé différentes solution :
- Requete dynamique (code ci-dessous)
- Utilisation d'une vue (avec et sans requete dynamique)
Avec le même résultat.
Je n'ai pas pu tester une vue matérialisée car cela pose d'autres soucis.


Ma question est donc de savoir pourquoi on observe ce comportement ?
Est-ce corrigeable et si oui comment ?
J'aimerai éviter la solution de remplir une table avec le résultat de ma requete avant le traitement.

Voici la requete banalisée :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
WITH TGN AS (
  SELECT TGN_STR, min(path) keep(dense_rank last ORDER BY l) path, max(l) l 
    FROM (
          SELECT TGN_STR, level l, SYS_CONNECT_BY_PATH(TGN_STR, '|') path 
            FROM TABLE_TGN_STR 
         connect BY prior TGN_STR=TGN_STR_PERE
        ) 
    GROUP BY TGN_STR
)
SELECT count(*) 
FROM
(
    SELECT CHAMP_STR1 CHAMP_STR1,
           DECODE(CHAMP_FLAG, 'E', 0, 1) CHAMP_FLAG,
           100-10*(decode(instr(TGN1.path, act.TGA),0,0,instr(TGN1.path, act.TGA)-2))/4 +
           1000-100*(decode(instr(TGN2.path, act.TGB),0,0,instr(TGN2.path,act.TGB)-2))/4 +
           1000*tgn1.l + 
           10000*tgn2.l +
           DECODE(INFO, NULL, 4, 0) + 
           DECODE(act.C1, 
                  NULL, 3, 
                  DECODE(act.C2, 
                         NULL, 2,
                         DECODE(act.C3, NULL, 1, 0))
                  ) CHAMP_NUM1,
           TRIM(RPAD(act.C6, 4)||
               act.C5||
               RPAD(tgn1.TGN_STR, 3)||
               RPAD(tgn2.TGN_STR, 3)||
               RPAD(NVL(act.C1, ' '), 5)||
               RPAD(NVL(act.C2, ' '), 5)||
               RPAD(NVL(act.C3, ' '), 5)||
               RPAD(NVL(act.C4, ' '), 5)
           ) CHAMP_STR3,
           DECODE(INFO, NULL, 1, 0) CHAMP_NUM2, 
           CHAMP_STR4 CHAMP_STR4,
           TO_CHAR(DATE_DEBUT, 'YYYYMMDDHH24MISS') date_debut_str,
           DATE_DEBUT date_debut,
           TO_CHAR(DATE_FIN, 'YYYYMMDDHH24MISS') date_fin_str,
           DATE_FIN date_fin
      FROM TABLE_ACT ACT
              JOIN TGN TGN1 ON instr(TGN1.path, act.TGA) > 0
              JOIN TGN TGN2 ON instr(TGN2.path, act.TGB) > 0
);

Et voici le code Pro*C en version requete dynamique ( obligatoire vu la requete, le Pro*C ne compilant pas sinon )

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
VARCHAR              dyn_requete[4000];
long Nombre;
 
strcpy(dyn_requete.arr, "requete ci-dessus");
dyn_requete.len = strlen(dyn_requete.arr);
 
EXEC SQL CONTEXT USE :g_OracleContext;
EXEC SQL PREPARE ReqCount FROM :dyn_requete;
EXEC SQL DECLARE Cursor CURSOR FOR ReqCount;
EXEC SQL OPEN Cursor;
/* gestion d'erreur */
EXEC SQL FETCH Cursor INTO :Nombre;
EXEC SQL CLOSE Cursor;

Note : ici j'ai mis un count(*) dans la requete pour simplifier et voir le nombre de ligne retournées. Nombre qui est donc différent de la même requete executée directement sous SQL Dev par exemple.
Le : g_OracleContext est bien défini.


Merci pour vos réponses/pistes

(Note bis: ce post est un doublon d'un autre que je passe en delestage, le précédent n'étant pas assez clair et mal positionné dans le forum)

[mnitu]

En supposant que vous ne vous ai pas trompé dans vos tests et en absence d’un jeu d’essai qui permettra de reproduire votre problème je vous suggère d’utiliser pour l’instant une fonction Pl/SQL qui exécute votre select et l’appeler en PRO*C. Est-ce que dans ce cas vous obtenez un résultat correct ?

[Drizzt [Drone38]]

Merci pour la réponse,

C'est la piste que je regarde pour l'instant.

Je vais tester rapidement sur le count(*) pour voir ce que ça donne (enfin rapidement à cout de 40min par test c'est pas complétement gagné).

[Drizzt [Drone38]]

Meme souci en passant par un package...
Alors que la fonction appelé via SQL*Plus renvoie bien le bon résultat en quelques secondes.

Je ne comprends vraiement pas la...

[Drizzt [Drone38]]

J'ai le coupable, mais toujours pas d'explication.

Il s'agit du SYS_CONNECT_BY_PATH

Sans ce dernier, si je remplace par un bete champ, résultat (partiel du coup) en 2 secondes.

Si je remplace toute la sous-requete par une table contenant les bonnes infos, résultat complet cette fois, en 2secondes aussi. Ce qui prouve que c'est bien le SYS_CONNECT... et non l'utilisation du résultat de la sous_requete qui est en cause.


Si vous avez des pistes sur la cause je suis preneur. Ou sur comment remplacer le sys_connect_by_path par autre chose.

[mnitu]

Vous avez juste un problème de performance ou un problème de résultat incorrect ?

[Drizzt [Drone38]]

Les deux quand ça met 10 minutes, le résultat est faux.

[mnitu]

C’est étonnant parce que PL/SQL utilise le même moteur SQL ce qui n’est pas valable pour le PRO*C.
En sqlplus la procédure package donne le bon résultat ?

[Drizzt [Drone38]]

Oui via SQL*Plus le résultat est correct en appelant le package.

[Waldar]

Qu'est-ce que ça donne en créant une vue qui contient la requête côté base de données et un simple SELECT * FROM MaVue côté Pro*C ?

[mnitu]

Oui via SQL*Plus le résultat est correct en appelant le package.

Alors ça doit être pareil en appelant la procédure depuis Pro*C.

[Drizzt [Drone38]]

Qu'est-ce que ça donne en créant une vue qui contient la requête côté base de données et un simple SELECT * FROM MaVue côté Pro*C ?

Bonjour et merci,
J'avais déjà testé ce point, ce fut ma première piste. Même résultat.


mnitu> Je suis bien d'accord, en théorie ça devrait être pareil. Mais ce n'est hélas pas ce que j'observe.


Sinon j'ai ré-ecrit ma requête en supprimant SYS_CONNECT_BY_PATH et en utilisant CONNECT_BY_ROOT à la place (et en adaptant le reste de la requete, ce qui l'a simplifie un peu d'ailleurs).
Je n'ai pas encore le résultat, je vous tiens au courant.

[Drizzt [Drone38]]

Avec la nouvelle requete, j'obtient une magnifique erreur Oracle lorsque je l'appele via Pro*C :

ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [],

Ca me donne envie de pleurer
Cette erreur à l'air en plus d'avoir plein de causes différentes possibles... Même si toutes celles que j'ai observées sont corrigées dans ma version d'Oracle : 10.2.0.4

[Drizzt [Drone38]]

Victory !!!!!!

Donc pour résumer :
SYS_CONNECT_BY_PATH : resultat erroné et temps d'execution aberrant quelque soit la façon dont j'ai pu l'appeler (via VUE, package, vue de vue, requete dynamique). Fonctionnement OK hors Pro*C

Après ré-écriture de la requete en utilisant CONNECT_BY_ROOT je tombe sur l'erreur Oracle ORA-00600 lors de l'appel via Pro*C (toujours OK hors Pro*C), la encore avec différents types d'appels (VUE, package, requete dynamique).

Par contre, cette fois en remplaçant le bloc CTE dans lequel se trouve le CONNECT_BY_ROOT par une vue et en utilisant cette vue dans une requete dynamique (obligatoire à cause des fonctions analytiques) cela fonctionne correctement.
Alors que cela ne fonctionnait pas si j'appelais simplement une VUE contenant la totalité de la requête.


Bref, Pro*C m'a fait perdre 3 jours.

Question bonus si quelqu'un passe par là. En compilant mon Pro*C j'ai le warning suivant :

warning: argument #1 is incompatible with prototype:
prototype: pointer to char : "/usr/include/iso/string_iso.h", line 65
argument : pointer to unsigned char

J'ai rien contre, mais vu que j'ai suivi l'exemple de la doc Oracle pour ma requete dynamique (cf premier post) je suis un peu surpris.
Et comme je suis un gros débutant en C/Pro*C pour pas dire néophyte, si quelqu'un peut m'indiquer comment éviter ce warning ?

Merci à tous pour vos participations.