SCN : pb avec SYSDATE et les conversions via SCN_TO_TIMESTAMP et TIMESTAMP_TO_SCN

Version imprimable

Bonjour,

Je fais des tests sur le SCN et je constate des décalages avec SYSDATE.

J'affiche 60 SCN en me basant sur le champ CURRENT_SCN de V$DATABASE ainsi que SYSDATE.
Je fais aussi une conversion du SCN en TIMESTAMP et de SYSDATE en SCN.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SET SERVEROUTPUT ON
 
DECLARE
V_NUM_CURRENT_SCN  NUMBER;
V_VCHAR_SCN  VARCHAR2(50);
V_VCHAR_SYSDATE  VARCHAR2(50);
V_NUM_SYSDATE_SCN  NUMBER;
 
 
BEGIN
    FOR i in 1..60 LOOP
        DBMS_LOCK.SLEEP(1);
 
        select 
            current_scn, 
            TO_CHAR(SCN_TO_TIMESTAMP(current_scn), 'DD/MM/YYYY HH:MI:SS'), 
            TO_CHAR(sysdate, 'DD/MM/YYYY HH:MI:SS'),
            TO_NUMBER(TIMESTAMP_TO_SCN(sysdate))
 
        INTO V_NUM_CURRENT_SCN, V_VCHAR_SCN, V_VCHAR_SYSDATE, V_NUM_SYSDATE_SCN  
        from v$database, dual;
 
        DBMS_OUTPUT.PUT_LINE('Current SCN : ' || V_NUM_CURRENT_SCN || ' ----- Date current_scn : ' || V_VCHAR_SCN || ' ----- Date Sysdate : ' || V_VCHAR_SYSDATE || ' ----- SCN Sysdate : ' || V_NUM_SYSDATE_SCN);
 
    END LOOP;
 
END;

Voici les résultats.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Current SCN : 1105719307 ----- Date current_scn : 30/06/2016 11:03:12 ----- Date Sysdate : 30/06/2016 11:03:13 ----- SCN Sysdate : 1105719307
Current SCN : 1105719308 ----- Date current_scn : 30/06/2016 11:03:12 ----- Date Sysdate : 30/06/2016 11:03:14 ----- SCN Sysdate : 1105719307
Current SCN : 1105719309 ----- Date current_scn : 30/06/2016 11:03:12 ----- Date Sysdate : 30/06/2016 11:03:15 ----- SCN Sysdate : 1105719307
Current SCN : 1105719311 ----- Date current_scn : 30/06/2016 11:03:15 ----- Date Sysdate : 30/06/2016 11:03:16 ----- SCN Sysdate : 1105719310
Current SCN : 1105719312 ----- Date current_scn : 30/06/2016 11:03:15 ----- Date Sysdate : 30/06/2016 11:03:17 ----- SCN Sysdate : 1105719310
Current SCN : 1105719313 ----- Date current_scn : 30/06/2016 11:03:15 ----- Date Sysdate : 30/06/2016 11:03:18 ----- SCN Sysdate : 1105719310
Current SCN : 1105719315 ----- Date current_scn : 30/06/2016 11:03:18 ----- Date Sysdate : 30/06/2016 11:03:19 ----- SCN Sysdate : 1105719314
Current SCN : 1105719316 ----- Date current_scn : 30/06/2016 11:03:18 ----- Date Sysdate : 30/06/2016 11:03:20 ----- SCN Sysdate : 1105719314
Current SCN : 1105719317 ----- Date current_scn : 30/06/2016 11:03:18 ----- Date Sysdate : 30/06/2016 11:03:21 ----- SCN Sysdate : 1105719314
Current SCN : 1105719319 ----- Date current_scn : 30/06/2016 11:03:21 ----- Date Sysdate : 30/06/2016 11:03:22 ----- SCN Sysdate : 1105719318
Current SCN : 1105719320 ----- Date current_scn : 30/06/2016 11:03:21 ----- Date Sysdate : 30/06/2016 11:03:23 ----- SCN Sysdate : 1105719318
Current SCN : 1105719321 ----- Date current_scn : 30/06/2016 11:03:21 ----- Date Sysdate : 30/06/2016 11:03:24 ----- SCN Sysdate : 1105719318
Current SCN : 1105719323 ----- Date current_scn : 30/06/2016 11:03:24 ----- Date Sysdate : 30/06/2016 11:03:25 ----- SCN Sysdate : 1105719322
Current SCN : 1105719324 ----- Date current_scn : 30/06/2016 11:03:24 ----- Date Sysdate : 30/06/2016 11:03:26 ----- SCN Sysdate : 1105719322
Current SCN : 1105719325 ----- Date current_scn : 30/06/2016 11:03:24 ----- Date Sysdate : 30/06/2016 11:03:27 ----- SCN Sysdate : 1105719322
Current SCN : 1105719327 ----- Date current_scn : 30/06/2016 11:03:27 ----- Date Sysdate : 30/06/2016 11:03:28 ----- SCN Sysdate : 1105719326
Current SCN : 1105719328 ----- Date current_scn : 30/06/2016 11:03:27 ----- Date Sysdate : 30/06/2016 11:03:29 ----- SCN Sysdate : 1105719326
Current SCN : 1105719329 ----- Date current_scn : 30/06/2016 11:03:27 ----- Date Sysdate : 30/06/2016 11:03:30 ----- SCN Sysdate : 1105719326
Current SCN : 1105719331 ----- Date current_scn : 30/06/2016 11:03:30 ----- Date Sysdate : 30/06/2016 11:03:31 ----- SCN Sysdate : 1105719330
Current SCN : 1105719332 ----- Date current_scn : 30/06/2016 11:03:30 ----- Date Sysdate : 30/06/2016 11:03:32 ----- SCN Sysdate : 1105719330
Current SCN : 1105719333 ----- Date current_scn : 30/06/2016 11:03:30 ----- Date Sysdate : 30/06/2016 11:03:33 ----- SCN Sysdate : 1105719330
Current SCN : 1105719335 ----- Date current_scn : 30/06/2016 11:03:33 ----- Date Sysdate : 30/06/2016 11:03:34 ----- SCN Sysdate : 1105719334
Current SCN : 1105719336 ----- Date current_scn : 30/06/2016 11:03:33 ----- Date Sysdate : 30/06/2016 11:03:35 ----- SCN Sysdate : 1105719334
Current SCN : 1105719337 ----- Date current_scn : 30/06/2016 11:03:33 ----- Date Sysdate : 30/06/2016 11:03:36 ----- SCN Sysdate : 1105719334
Current SCN : 1105719339 ----- Date current_scn : 30/06/2016 11:03:36 ----- Date Sysdate : 30/06/2016 11:03:37 ----- SCN Sysdate : 1105719338
Current SCN : 1105719340 ----- Date current_scn : 30/06/2016 11:03:36 ----- Date Sysdate : 30/06/2016 11:03:38 ----- SCN Sysdate : 1105719338
Current SCN : 1105719341 ----- Date current_scn : 30/06/2016 11:03:36 ----- Date Sysdate : 30/06/2016 11:03:39 ----- SCN Sysdate : 1105719338
Current SCN : 1105719343 ----- Date current_scn : 30/06/2016 11:03:39 ----- Date Sysdate : 30/06/2016 11:03:40 ----- SCN Sysdate : 1105719342
Current SCN : 1105719344 ----- Date current_scn : 30/06/2016 11:03:39 ----- Date Sysdate : 30/06/2016 11:03:41 ----- SCN Sysdate : 1105719342
Current SCN : 1105719345 ----- Date current_scn : 30/06/2016 11:03:39 ----- Date Sysdate : 30/06/2016 11:03:42 ----- SCN Sysdate : 1105719342
Current SCN : 1105719347 ----- Date current_scn : 30/06/2016 11:03:42 ----- Date Sysdate : 30/06/2016 11:03:43 ----- SCN Sysdate : 1105719346
Current SCN : 1105719348 ----- Date current_scn : 30/06/2016 11:03:42 ----- Date Sysdate : 30/06/2016 11:03:44 ----- SCN Sysdate : 1105719346
Current SCN : 1105719349 ----- Date current_scn : 30/06/2016 11:03:45 ----- Date Sysdate : 30/06/2016 11:03:45 ----- SCN Sysdate : 1105719349
Current SCN : 1105719351 ----- Date current_scn : 30/06/2016 11:03:45 ----- Date Sysdate : 30/06/2016 11:03:46 ----- SCN Sysdate : 1105719349
Current SCN : 1105719352 ----- Date current_scn : 30/06/2016 11:03:45 ----- Date Sysdate : 30/06/2016 11:03:47 ----- SCN Sysdate : 1105719349
Current SCN : 1105719353 ----- Date current_scn : 30/06/2016 11:03:48 ----- Date Sysdate : 30/06/2016 11:03:48 ----- SCN Sysdate : 1105719353
Current SCN : 1105719355 ----- Date current_scn : 30/06/2016 11:03:48 ----- Date Sysdate : 30/06/2016 11:03:49 ----- SCN Sysdate : 1105719353
Current SCN : 1105719356 ----- Date current_scn : 30/06/2016 11:03:48 ----- Date Sysdate : 30/06/2016 11:03:50 ----- SCN Sysdate : 1105719353
Current SCN : 1105719357 ----- Date current_scn : 30/06/2016 11:03:51 ----- Date Sysdate : 30/06/2016 11:03:51 ----- SCN Sysdate : 1105719357
Current SCN : 1105719359 ----- Date current_scn : 30/06/2016 11:03:51 ----- Date Sysdate : 30/06/2016 11:03:52 ----- SCN Sysdate : 1105719357
Current SCN : 1105719360 ----- Date current_scn : 30/06/2016 11:03:51 ----- Date Sysdate : 30/06/2016 11:03:53 ----- SCN Sysdate : 1105719357
Current SCN : 1105719361 ----- Date current_scn : 30/06/2016 11:03:54 ----- Date Sysdate : 30/06/2016 11:03:54 ----- SCN Sysdate : 1105719361
Current SCN : 1105719363 ----- Date current_scn : 30/06/2016 11:03:54 ----- Date Sysdate : 30/06/2016 11:03:55 ----- SCN Sysdate : 1105719361
Current SCN : 1105719364 ----- Date current_scn : 30/06/2016 11:03:54 ----- Date Sysdate : 30/06/2016 11:03:56 ----- SCN Sysdate : 1105719361
Current SCN : 1105719365 ----- Date current_scn : 30/06/2016 11:03:57 ----- Date Sysdate : 30/06/2016 11:03:57 ----- SCN Sysdate : 1105719365
Current SCN : 1105719367 ----- Date current_scn : 30/06/2016 11:03:57 ----- Date Sysdate : 30/06/2016 11:03:58 ----- SCN Sysdate : 1105719365
Current SCN : 1105719368 ----- Date current_scn : 30/06/2016 11:03:57 ----- Date Sysdate : 30/06/2016 11:03:59 ----- SCN Sysdate : 1105719365
Current SCN : 1105719369 ----- Date current_scn : 30/06/2016 11:04:00 ----- Date Sysdate : 30/06/2016 11:04:00 ----- SCN Sysdate : 1105719369
Current SCN : 1105719371 ----- Date current_scn : 30/06/2016 11:04:00 ----- Date Sysdate : 30/06/2016 11:04:01 ----- SCN Sysdate : 1105719369
Current SCN : 1105719372 ----- Date current_scn : 30/06/2016 11:04:00 ----- Date Sysdate : 30/06/2016 11:04:02 ----- SCN Sysdate : 1105719369
Current SCN : 1105719373 ----- Date current_scn : 30/06/2016 11:04:03 ----- Date Sysdate : 30/06/2016 11:04:03 ----- SCN Sysdate : 1105719373
Current SCN : 1105719375 ----- Date current_scn : 30/06/2016 11:04:03 ----- Date Sysdate : 30/06/2016 11:04:04 ----- SCN Sysdate : 1105719373
Current SCN : 1105719376 ----- Date current_scn : 30/06/2016 11:04:03 ----- Date Sysdate : 30/06/2016 11:04:05 ----- SCN Sysdate : 1105719373
Current SCN : 1105719377 ----- Date current_scn : 30/06/2016 11:04:06 ----- Date Sysdate : 30/06/2016 11:04:06 ----- SCN Sysdate : 1105719377
Current SCN : 1105719379 ----- Date current_scn : 30/06/2016 11:04:06 ----- Date Sysdate : 30/06/2016 11:04:07 ----- SCN Sysdate : 1105719377
Current SCN : 1105719380 ----- Date current_scn : 30/06/2016 11:04:06 ----- Date Sysdate : 30/06/2016 11:04:08 ----- SCN Sysdate : 1105719377
Current SCN : 1105719381 ----- Date current_scn : 30/06/2016 11:04:09 ----- Date Sysdate : 30/06/2016 11:04:09 ----- SCN Sysdate : 1105719381
Current SCN : 1105719383 ----- Date current_scn : 30/06/2016 11:04:09 ----- Date Sysdate : 30/06/2016 11:04:10 ----- SCN Sysdate : 1105719381
Current SCN : 1105719384 ----- Date current_scn : 30/06/2016 11:04:09 ----- Date Sysdate : 30/06/2016 11:04:11 ----- SCN Sysdate : 1105719381
Current SCN : 1105719385 ----- Date current_scn : 30/06/2016 11:04:12 ----- Date Sysdate : 30/06/2016 11:04:12 ----- SCN Sysdate : 1105719385
PL/SQL procedure successfully completed.

J'ai plusieurs questions :
1) le CURRENT_SCN saute 1 numéro tous les 3 numéros... on passe de 1105719309 à 1105719311.
2) la date obtenue depuis le SCN a une précision de 3 secondes (c'est expliqué dans la doc de la fonction SCN_TO_TIMESTAMP) mais c'est quand même bizarre non : 3 SCN sont rattachés à la même date?
3) il y a un saut de 3 secondes sur les dates obtenus depuis CURRENT_SCN : on passe de 30/06/2016 11:03:12 à 30/06/2016 11:03:15
4) le SCN obtenu à partir de SYSDATE fais aussi des siennes : un saut de 3 numéros entre 1105719307 et 1105719310 MAIS ensuite on a un saut de 4 numéros (entre 1105719310 et 1105719314 par exemple).
5) A part pour le premier SCN (1105719307), je n'ai plus aucune synchronisation entre la première colonne Current SCN et la dernière TIMESTAMP_TO_SCN(sysdate) sur plusieurs lignes puis ensuite la synchro reprends :weird:.

Tout ceci me perturbe, quelqu'un pourrait me dire où je me trompe (dans la précision des fonctions de conversion?) car c'est quand même un identifiant extrêmement important dans la vie d'une base Oracle et je voudrais bien comprendre pourquoi il y a des écarts dans les conversions.

