ORA-01555 - Quand sont utilisés les rollback segments ?

[dumas.blr]

Bonjour,

Je suis en train d'exécuter une requête de type PIVOT

Code :

1
2
3
4
5
 
SELECT *
FROM
  (SELECT SLOT_NAME, SLOT_VALUE, mc_ueid FROM EXTENDED_EVENT_SLOTS
  ) PIVOT (MAX (SLOT_VALUE) AS VALEUR FOR (SLOT_NAME) IN ('sfr_SI' AS SI, 'sfr_code_SSA' AS CodeSSA, 'sfr_ack_date' AS AckDate, 'sfr_ack_user' AS AckUser, 'sfr_closed_date' AS ClosedDate, 'sfr_closed_user' AS ClosedUser, 'sfr_nom_CI' AS NomCI, 'sfr_domaine' AS Domaine, 'sfr_url_consigne' AS UrlConsigne, 'sfr_source' AS Source, 'sfr_msg' AS MESSAGE, 'sfr_ref_incident' AS incident, 'sfr_ref_mep_dds' AS mepdds, 'sfr_time_to_ack' AS acquittement))

sur une table contenant un grand nombre d'enregistrement (environ 27 millions)

et je tombe en erreur ORA-01555

Code :

1
2
3
4
5
6
7
 
ORA-01555: clichés trop vieux : rollback segment no 1, nommé "_SYSSMU1_2561240141$", trop petit
01555. 00000 -  "snapshot too old: rollback segment number %s with name \"%s\" too small"
*Cause:    rollback records needed BY a reader FOR consistent READ are
           overwritten BY other writers
*Action:   IF IN Automatic Undo Management mode, increase undo_retention
           setting. Otherwise, USE larger rollback segments

Je pensais que les rollback segments n'intervenaient uniquement que dans les opérations de mise à jour (INSERT, UPDATE, DELETE) et aucunement dans les SELECT. Ce n'est manifestement pas le cas.

Quelqu'un peut m'expliquer précisément le fonctionnement de ceux-ci ? est-ce qu'ils sont mis en oeuvre lors d'opération particulières (type PIVOT/ UNPIVOT) ?

Merci d'avance pour vos réponses éclairées

[pachot]

Envoyé par dumas.blr

Je pensais que les rollback segments n'intervenaient uniquement que dans les opérations de mise à jour (INSERT, UPDATE, DELETE) et aucunement dans les SELECT. Ce n'est manifestement pas le cas.

Effectivement, ils sont aussi utilisés pour faire des lectures cohérentes (consistent read) sans avoir à verrouiller les données:
Lorsque le select rencontre une modification faite par une autre session, non encore commitée, ou commitée mais après le debut de notre SELECT, alors Oracle va aller voir l'image antérieure des données - grâce à l'UNDO stocké dans les rollback segments.

Si vous avez des requêtes longues qui listent des données en cours de modification, il peut être nécessaire d'augmenter l' UNDO_RETENTION.

Cordialement,
Franck.

[skuatamad]

A lire impérativement :
Data Concurrency and Consistency

Read Consistency and Undo Segments

To manage the multiversion read consistency model, the database must create a read-consistent set of data when a table is simultaneously queried and updated. Oracle Database achieves this goal through undo data.

Whenever a user modifies data, Oracle Database creates undo entries, which it writes to undo segments ("Undo Segments"). The undo segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Thus, multiple versions of the same data, all at different points in time, can exist in the database. The database can use snapshots of data at different points in time to provide read-consistent views of the data and enable nonblocking queries.

Read consistency is guaranteed in single-instance and Oracle Real Application Clusters (Oracle RAC) environments. Oracle RAC uses a cache-to-cache block transfer mechanism known as Cache Fusion to transfer read-consistent images of data blocks from one database instance to another.

[dumas.blr]

Merci pour cette réponse

Si vous avez des requêtes longues qui listent des données en cours de modification, il peut être nécessaire d'augmenter l' UNDO_RETENTION

J'imagine que l'UNDO_RETENTION n'est pas suffisant, et qu'il faille aussi augmenter le nombre de rollback segment associés à la transaction. Ce qui risque d'être compliqué car cette table est alimentée en temps réel.

En fait, ma problématique est la suivante : Pour des besoins de reporting, et afin de ne pas charger inutilement la machine et la BDD, cette requête fait partie d'une requête plus complète qui crée toute les heures une vue matérialisée; cette dernière étant utilisée par l'outil de BI Business Object (c'est un "pseudo-datamart", avec les inconvénient inhérent aux snapshots : pas d'index, ni de partition, donc accès non optimisés ... ).

