"UPDATE WHERE EXISTS" aléatoirement KO depuis migration vers Oracle 19c

Bonjour,

Nous avons effectué une migration de Oracle 12c vers Oracle 19c (19.10.0.0.0) récemment. Depuis une requête en particulier plante régulièrement. Il s'agit d'un update sur une table avec une jointure sur d'autres tables dans le where via une clause EXISTS et une sous requête. La requête est banale et fonctionnait très bien depuis 2 ans en 12c.

En l'occurrence, ça plante particulièrement quand la sous-requête n'a qu'une ligne à exploiter (table principale issue d'un xmlelement) et ne renvoie rien (donc aucune mise à jour ne se fait).

En outre, le problème semble aléatoire même lorsque ces conditions sont remplies. Cette requête appelée des milliers de fois par heure dans un contexte de PROD plante environ 1 fois sur 3 et 1 jour sur 3 (les 2 autres jours, aucun plantage et les jours ou ça plante, 2 fois sur 3 ça marche sans explication).

On a changé le EXISTS par un IN pour la jointure via sous requête (ce qui est moins bien pour les perfs...) et depuis ça remarche à tous les coups.

Il semble donc y avoir un bug dans la 19c qui plante sur une certaine structure de requête UPDATE WHERE EXISTS et selon des conditions que je n'ai pas réussi à clairement identifier (sous requête renvoie 0 ligne Ok ; mais pourquoi certains jours et certains appels et pas d'autres ??? Peut être en lien avec le traffic sur la BDD).

Je n'ai malheureusement pas le code erreur d'Oracle car l'exception est cachée et rendue générique (encore une fois en contexte de PROD). Et je n'ai pas d'environnement de test avec exactement la même version d'Oracle sur lequel on reproduirait le problème.

Est-ce que quelqu'un aurait entendu parler de ce genre de bug en 19c pour m'aider à y voir plus clair et savoir quel patch appliquer ?

Merci d'avance,
Seb

Bonjour,

Si tu ne donnes ni l'erreur ni la requête, ça va être compliqué d'aider. Quand tu dis plante, c'est une erreur ou un mauvais résultat?
Il est possible qu'il y ait un bug, mais si ça produit aléatoirement ce sera difficile de faire analyser. Puisque tu as migré en 19c, je te conseille d'appliquer le dernier RU, ça corrige souvent beaucoup de bugs.

Vu que ton erreur est aléatoire, regarde si cette requête n'a pas plusieurs plans d'exécutions : peut-être que l'un est appelé selon les valeurs de la clause WHERE et c'est lui qui plante.
Regarde pour cela dans V$SQL_PLAN et DBA_HIST_SQL_PLAN (mais là tu n'auras que les requêtes les plus consommatrices de ressources).

Est-ce que ta table ou ses index sont partitionnés? Si oui, peut-être qu'une partition se trouve sur un disque dur qui est tombé ou un serveur éteint et c'est lors d'un UPDATE avec des valeurs précises que cette ou ces partitions sont appelées et ça fait planter la requête.

Bonjour,

Merci pour vos réponses.

Quand je dit que ça plante c'est effectivement une erreur Oracle qui lève une exception ORA-xxxx dont je n'ai pas le code.

Nous sommes en version Standard pour des raisons de coûts de licence. Nous avons spécifiquement migré vers la 19c pour bénéficier du HA (haute dispo actif passif) car le RAC (haute dispo actif actif) n'est plus permis en mode Standard par Oracle.

Je vais regarder si j'ai accès aux plans d'exécution de cette requête au moment où elle plantait versus ok.

Qu'est ce qu'il y a dans l'alert.log ?

Bonjour,

Je n'ai malheureusement pas pu récupérer les plans d'exécution du moment où le bug se produisait (il faut dire que je n'ai pas accès à grand chose, la BDD étant infogérée en externe).

Concernant l'alert.log, pas vu d'erreur spécifique les jours où le bug a eu lieu, seulement des artefacts de la migration (software edition is incompatible with SQL plan management) apparemment sans impact d'après ce que j'ai lu, et des traces de quelques incidents avec microcoupures de la base et pertes de connexions.

Je suis pas mal occupé par le rattrapage des données suite à ce bug. Je reprendrais ma chasse au sorcières un peu plus tard...

On n'a ni la requête ni l'erreur Oracle. Honnêtement je ne vois pas comment faire.

Voici la requête qui posait problème aléatoire (même si je doute que ça puisse aider sans connaitre le schéma) :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

UPDATE DBA_FID.BDDO_FID_VOUCHER Z SET VCH_VCS_ID = 7 WHERE Z.VCH_VCS_ID <> 3 AND VCH_ACC_ID = VAR_ACC_ID AND EXISTS ( SELECT 1 FROM TABLE(XMLSequence(PurchaseTicket.extract('/PurchaseTicket/Lines'))) INNER JOIN DBA_FID.BDDO_FID_VOUCHER V ON extractvalue(column_value, '/Lines/AdvantageId') = V.VCH_ID LEFT JOIN DBA_FID.BDDO_FID_GRP_VOUCHER G ON G.GLV_ID = V.VCH_GLV_ID LEFT JOIN DBA_FID.BDDO_FID_OFFER O ON V.VCH_OFF_ID = O.OFF_ID WHERE OFF_OFT_ID in (1, 2, 4) AND VCH_ACC_ID = VAR_ACC_ID AND Z.VCH_GLV_ID = GLV_ID ) ;

---

Le problème avait lieu uniquement certains jours, sur certains appels qui avaient en commun les caractéristiques suivantes :
- La table issue de XMLELEMENT dans la sous-requête renvoyait une ligne exactement.
- Cette ligne ne matchait pas avec la première table jointée dans la sous-requête (id = 0 inexistant).
- le produit de cette jointure renvoyait donc 0 lignes dans la sous-requête.

Et sa version patchée :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

UPDATE DBA_FID.BDDO_FID_VOUCHER Z SET VCH_VCS_ID = 7 WHERE Z.VCH_VCS_ID <> 3 AND VCH_ACC_ID = VAR_ACC_ID AND VCH_ID IN ( SELECT VCH_ID FROM TABLE(XMLSequence(PurchaseTicket.extract('/PurchaseTicket/Lines'))) INNER JOIN DBA_FID.BDDO_FID_VOUCHER V ON extractvalue(column_value, '/Lines/AdvantageId') = V.VCH_ID LEFT JOIN DBA_FID.BDDO_FID_GRP_VOUCHER G ON G.GLV_ID = V.VCH_GLV_ID LEFT JOIN DBA_FID.BDDO_FID_OFFER O ON V.VCH_OFF_ID = O.OFF_ID WHERE OFF_OFT_ID in (1, 2, 4) AND VCH_ACC_ID = VAR_ACC_ID AND Z.VCH_GLV_ID = GLV_ID ) ;

---

En relisant, je vois qu'en patchant, on a ajouté une jointure 1..1 (VCH_ID = VCH_ID) mais en principe la jointure VCH_GLV_ID = GLV_ID entre requête principale et sous-requête est suffisante.
Encore une fois, la requête avec le EXISTS s'est bien exécuté sans problème pendant des années avant que l'on migre sur la 19C (y compris avec les caractéristiques qui posent problème en 19C).

Une analyse des bug a conclu que le problème est apparu immédiatement après la migration vers 19C. Il y a donc vraisemblablement une structure de requête qui passait en 12C et plus en 19C. Je ne trouve pas ça normal car ces versions sont sensées être rétrocompatibles. D'où ma recherche d'un bug Oracle et votre sollicitation si quelqu'un avait rencontré un problème similaire (sur la 19C Standard en HA spécifiquement). :)

En outre, si on trouve à quel bug Oracle ça correspond, on pourra patcher le serveur pour que le problème n'apparaisse pas sur une autre requête et on pourra se retourner contre Oracle au besoin (après tout, on ne paye pas une licence pour avoir des bugs, même si c'est la version standard !)

Possible qu'il y ait un bug oui, d'autant plus que XMLSEQUENCE est déprécié en 19c:

https://docs.oracle.com/en/database/...2-1FECAD17E569

Citation:

---

Note:
The XMLSEQUENCE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function instead. See XMLTABLE for more information.

---

Par exemple, sur le support Oracle, ce bug est indiqué, mais aucun détail n'est fourni:
Bug 31404014 - xmlsequence wrong results (Doc ID 31404014.8)

Corrigé dans le RU10.
Donc, ce que je te conseille:
- appliquer le dernier RU pour la 19c (ça doit être le 11 je pense).

Voir si ton problème persiste. Sinon, réécrire la requête en utilisant XMLTABLE plutôt que XMLSEQUENCE.

Merci beaucoup pour cette réponse, c'est exactement ce que je cherchais et que je n'avais pas trouvé.

Je vais remonter ces éléments aux équipes de développement.

Je ne passe pas tout de suite à résolu au cas où il y ait d'autres suggestions. Je ferais courant de semaine prochaine.

Quelques remarques :

• Remettre la table BDDO_FID_VOUCHER dans la sous-requête avec EXISTS est probablement inutile, mais difficile d'être certain car de nombreuses colonnes ne sont pas préfixées.
• La même ligne peut aussi être remise à jour, ce que vous pouvez éviter en modifiant ce filtre : WHERE Z.VCH_VCS_ID not in (3, 7)
• Quitte à utiliser un IN, vous pouvez utiliser le rowid :
  
  Code:

  ---

  1 2 3

  AND rowid IN ( SELECT V.rowid

  ---

Bonjour,

En fait, le caractère imprévisible et aléatoire du problème a fait qu'il est revenu plusieurs jours après le patch qui avait transformé le EXISTS en IN (après plusieurs jours de fonctionnement nominal de fort trafic).

Pour l'instant, nous avons mis cette requête sous conditionnelle pour qu'elle s'exécute le moins possible et nous allons probablement la transformer en merge (plusieurs merge similaires existent et ne plantent jamais).

Prochaine étape : solliciter le support Oracle à propos de ce bug apparu depuis la migration vers la 19C et patcher notre version d'oracle (on est sur la 19.10.0.0.0).

Seb