30/06/2016, 15h09
Pomalaix

Pour éclairer un peu votre compréhension :

Le temps et le SCN sont des informations de nature différente.
Le temps progresse à un rythme régulier indépendamment de ce que l'on fait ou pas dans la base (chaque seconde fait.. une seconde !)
Le SCN progresse à un rythme irrégulier, dépendant des transactions qui sont conclues.

De manière schématique (à ne pas prendre au pied de la lettre !), si je ne fais rien dans ma base, le SCN ne bouge pas alors que le temps continue à avancer. De même, si ma base est très active, on peut imaginer qu'en une seconde, mon SCN passe de 10 000 à 10 100.
La conversion de SCN en date ou vice versa ne peut donc pas reposer sur une formule de calcul. C'est la table SMON_SCN_TIME qui fournit la correspondance entre ces deux éléments, mais pour des raisons de performances, on aura une granularité de 3 secondes comme vous l'avez noté.
Au lieu que l'équivalence en temps soit enregistrée pour chaque SCN, tous les SCN relevant du même intervalle de 3 secondes sont enregistrés en une entrée unique, donnant l'impression que les transactions correspondantes ont eu lieu au même moment.

Je sens que vous allez bien vous amuser avec SMON_SCN_TIME !

Et un peu de lecture en complément :
http://mbouayoun.developpez.com/scn/
http://blog.developpez.com/pachot/jl_glossary_scn/
30/06/2016, 15h49
Ikebukuro

Hello Pomalaix,

J'avoue être un peu vexé par ta réponse car, sauf erreur de ma part, je pensais avoir une "meilleure" image sur ce forum ou alors je passe encore pour un DBA neuneu avec mes questions :calim2:

Je sais bien qu'un SCN est associé à une transaction et qu'on peut en avoir 1 000 en une seconde (des transactions et donc des SCN générés) ou bien un par jour.

Dans le cas qui m'intéresse, c'est le champ CURRENT_SCN de la table V$DATABASE que je teste et pas CHECKPOINT_CHANGE#. D'après mes tests, à chaque COMMIT ou CHECKPOINT, CHECKPOINT_CHANGE# est incrémenté et il correspond au dernier SCN lié à un changement de la base. En revanche, le CURRENT_SCN est incrémenté toutes les secondes même si aucun COMMIT n'est fait; essaye sur une base où tu es seul et tu le constateras.
Pour moi, CURRENT_SCN est comme SYSDATE, ce champ te donne la valeur du SCN à l'instant t mais elle n'est pas stockée en base si aucun changement n'est intervenu à cet instant t.

Je connaissais les deux liens et la table SMON_SCN_TIME mais je vais les réétudier de plus près.

Citation:

Envoyé par Ikebukuro

J'avoue être un peu vexé par ta réponse...

Il n'y a pas de quoi...
Votre 2e question laisse supposer qu'il y a un basique qui vous échappe (ou que vous ne tirez pas les conséquences de cette granularité de 3 secondes), donc je fais mon possible pour essayer de préciser les choses.

Par ailleurs, gare aux tests trompeurs.
Même si je suis loin de maîtriser toutes les finesses du sujet, je peux affirmer que CURRENT_SCN n'est pas incrémenté toutes les secondes.

Petit test :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 16:29:34 SYS@COURS> set time on 16:29:37 SYS@COURS> select current_scn from v$database; CURRENT_SCN ----------- 4831759 16:29:37 SYS@COURS> exec dbms_lock.sleep(10); ProcÚdure PL/SQL terminÚe avec succÞs. 16:29:47 SYS@COURS> select current_scn from v$database; CURRENT_SCN ----------- 4831763 16:29:47 SYS@COURS>;
En 10 secondes, mon SCN et passé de 59 à 63.

A mon avis, vous vous êtes fait avoir par le fait que la simple consultation de CURRENT_SCN a pour effet de l'augmenter !
(Plus largement, il m'a semblé constater que tout SELECT dans V$DATABASE augmente CURRENT_SCN.)

Ceci étant dit, ça n'explique pas pourquoi il y a des décalages sur le CURRENT_SCN et Sysdate avec les fonctions de conversions; j'avoue que c'est surtout cette partie qui m'a perturbé.

J'ai fais le test suivant et effectivement le CURRENT_SCN n'est pas incrémenté toutes les secondes : 5 SCN en 10 secondes (on estime que les deux Select se sont fait dans la première et dernière seconde?).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select current_scn from v$database; EXECUTE DBMS_LOCK.SLEEP(10); select current_scn from v$database; CURRENT_SCN ----------- 1105803411 1 row selected. PL/SQL procedure successfully completed. CURRENT_SCN ----------- 1105803415 1 row selected.
Et là, on en a plus de 1 par seconde :aie: J'adore les tests dont je ne comprends pas le résultat, j'ai l'impression que je vais tomber sur un trésor enfoui!
En 60 secondes, on a eu 65 SCN...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 select current_scn from v$database; EXECUTE DBMS_LOCK.SLEEP(60); select current_scn from v$database; CURRENT_SCN ----------- 1105804019 1 row selected. PL/SQL procedure successfully completed. CURRENT_SCN ----------- 1105804384 1 row selected.

05/07/2016, 17h10
skuatamad

Comme déjà mentionné / constaté, le SCN n'a rien à voir avec les secondes.
Par contre il ne faut pas oublier dans ses tests les process orale qui eux commit quasi continuellement :
https://asktom.oracle.com/pls/asktom...48052838748707
05/07/2016, 18h04
Ikebukuro

Hum..., tu es en train de dire que le champs CURRENT_SCN de v$database est VRAIMENT mis à jour suite à des COMMIT de process Oracle et non pas de façon régulière liée au temps qui passe comme je pensais! OK, c'est compris, il s'incrémente donc même si ni moi ni un autre utilisateur ne fait de modif en base car c'est Oracle lui même qui travaille.

En revanche j'ai constaté que le CURRENT_SCN et le CHECKPOINT_CHANGE# de V$DATABASE ne sont pas synchrones. Et d'après mes tests c'est surtout le CHECKPOINT_CHANGE# qui m'intéresse car quand je fais un COMMIT suite à des INSERT, il est mis à jour mais, sauf erreur de ma part, quand c'est Oracle qui met à jour le CURRENT_SCN, le CHECKPOINT_CHANGE# ne change pas... Il faudra que je refasse des tests pour valider ce dernier point.

Doc Oracle
CHECKPOINT_CHANGE# Last SCN checkpointed
CURRENT_SCN Current SCN
05/07/2016, 18h26
Pomalaix

Citation:

Envoyé par Ikebukuro

En revanche j'ai constaté que le CURRENT_SCN et le CHECKPOINT_CHANGE# de V$DATABASE ne sont pas synchrones....

Ben non, pourquoi voudriez-vous qu'ils soient synchrones ? CURRENT_SCN désigne en quelque sorte la dernière transaction ayant eu lieu dans la base, alors que la notion de CHECKPOINT est relative aux écritures dans les fichiers DBF.

Ces deux notions sont indépendantes (tout comme SCN et le temps !)
On peut effectuer de multiples transactions avant qu'un CHECKPOINT ait lieu. A l'inverse, pour une transaction très volumineuse et longue, des CHECKPOINTS intermédiaires peuvent avoir lieu...
05/07/2016, 19h53
Ikebukuro

Ca commence à bien m'énerver cette histoire...
Enfin bon, c'est dans la douleur qu'on apprends :aie: