Discussion :

[Ikebukuro]

Hello tout le monde,

Je suis à la recherche des grosses requêtes SQL qui saturent le tablespace UNDO.

Quelqu'un ici m'a parlé de la vue V$UNDOSTAT.
"V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns null values if the system is in manual undo management mode."

J'utilise le champ MAXQUERYID "SQL identifier of the longest running SQL statement in the period" mais je me demande si Oracle ne sélectionne QUE les requêtes mettant à jour le TBS UNDO ou s'il affiche même les requêtes qui n'y touchent pas?

Je me pose cette question car c'est un Select qui ressort à chaque fois...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT v.sql_text, u.BEGIN_TIME, u.END_TIME, u.UNDOBLKS, u.TXNCOUNT, u.MAXQUERYLEN, u.MAXQUERYID, u.NOSPACEERRCNT, u.ACTIVEBLKS, u.UNEXPIREDBLKS, u.EXPIREDBLKS
FROM V$UNDOSTAT u, V$SQL v
WHERE u.MAXQUERYID = v.SQL_ID 
ORDER BY BEGIN_TIME;
 
SQL_TEXT	BEGIN_TIME	END_TIME	UNDOBLKS	TXNCOUNT	MAXQUERYLEN	MAXQUERYID	NOSPACEERRCNT	ACTIVEBLKS	UNEXPIREDBLKS	EXPIREDBLKS
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 09:32:25	03/19/2016 09:42:25	3	43	215	0rc4km05kgzb9	0	224	668 808	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 09:32:25	03/19/2016 09:42:25	3	43	215	0rc4km05kgzb9	0	224	668 808	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 09:42:25	03/19/2016 09:52:25	0	2	816	0rc4km05kgzb9	0	224	668 808	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 09:42:25	03/19/2016 09:52:25	0	2	816	0rc4km05kgzb9	0	224	668 808	107 312
select ts# from ts$ where ts$.online$ != 3 and bitand(flags,2048) != 2048	03/19/2016 09:52:25	03/19/2016 10:02:25	7 348	1 012	1 407	89w8y2pgn25yd	0	224	668 808	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 10:02:25	03/19/2016 10:12:25	4 396	666	815	0rc4km05kgzb9	0	224	679 048	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 10:02:25	03/19/2016 10:12:25	4 396	666	815	0rc4km05kgzb9	0	224	679 048	107 312
select ts# from ts$ where ts$.online$ != 3 and bitand(flags,2048) != 2048	03/19/2016 10:12:25	03/19/2016 10:22:25	1	6	808	89w8y2pgn25yd	0	224	679 048	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 10:22:25	03/19/2016 10:32:25	3	38	812	0rc4km05kgzb9	0	224	679 048	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 10:22:25	03/19/2016 10:32:25	3	38	812	0rc4km05kgzb9	0	224	679 048	107 312
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'	03/19/2016 10:32:25	03/19/2016 10:42:25	0	3	208	0rc4km05kgzb9	0	224	679 048	107 312

Si je me suis trompé sur l'usage de cette table, merci de me dire où

[fouedgr]

​
​Bonjour,
Oui , les Select peuvent générer de l'Undo et parfois trop pour les requêtes qui sélectionnent beaucoup de données et qui ont une longue durée sur une base tres actives (ou avec un Undo tablespace trop petit .. ) d'ou la fameuse 'snapshot too old ' ..
Une requête Select commence a l'instant t0 , et en même temps d'autres transactions ont besoin de modifier ces mêmes données lues par la dite requêtes (qui est toujours en cours) a un instant t1, alors Oracle met ces données dans le Undo afin que la requête Select puisse conserver l'aspect 'consistency' (les donnees ne doivent pas changer en cours de route)

Concernant le select de V$UNDOSTAT : vaut mieux ajouter un order by , probablement sur la colonne UNDOBLKS

aussi , il est possible de faire un filtre sur la période pour mieux cibler, du genre :
-- where begin_time between to_date('21/09/2016 08:20:54' , 'DD/MM/YYYY HH24:MI:SS') and to_date('21/09/2016 10:10:54' , 'DD/MM/YYYY HH24:MI:SS') ;