Je me demandais s'il existait un ordre spécifique SQL permettant de bypasser la sécurité du consistent read, et donc de s'affranchir de la contrainte des rollback segment.
Bien sur le risque est d'obtenir des données incohérentes, car ne prenant opas en compte les derniers raffraichissement, mais c'est un risque qui est parfaitement tolérable car la marge d'erreur sur ces reporting sera relativement faible.

Si ce n'est pas le cas, quelles sont les pistes de contournement possibles ?

J'en vois personnellement plusieurs :

1. paralléliser la requête : on s'expose moins au risque de mise à jour concommitante car le temps de traitement est divisé
2. Décomposer la requête, et donc passer par un PL/SQL plutôt que par une vue matérialisée, ce qui permettrait de supprimer les inconvénients cités plus haut

De plus, je me demande si le fait de remplacer l'operation PIVOT par des DECODE n'économiserait pas de la ressource.

Enfin, si aucune solution native Oracle n'st stisfaisante, il reste toujours la solution de l'ETL, mais là, on par sur d'autre contraintes d'ordre budgétaire, et de supervision, pour une application qui, elle-même, fait déjà de la supervision ...

[dumas.blr]

Je viens de lire le lien posté par skuatamad.

Est ce que le SELECT ... AS OF ... (que je n'ai jamais utilisé) ne résoudrait pas mon problème ?

Merci pour votre réactivité

[pacmann]

Salut,

1) AS OF TIMESTAMP utilise justement l'undo. Ca donne une lecture consistante... dans le passé, donc c'est encore pire dans ton cas.

2) Impossible de bypasser la lecture consistante, c'est un mécanisme de base.

3) decode au lieu de PIVOT... la remarque est intéressante : de manière générale, optimiser la requête peut être une piste. Il faut voir le plan d'exécution.
En fait, pour aller plus loin, s'il s'agit d'une démarche de supervision, il y aurait peut être moyen de cibler les données modifiées ? Date de modif indexée par exemple ? Ou alors justement travailler sur le rafraîchissement de la vue matérialisée pour qu'elle capte le delta ?

4) Je dirais que ce n'est pas le nombre de rollback segments alloués à la transaction qu'il faut augmenter, mais la taille de l'undo de manière générale (combinée avec une augmentation de l'undo retention)

5) ETL : c'est bien pour ça qu'il y a des datawarehouse, parce que le reporting cohabite assez mal avec une base transactionnelle...

[dumas.blr]

Merci Pacman pour tes réponses

Je n'ai pas chomé durant ce temps et j'ai essayé de me cultiver sur le sujet, que je connaissais très mal, des vues matérialisées.



Ca m'a permis de tordre le cou à certaine idées reçues que j'avais acquises, par méconnaissance du sujet.

• 1er idée reçue à tuer (pan !) : Oui, on peut mettre des index sur une vue matérialisée
• 2ème idée recue à casser : Non une vue matérialisée ne se regénère pas tout le temps from scratch. on peut faire ce que tu suggère dans ton post

  Ou alors justement travailler sur le rafraîchissement de la vue matérialisée pour qu'elle capte le delta ?

Je me suis donc penché sur le concept de journal de vue matérialisée, qui, si j'ai bien compris, stocke le contenu de tous les rollback segment dans ces log et permet un rafraichisement en mode différentiel, (clause REFRESH FAST). Ce concept me séduit tout à fait, mais appelles d'autres questions :

1. Faut-il nécessairement poser un journal de log sur l'ensemble des tables en entrée de la MV pour que le REFRESH FAST fonctionne ?
2. Comment se gère le système de purge des log, sachant que ces tables puvent être à l'origine de plusieurs MV, qui n'ont pas toutes la même fréquence de rafraissement. Y a t'il alors une dépendence entre toutes les MV ?
3. Est-ce que cela signifie que le SQL associé à la MV n'est plus utilisé, passé la génération initiale ? sinon, comment l'optimiser pour qu'il ne travaille que sur les dernières mises à jour entre 2 refresh de MV ?
4. J'ai vu qu'il y avait un attibut de regénération ON COMMIT. Permet-il un raffraichissement en temps réel ( et donc de ne plus utiliser le ON DEMAND START WITH ... NEXT ...) ? Quels sont les riques ? Enfin, quelles sont les conditions nécessaires pour satisfaire à cette clause ? (j'ai essayé mais je me retrouve avec une erreur 12054 - the MV did not satisfy conditions for refresh at commit time).

En outre, j'ai 2 autres question sur le parrallélisme.

• dans la création de la MV, il y a une clause PARALLEL (nombre de //). Quelle est la différence avec les Hint PARALLEL que je positionne dans ma clause SELECT d'origine ?
• J'ai vu que l'on pouvait partitionner les index des MV. Quelle est la différentiation avec un partitionnement de table ? est-ce que la syntaxe est la même ?

Merci d'avance pour vos réponses éclairées