IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

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


Sujet :

SQL Oracle

  1. #1
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut ORA-01555 - Quand sont utilisés les rollback segments ?
    Bonjour,

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

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

  2. #2
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par dumas.blr Voir le message

    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.
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  3. #3
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    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.

  4. #4
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut
    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 ...
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

  5. #5
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut
    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é
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

  6. #6
    Membre émérite Avatar de pacmann
    Homme Profil pro
    Consulté Oracle
    Inscrit en
    Juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : Juin 2004
    Messages : 1 626
    Points : 2 845
    Points
    2 845
    Par défaut
    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...

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  7. #7
    Membre éclairé Avatar de dumas.blr
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juin 2010
    Messages
    598
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : Juin 2010
    Messages : 598
    Points : 879
    Points
    879
    Par défaut
    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
    S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !!!
    si tout est OK, n'oubliez pas de cliquer sur

Discussions similaires

  1. Réponses: 1
    Dernier message: 30/08/2010, 00h57
  2. [HTML] empêcher le scrolling quand sont pressées les touches
    Par chuckey dans le forum Balisage (X)HTML et validation W3C
    Réponses: 3
    Dernier message: 11/03/2009, 12h44
  3. [9i]Ne pas utiliser les rollback segments
    Par loudo dans le forum Oracle
    Réponses: 3
    Dernier message: 15/06/2006, 15h17
  4. Oracle 8.1.7.4 : Vider les Rollback segments
    Par beyonder2005 dans le forum Oracle
    Réponses: 7
    Dernier message: 02/11/2005, 15h37
  5. Les Rollback Segments
    Par blids dans le forum Administration
    Réponses: 20
    Dernier message: 18/05/2004, 20h22

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo