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.