Pour le reste la requête fournie dans l'autre discussion peut être modifiée , mais est tres utile our voire les sessions en cours qui consomment le plus d'undo (un order by undoblks_MB )
http://www.developpez.net/forums/d15...s/#post8573032

[Pomalaix]

​...Oui , les Select peuvent générer de l'Undo ...

Non, dans toute votre réponse, vous présentez les choses à l'envers !

Les opérations modificatives (INSERT / UPDATE / DELETE) génèrent systématiquement des informations d'annulation, sans se préoccuper de savoir si elles manipulent des données communes avec un SELECT simultané. L'objectif premier étant de pouvoir faire un ROLLBACK si on le souhaite.

Par ailleurs, chez Oracle, tout SELECT a lieu sous le régime de la lecture cohérente, comme vous l'avez signalé.
Cela signifie que quelle que soit sa durée, les données doivent être ramenées telles qu'elles étaient au début de ce SELECT, en ignorant les éventuelles modifications faites en cours de route par les autres sessions.

Schématiquement, le SELECT démarre à T0. Toutes les lignes ramenées doivent présenter les valeurs telles qu'elles étaient à T0.
Quand le SELECT parcourt un bloc, il vérifie dans l'en-tête si ce bloc n'a pas été modifié après T0.
S'il a été modifié, ce bloc n'est pas cohérent (ne contient pas la bonne version des données), et le processus serveur va alors faire appel aux données d'annulation pour générer un bloc à l'état de T0.

Donc le SELECT ne génère pas de données d'annulation, il les exploite.

[fouedgr]

Oui , vous avez tout a fait raison, je m'excuse pour cet amalgame, je pense que le seul cas ou un select génère de l'undo est dans le cas d'un select for update .

[Ikebukuro]

Merci à vous deux pour ces explications.

Je ne comprends pas "Donc le SELECT ne génère pas de données d'annulation, il les exploite."

Ce que j'ai saisi c'est que à t0 le user U1 commence une transaction. U1 fait un SELECT sur la table Tab1, puis un update sur la table Tab2. Le user U2 fais un update sur Tab1 et COMMIT sa transaction. A t1 U1 refait un Select dans Tab1 : il doit avoir les données déjà lues à t0 car il est dans sa même transaction et même si U2 a commité les modifs dans Tab1, U1 doit accéder aux mêmes données qu'à t0. Donc ces données ont été copiées dans le UNDO TBS lors du premier Select de U1 et ce sont ces données là que Oracle va chercher pour le nouveau SElct.

J'ai tout bon?

Bonne soirée.

[fouedgr]

( c’était la mon erreur lors de ma première réponse) "Donc ces données ont été copiées dans le UNDO TBS lors du premier Select de U1 et ce sont ces données là que Oracle va chercher pour le nouveau SElct. " : c'est lors de l'Update fait par U2 que ces données ont été mises dans le Undo.
Je ne suis pas sur que U1 va lire a t1 les mêmes données que dans t0 dans le cas évoqué , vous faites reference 'refait un Select ' après un update, normalement ca doit etre le même select qui garantit la cohérence des données, si c'est un nouveau select fait après que U2 a déjà commitee ses données , alors ce sont les nouvelles donnees qui sont retournées. Notre ami 'Pomalaix' peut donner son avis sur ce cas précis.

[Ikebukuro]

Je ne suis pas d'accord, Oracle doit me garantir que les données lues en base pendant toute ma transaction sont identiques sinon cela pourrait mettre un bordel pas possible dans mes applications.
Entre le début et la fin de ma transaction je n'ai pas à voir les données commitées des autres transactions dans le cas précis où j'ai déjà récupérées celles-ci avant le commit. En revanche si je fais un select sur la table Tab3 après des modifications validées par un user après le début de ma transaction, je vais bien récupérer les nouvelles données car c'est la première fois que j'accède à cette table.

[fouedgr]

​
​Bonsoir,
Oui , les aspects de consistance et d'isolation s'appliquent au niveau d'une transaction donnée. Je ne peux mettre un lien vers la doc Oracle, mais voici un petit mot "All Oracle transactions obey the basic properties of a database transaction, known as ACID properties.".
Le point sur lequel je ne suis pas certain et que j'ai voulu soulever dans mon post précédant etait relatif au scénario précis évoqué : Il est mentionné "refait un Select " , donc c'est un nouveau select et non le même select de t0, est ce que le nouveau select va lire ses données a partir de l'Undo ?

[Pomalaix]

Je ne suis pas d'accord, Oracle doit me garantir que les données lues en base pendant toute ma transaction sont identiques sinon cela pourrait mettre un bordel pas possible dans mes applications.
Entre le début et la fin de ma transaction je n'ai pas à voir les données commitées des autres transactions dans le cas précis où j'ai déjà récupérées celles-ci avant le commit. En revanche si je fais un select sur la table Tab3 après des modifications validées par un user après le début de ma transaction, je vais bien récupérer les nouvelles données car c'est la première fois que j'accède à cette table.

La lecture cohérente s'applique par défaut à l'échelle de l'instruction individuelle (le SELECT pour ce qui nous concerne), du fait du mode READ COMMITTED.
Si vous voulez une lecture cohérente au niveau de la transaction complète, il faut le demander explicitement, par exemple par SET TRANSACTION READ ONLY.

La compréhension de cette distinction est fondamentale, sinon bonjour les erreurs de logique dans votre code !

[fouedgr]

Un grand merci Pomalaix , maintenant l'image complète est bien claire. J’apprécie vos éclaircissements.

[Ikebukuro]

Pour moi ça l'est moins

Bon, ben j'ai un week-end de trois jours donc je sais ce que je vais tester comme code SQL à la maison

[Pomalaix]

Peut-être les précisions suivantes sont-elles utiles.

Il faut bien distinguer le niveau instruction et le niveau transaction.
Une transaction est composée de une ou plusieurs instructions.

La notion de cohérence d'une transaction ne doit pas être confondue avec le principe de lecture cohérente lors d'un SELECT.

Les fameuses propriétés ACID s'appliquent à l'échelle de la transaction.
Atomicité : la transaction complète se comporte comme un tout indissociable face au COMMIT / ROLLBACK.
Cohérence : une transaction ne peut être validée (COMMIT) avec succès que si les valeurs résultant de la transaction respectent les contraintes déclaratives (PK, FK, UNIQUE, NOT NULL, CHECK).
Isolation : dans le mode READ COMMITTED, tant que la transaction n'est pas validée, ses effets sont invisibles par les autres sessions

Comme je l'ai mentionné, la lecture cohérente (ou consistante pour parler franglais), quant à elle, s'applique par défaut uniquement à l'échelle de l'instruction.
Je démarre à 10h00 un SELECT qui dure 15 minutes.
Ce SELECT me montrera les données telles qu'elles étaient à 10H00, même si entre 10H00 et 10H15 une autre session a modifié tout ou partie de ces données et a validé ses modifications.
Quand je démarre un SELECT à l'instant T, même s'il dure des heures, implicitement je demande à Oracle "quelles étaient les données à l'instant T ?".
Tout SELECT fonctionne de cette manière, mais chaque SELECT est indépendant.
Ils n'ont pas été lancés au même moment, donc ils ne font pas référence au même point dans le temps.
Si donc je relance le même SELECT à 10H20, cette fois-ci je verrai les modifications faites par la transaction parallèle, sous réserve qu'elle ait été validée.

En revanche, dans une transaction READ ONLY, tous les SELECT présenteront les données telles qu'elles étaient au début de cette transaction.

[fouedgr]

Pour moi ça l'est moins

Bon, ben j'ai un week-end de trois jours donc je sais ce que je vais tester comme code SQL à la maison

Bon courage, mais pour revenir déjà aux résultats de la requête V$UNDOSTAT : Il me semble bien que ce ne sont pas ces requêtes qui consomment le plus , peut etre qu'il faut filtrer comme je l'ai deja dit sur une periode de temps plus representative de ton workload applicatif. Je dis ca car toutes les requets font apperement partie du scheduler Oracle ( 'DBA_QUEUE_SCHEDULES') et en plus les UNDOBLKS sont très minimes. la vue historique pour V$UNDOSTAT est DBA_HIST_UNDOSTAT

[Ikebukuro]

ca y est, j'ai fais les tests avec le niveau d'isolation SERIALIZABLE au niveau de la transaction et c'était ce que je cherchais comme niveau d'isolation.