ORACLE smon_scn_time研究

首先看一下这张表格smon_scn_time在9i和10g的结构变化
9i：

引用

SQL> desc smon_scn_time
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
THREAD                                             NUMBER
TIME_MP                                            NUMBER
TIME_DP                                            DATE
SCN_WRP                                            NUMBER
SCN_BAS                                            NUMBER

10g：

引用

SQL> desc smon_scn_time
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
THREAD                                             NUMBER
TIME_MP                                            NUMBER
TIME_DP                                            DATE
SCN_WRP                                            NUMBER
SCN_BAS                                            NUMBER
NUM_MAPPINGS                                       NUMBER
TIM_SCN_MAP                                        RAW(1200)
SCN                                                NUMBER
ORIG_THREAD                                        NUMBER

一般情况下，Oracle 9i和Oracle 10g对此表更新频率维持在5分钟左右，但Oracle 10g增加了TIM_SCN_MAP字段，通过这个字段可以scn和time的转换精确到6秒钟，而9i只能精确到5分钟左右。下面分别以9i做测试：
1、

引用

13:38:19 SQL> SELECT LOCALTIMESTAMP FROM   dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
09-JUL-09 01.38.20.890171 PM

Elapsed: 00:00:00.00
13:38:20 SQL> select dbms_flashback.get_system_change_Number  scn from dual;

                SCN
-------------------
      9744666467101

Elapsed: 00:00:00.00
13:38:20 SQL> insert into t select * from t where rownum<10;

9 rows created.

Elapsed: 00:00:00.00
13:38:20 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
13:38:20 SQL> SELECT count(*) FROM   t AS OF TIMESTAMP TO_TIMESTAMP('09-JUL-09 01.38.20.890171 PM');

  COUNT(*)
----------
    259047

Elapsed: 00:00:00.42
13:38:36 SQL> SELECT count(*) FROM   t AS OF scn 9744666467101;

  COUNT(*)
----------
    260181

Elapsed: 00:00:00.35

可以看到通过2种闪回机制，用scn闪回可以看到精确的数据，用TIMESTAMP 只能模糊匹配。
在研究一下TIMESTAMP 匹配了哪个scn

引用

13:43:14 SQL> SELECT TIME_DP, SCN_WRP, SCN_BAS from SMON_SCN_TIME where TIME_DP < = to_date('2009-07-09 13:38:19','yyyy-mm-dd hh24:mi:ss' ) ORDER BY TIME_DP  desc ;

TIME_DP                SCN_WRP    SCN_BAS
------------------- ---------- ----------
2009-07-09 13:33:20       2268 3680638917

scn的算法是
SCN_WRP*4294967296+SCN_BAS=9744664906096
可以看到

引用

13:44:06 SQL> SELECT count(*) FROM   t AS OF scn 9744666466245;

  COUNT(*)
----------
    259047

和timestamp保持一致了。
1、10g增加了函数scn_to_timestamp,可以看到精确度相差6秒

引用

SQL> select scn_to_timestamp(9744666543478) from dual;

SCN_TO_TIMESTAMP(9744666543478)
---------------------------------------------------------------------------
09-JUL-09 03.33.47.000000000 PM

SQL> select scn_to_timestamp(9744666543477) from dual;

SCN_TO_TIMESTAMP(9744666543477)
---------------------------------------------------------------------------
09-JUL-09 03.33.41.000000000 PM

开启10046跟踪事件跟踪一下，发现没有用到smon_scn_time

引用

*** 2009-07-09 15:45:34.627
*** SERVICE NAME:(SYS$USERS) 2009-07-09 15:45:34.627
*** SESSION ID:(132.250) 2009-07-09 15:45:34.627
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1217896029909494
WAIT #3: nam='SQL*Net message from client' ela= 3616167 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1217896033525929
=====================
PARSING IN CURSOR #1 len=48 dep=0 uid=0 oct=3 lid=0 tim=1217896033527758 hv=2148814857 ad='3912114c'
select scn_to_timestamp(9744666543477) from dual
END OF STMT
PARSE #1:c=2000,e=1728,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1217896033527753
BINDS #1:
EXEC #1:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1217896033527918
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1217896033527950
FETCH #1:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1217896033528057
WAIT #1: nam='SQL*Net message from client' ela= 126 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1217896033528238
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1217896033528268
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1217896033528293

delete smon_scn_time表格内容

引用

SQL> alter system set events '12500 trace name context forever, level 10';

System altered.

SQL> delete from smon_scn_time;

1808 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system set events '12500 trace name context off';

System altered.

再次查找

引用

SQL> select scn_to_timestamp(9744666543478) from dual;

SCN_TO_TIMESTAMP(9744666543478)
---------------------------------------------------------------------------
09-JUL-09 03.33.47.000000000 PM

可以看到还是有结果，因为可能将结果保存在pga中，开启另外一个会话就查不到了

引用

SQL> select scn_to_timestamp(9744666543478) from dual;
select scn_to_timestamp(9744666543478) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

从另外一个角度证明了函数scn_to_timestamp是从smon_scn_time取到值的

从客户的一份statspack可以看出Oracle对smon_scn_time表格的操作

引用

Buffer Gets    Executions  Gets per Exec  %Total CPU-Time(s) Elapsd-Time (s) Hash Value
---------------    ------------ ---------- -----  ------  --------- -----------  

delete from smon_scn_time where thread=0 and scn =  (select min(
scn) from smon_scn_time where thread=0)