Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 7 sur 7
  1. #1
    Membre chevronné Avatar de dumas.blr
    Homme Profil pro Jean-Yves DUMAS
    Consultant informatique
    Inscrit en
    juin 2010
    Messages
    540
    Détails du profil
    Informations personnelles :
    Nom : Homme Jean-Yves DUMAS
    Âge : 51
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : juin 2010
    Messages : 540
    Points : 674
    Points
    674

    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 :
    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
    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
    Modérateur
    Avatar de pachot
    Homme Profil pro Franck Pachot
    Consultant DBA en Suisse (dbi services) OCM 11g
    Inscrit en
    novembre 2007
    Messages
    1 365
    Détails du profil
    Informations personnelles :
    Nom : Homme Franck Pachot
    Âge : 43
    Localisation : Suisse

    Informations professionnelles :
    Activité : Consultant DBA en Suisse (dbi services) OCM 11g
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : novembre 2007
    Messages : 1 365
    Points : 4 070
    Points
    4 070

    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 - Consultant en Suisse Romande (dbi services) - Oracle Certified Master (OCP 12c et OCM 11g) - twitter: @FranckPachot


  3. #3
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 146
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 146
    Points : 3 745
    Points
    3 745

    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 chevronné Avatar de dumas.blr
    Homme Profil pro Jean-Yves DUMAS
    Consultant informatique
    Inscrit en
    juin 2010
    Messages
    540
    Détails du profil
    Informations personnelles :
    Nom : Homme Jean-Yves DUMAS
    Âge : 51
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : juin 2010
    Messages : 540
    Points : 674
    Points
    674

    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 chevronné Avatar de dumas.blr
    Homme Profil pro Jean-Yves DUMAS
    Consultant informatique
    Inscrit en
    juin 2010
    Messages
    540
    Détails du profil
    Informations personnelles :
    Nom : Homme Jean-Yves DUMAS
    Âge : 51
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : juin 2010
    Messages : 540
    Points : 674
    Points
    674

    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
    Expert Confirmé Avatar de pacmann
    Homme Profil pro Pacman Pacman
    Consulté Oracle
    Inscrit en
    juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Nom : Homme Pacman Pacman
    Âge : 33
    Localisation : France, Bas Rhin (Alsace)

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

    Informations forums :
    Inscription : juin 2004
    Messages : 1 626
    Points : 2 626
    Points
    2 626

    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 chevronné Avatar de dumas.blr
    Homme Profil pro Jean-Yves DUMAS
    Consultant informatique
    Inscrit en
    juin 2010
    Messages
    540
    Détails du profil
    Informations personnelles :
    Nom : Homme Jean-Yves DUMAS
    Âge : 51
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Conseil

    Informations forums :
    Inscription : juin 2010
    Messages : 540
    Points : 674
    Points
    674

    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

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •