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:

---

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 :weird::weird:

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.

Il faut regarder du côté des objets connectés à la table : index, déclencheurs, vues matérialisées.

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

Citation:

---

Envoyé par dell68

utilise. le. conseiller. dbms_sqltune. pour. analyser. le. delete

---

Attention aux licences !!!

Citation:

---

"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

Citation:

---

Envoyé par Ikebukuro

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

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...

Citation:

---

Envoyé par Ikebukuro

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à.

Bon, ça se complique...

Les snapshots AWR occupent 1.13 Go sur disque.

Code:

---

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:

---

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:

---

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.

Citation:

---

Envoyé par Ikebukuro

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.

envoi. le. plan. d’execution. correspondant. en. ajoutant. le. hint. /*+. gather_plan_statistics. */

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 :ptdr:

si. tu. as. un. serveur. de. test. tu. peux. le. faire. ??

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 :aie:

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. ????

Ah mais il n'y a aucune opération à optimiser, l'ordre SQL est hyper basique :

Code:

---

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.

Citation:

---

Envoyé par Ikebukuro

... 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 ?

Citation:

---

Envoyé par Pomalaix

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 8O 8O