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

Administration Oracle Discussion :

Delete snapshots AWR : 30 000 physical reads et 8 000 000 de buffer gets


Sujet :

Administration Oracle

  1. #1
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut Delete snapshots AWR : 30 000 physical reads et 8 000 000 de buffer gets
    Bonjour tout le monde,

    Sur une base j'ai noté un script qui s'exécute pour nettoyer les snapshots AWR de plus de 31 jours.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    delete from WRM$SNAPSHOT_DETAILS where dbid = xx and snap_id >=0 and snap_id <= 2500;
    il n'y en a que 24 pour ce delete à supprimer (24 par jour).

    Le plan d'exécution est un full table scan.
    Ce qui m'interloque c'est que le nombre de physical reads est de 30 000 (trente mille) et le nombre de buffer gets de 8 000 000 (8 millions) pour une seule exécution

    Est-ce que vous pourriez m'expliquer ce ratio de 250?

    J'avais pensé à une histoire de FK et le fait que le DELETE génère N SELECTs sous le capot pour valider le DELETE. Mais, sauf erreur de ma part, cela n'explique pas ce ratio immense et, de toute façon, les blocs lus pour ces SELECTs ne doivent pas apparaître dans le plan d'exécution ou dans les stats de cet ordre SQL.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  2. #2
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    Il faut regarder du côté des objets connectés à la table : index, déclencheurs, vues matérialisées.

  3. #3
    Membre confirmé
    Homme Profil pro
    xxxxxxxxx
    Inscrit en
    Avril 2015
    Messages
    392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : xxxxxxxxx

    Informations forums :
    Inscription : Avril 2015
    Messages : 392
    Points : 552
    Points
    552
    Par défaut
    Alors. on. peut. utiliser. la. procédure. suivant. pour. la. durée. de. rentention. des. snapshots. du AWR
    dbms_workload_repository.modify_snapshot_settings
    (retention=>. ....
    Et. pour. ta. requête. problèmatique. utilise. le. conseiller. dbms_sqltune. pour. analyser. le. delete
    Bonne chance

  4. #4
    Membre expérimenté
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Septembre 2016
    Messages
    729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2016
    Messages : 729
    Points : 1 414
    Points
    1 414
    Par défaut
    Citation Envoyé par dell68 Voir le message
    utilise. le. conseiller. dbms_sqltune. pour. analyser. le. delete
    Attention aux licences !!!

    "The following subprograms of the DBMS_SQLTUNE package provide an interface to manage SQL tuning sets and are part of the Real Application Testing option as well as the Database Tuning Pack"
    https://docs.oracle.com/cd/B19306_01...s.htm#DBLIC150
    Le savoir est une nourriture qui exige des efforts.

  5. #5
    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 Ikebukuro Voir le message
    Ce qui m'interloque c'est que le nombre de physical reads est de 30 000 (trente mille) et le nombre de buffer gets de 8 000 000 (8 millions) pour une seule exécution
    Ca veut juste dire que la pluspart des buffer gets sont en cache, est seulement 0.3% doivent être lus sur disque. Il y a des index sur cette table, probablement que la pluspart des blocs sont en buffer cache
    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

  6. #6
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Un gros merci à vous tous mais un spécial thank à Pachot qui, une fois de plus, a trouvé la réponse simple à un problème qui m'apparaissait complexe.

    C'était tellement basique que j'en suis gêné de ne pas avoir compris cela plus tôt...
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  7. #7
    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 Ikebukuro Voir le message
    C'était tellement basique
    Ce n'est pas si évident car dans ce qu'on lit souvent on a l'impression que l'accès à un bloc est soit un physical read, soit un logical read (buffer get). En fait, tous les blocs lus se font avec un logical read. Physical read est une activité supplémentaire qui l'amène en mémoire lorsqu'il n'y est pas déjà.
    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

  8. #8
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Bon, ça se complique...

    Les snapshots AWR occupent 1.13 Go sur disque.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select round(space_usage_kbytes/(1024*1024),3) AS "Taille Go" 
    from v$sysaux_occupants
    where occupant_name = 'SM/AWR';
    Taille Go
    ---------------
    1,1334228515625
    Le block size du tbs SYSAUX est de 8Ko.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    select block_size from dba_tablespaces where tablespace_name = 'SYSAUX';
    BLOCK_SIZE
    ----------
    8192

    Nombre de blocs dans SYSAUX pour les snapshots AWR : 150 000, comprenant pour moitié les snapshots et les index.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    select (space_usage_kbytes*1024)/8192 
    from v$sysaux_occupants
    where occupant_name = 'SM/AWR';
     
    (SPACE_USAGE_KBYTES*1024)/8192
    ------------------------------
    148560

    On est donc très loin des 8 000 000 de blocs à lire...

    Le seul cas où j'avais eu un nombre de gets incroyablement plus important que le nombre de physical reads, c'était lors d'un produit cartésien : 3 000 blocs lus sur disque dur et 100 000 000 de gets de mémoire.

    Là, j'avoue ne pas comprendre car pour lire tous les blocs lors du full scan, Oracle doit en lire au plus 150 000 sur disque dur. Et 150 000 en mémoire, sauf s'il doit en lire certains plusieurs fois mais, pour un full scan, je ne vois pas pourquoi Oracle ferait cela. En tout cas le ratio de 250 ne s'explique toujours pas.

    Au fait, quant on me conseille de voir les triggers, on est d'accord pour dire que toutes les opérations liées à un ordre SQL n'apparaissent pas dans les stats Oracle de cet ordre mais à côté, elles sont liées à l'ordre SQL relié mais pas le principal.
    Donc les 8 000 000 de buffer gets, pour moi, sont lié au DELETE dans la table des snapshots AWR, point. Et donc aucun lien avec les index puisque Oracle fait un FTS.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  9. #9
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    Citation Envoyé par Ikebukuro Voir le message
    Donc les 8 000 000 de buffer gets, pour moi, sont lié au DELETE dans la table des snapshots AWR, point. Et donc aucun lien avec les index puisque Oracle fait un FTS.
    Oui mais il faut maintenir l'index pour impacter les suppressions.

  10. #10
    Membre confirmé
    Homme Profil pro
    xxxxxxxxx
    Inscrit en
    Avril 2015
    Messages
    392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : xxxxxxxxx

    Informations forums :
    Inscription : Avril 2015
    Messages : 392
    Points : 552
    Points
    552
    Par défaut
    envoi. le. plan. d’execution. correspondant. en. ajoutant. le. hint. /*+. gather_plan_statistics. */

  11. #11
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Désolé mais je ne peux pas lancer de requête en prod...
    Et oui, je suis DBA non Autonome, j'ai le niveau 12c mais pas le 19c
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  12. #12
    Membre confirmé
    Homme Profil pro
    xxxxxxxxx
    Inscrit en
    Avril 2015
    Messages
    392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : xxxxxxxxx

    Informations forums :
    Inscription : Avril 2015
    Messages : 392
    Points : 552
    Points
    552
    Par défaut
    si. tu. as. un. serveur. de. test. tu. peux. le. faire. ??

  13. #13
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Le pb est que la volumétrie n'a rien a voir entre les deux bases, PROD et TEST.
    En outre, lancer un DELETE ... je pourrais faire un explain plan mais on perd le bénéfices du hint dont tu parles, il faut que la requête soit vraiment exécutée...

    Bon, passons
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  14. #14
    Membre confirmé
    Homme Profil pro
    xxxxxxxxx
    Inscrit en
    Avril 2015
    Messages
    392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : xxxxxxxxx

    Informations forums :
    Inscription : Avril 2015
    Messages : 392
    Points : 552
    Points
    552
    Par défaut
    Merci. des. éclaircissements. mais. avec. ce. plan. d’exécution. généré. incluant. toutes. les. statistiques.
    tu. verras. plus. claire. et. quelle. opération. faut. il. optimiser. ????

  15. #15
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Ah mais il n'y a aucune opération à optimiser, l'ordre SQL est hyper basique :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    delete from WRM$SNAPSHOT_DETAILS where dbid = xx and snap_id >=0 and snap_id <= 2500;
    et le plan d'exécution est un simple FTS sur WRM$SNAPSHOT_DETAILS, rien de plus.

    Je ne veux rien optimiser mais juste comprendre ce ratio de 250 entre les physicals I/O et les logocals I/O.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  16. #16
    Rédacteur

    Homme Profil pro
    Consultant / formateur Oracle et SQL Server
    Inscrit en
    Décembre 2002
    Messages
    3 460
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Consultant / formateur Oracle et SQL Server

    Informations forums :
    Inscription : Décembre 2002
    Messages : 3 460
    Points : 8 073
    Points
    8 073
    Par défaut
    Citation Envoyé par Ikebukuro Voir le message
    ... juste comprendre ce ratio de 250 entre les physicals I/O et les logocals I/O.
    Attention à ne pas prendre les choses par le mauvais bout : un ratio entre lectures physiques et lectures logiques ne permet de tirer aucune conclusion.

    D'après vos chiffres, vous avez 8 millions de lectures logiques. A-t-on lu 8 millions de blocs différents, ou à l'extrême 8 millions de fois le même bloc, on n'en sait rien.
    Vous avez dû lire 30 000 blocs depuis le disque pour les monter en mémoire. Ça pourrait vouloir dire que les 7 970 000 autres étaient déjà en mémoire.

    Moi, la question que je me poserais, c'est : semble-t-il cohérent d'avoir 8 millions de lectures logiques, soit 64 Go, par rapport au volume des lignes de données à supprimer ?
    Pour moi c'est douteux, car les lignes dans cette table sont très peu volumineuses, il y a un seul index, pas de déclencheur...

    Etes-vous sûr de cette valeur de 8 millions de lectures logiques ? Comment a-t-elle été obtenue ?
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration

  17. #17
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Citation Envoyé par Pomalaix Voir le message
    Moi, la question que je me poserais, c'est : semble-t-il cohérent d'avoir 8 millions de lectures logiques, soit 64 Go, par rapport au volume des lignes de données à supprimer ?
    Pour moi c'est douteux, car les lignes dans cette table sont très peu volumineuses, il y a un seul index, pas de déclencheur...

    Etes-vous sûr de cette valeur de 8 millions de lectures logiques ? Comment a-t-elle été obtenue ?
    Salut Pomalaix,

    J'utilise le Cloud Control pour obtenir ces valeurs.

    Je sélectionne l'opération de DELETE, j'arrive dans l'écran "SQL Détails", je choisis l'onglet Statistics et là, dans une liste déroulante, je sélectionne :
    1) "Executions by hour" : 1
    2) "Disk reads per execution" : 30 000
    3) "Buffer gets by execution" : 8 000 000

    Mais je suis d'accord avec toi, les snapshots AWR n'occupent que 1.13Go, on est donc bien loin des 64Go
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

Discussions similaires

  1. [12c] Un snapshot AWR est un .zip de snapshots ASH?
    Par Ikebukuro dans le forum Administration
    Réponses: 3
    Dernier message: 11/06/2018, 14h55
  2. [11gR2] Rapport AWR et direct physical writes tablespace TEMP et PK
    Par Ikebukuro dans le forum Administration
    Réponses: 6
    Dernier message: 12/05/2017, 19h16
  3. [11gR2] Autotrace : consistent gets et physical reads pas égaux?
    Par Ikebukuro dans le forum Administration
    Réponses: 0
    Dernier message: 08/05/2016, 12h07
  4. Oracle 10g : anayse de snapshots AWR
    Par fred_04510 dans le forum Administration
    Réponses: 3
    Dernier message: 08/01/2010, 17h50
  5. Question sur les Physical reads
    Par nax71 dans le forum Débuter
    Réponses: 2
    Dernier message: 18/02/2009, 09h38

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