Discussion :

[Ikebukuro]

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.

[Waldar]

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

[dell68]

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

[Michel.Priori]

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

[pachot]

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

[Ikebukuro]

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

[pachot]

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

[Ikebukuro]

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.

[Waldar]

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.