完全無事務的資料庫SCN增長之謎

    在深入區有一個帖子討論一個沒有任何事務的Oracle資料庫如果一直開啟，那麼SCN會不會增大？

http://www.itpub.net/thread-1221754-1-1.html

    現象是顯而易見的，SCN會隨時間而增加。

然後有人說：SCN就像Oracle的一個內部時鐘一樣，會隨時間的增加而相應的增加。

再然後又有人會問：SCN的增加到底是不是因為Oracle後臺程式做了一些內部的change？

 

    對於Oracle內部時鐘的比喻，我覺得是非常感性的認識，很形象地描述了一種實際存在併發生的現象。但是我實在是非常好奇，Oracle自己到底是如何維護這個內部時鐘的呢？

    對於SCN是否是Oracle後臺程式的內部change導致的，我起初是非常贊同的，因為我覺得這種解釋是非常理性也看似合理的。

 

    最後所有的問題還是透過自己的實驗得到了驗證，原來這裡面其實包含了兩種Oracle的行為，所以非常容易混淆大家的思路。

  第一種行為是Oracle的內部每三秒的heartbeat checkpoint，它不產生任何redo，但是會增加SCN+1。

  第二種行為是Oracle的內部維護一張SCN和時間的mapping關係的表--SMON_SCN_TIME，在9i中這個表大約每5分鐘被更新一次。它會產生redo，當然也會增大SCN。這種行為是由SMON後臺程式產生的。

    由於這兩種行為的共同作用，於是產生了我們可以看見的現象：SCN在完全沒事務發生的資料庫中增加了，redo也增加了。

 

    結論闡明瞭，那麼我是如何得出這兩個結論的呢？

    透過如下實驗（也在我的回帖中）。

1.每三秒的heartbeat checkpoint產生的SCN+1

SQL> select sysdate,dbms_flashback.get_system_change_number  scn from dual;

SYSDATE                            SCN
----------------- --------------------
20091010 22:50:19          11723811783

SQL> /

SYSDATE                            SCN
----------------- --------------------
20091010 22:50:20          11723811784

SQL> /

SYSDATE                            SCN
----------------- --------------------
20091010 22:50:22          11723811785

    首先從一次的結果來看，確實是一個scn差了3秒。
    讓我們等待更久一點
SQL> /

SYSDATE                            SCN
----------------- --------------------
20091010 22:51:00          11723811797

SQL> /

SYSDATE                            SCN
----------------- --------------------
20091010 22:51:29          11723811806

    兩者時間相差29s，SCN相差trunc(29/3)=9

SQL> select sysdate,dbms_flashback.get_system_change_number  scn from dual;

SYSDATE                            SCN
----------------- --------------------
20091010 23:02:05          11723812015

SQL> /

SYSDATE                            SCN
----------------- --------------------
20091010 23:03:55          11723812051
    兩者時間相差110s,SCN相差trunc(110/3)=36

    於是查詢v$sysstat的redo size，會發現一點都沒有變。
SQL> select sysdate,s.value
  2  from v$sysstat s,v$statname n
  3  where s.STATISTIC#=n.STATISTIC#
and n.name='redo size';  4 

SYSDATE                          VALUE
----------------- --------------------
20091010 23:06:26                66608

SQL> /

SYSDATE                          VALUE
----------------- --------------------
20091010 23:08:00                66608

2.但是從更長的時間上來看，除了這每隔三秒增加的1個scn，還有其他scn增加，也有redo的少量產生，所以我認為我們應該區分開來前者和後者。
於是現在我需要尋找到底是誰產生了除了每隔3s產生的這個scn以外的scn和redo。
結論2：在空閒狀態下的DB會產生一定的redo，而這些redo是由SMON後臺程式產生的。

create table stat1 as select * from v$sesstat;

SQL>select sysdate,s.value
from v$sysstat s,v$statname n
where s.STATISTIC#=n.STATISTIC#
and n.name='redo size';

SYSDATE                          VALUE
----------------- --------------------
20091010 23:17:55               221544

SQL> select sysdate,s.value
from v$sysstat s,v$statname n
where s.STATISTIC#=n.STATISTIC#
and n.name='redo size';

SYSDATE                          VALUE
----------------- --------------------
20091010 23:26:37               227980

create table stat2 as select * from v$sesstat;

我們可以看到兩個時間點相差了227980-221544=6436的redo。
SQL> select stat1.sid,n.name,stat1.value,stat2.value,
  2  stat1.value-stat2.value diff
from stat1,stat2,v$statname n
where stat1.sid=stat2.sid
and stat1.STATISTIC#=stat2.STATISTIC#
and stat1.STATISTIC#=n.STATISTIC#
and stat1.value<>stat2.value
and n.name='redo size'
order by stat1.sid,diff
;  3    4    5    6    7    8    9   10 

       SID NAME                                                                  VALUE      VALUE       DIFF
---------- ---------------------------------------------------------------- ---------- ---------- ----------
         8 redo size                                                             21776      28212      -6436
        13 redo size                                                            128200     164712     -36512

sid 13是我建stat1、2表的session，而sid 8 恰好就是產生著多餘的6436 redo的 session。
我們可以看出redo的差量完全吻合。
ora sid2pid 8
Connected.

SPID         sid/serial PROGRAM                                          SQL_HASH_VALUE PREV_HASH_VALUE
------------ ---------- ------------------------------------------------ -------------- ---------------
23823        8,1        878649' where "THREAD" = '1' and "TIME_MP" = '1230774206' and "TIME_DP" = TO_DATE('31-DEC-08', 'DD-MON-RR') and "SCN_WRP" = '2' and "SCN_BAS
" = '3133713182' and ROWID = 'AAAAICAABAAADqRACn';
         8

1.1724E+10 20091010 23:57:43
commit;
         8

3 rows selected.

原來，我們都錯怪SMON了，他產生了這些redo，原來是為了更新我們的SMON_SCN_TIME這個表。
這個表是作為time和scn的一個mapping關係，是需要時時更新的。
終於，知道原因了，可以睡個好覺了~