一个没有任何事务的DB，如果一直开着，那SCN号会不会增大？

sqysl

谢谢viadeazhu，学习了。

Yong Huang

viadeazhu's research is very interesting.

We (including me) should not quickly jump to the conclusion about any relationship between heartbeat and SCN increase. It's just a hypothesis, until anybody can find an official document to prove it. 9i's 'redo size' statistic# is 115. If you run this query

select sysdate, dbms_flashback.get_system_change_number, value from v$sysstat where statistic# = 115;

multiple times in 9i, you'll see the SCN go up while redo value remains the same for a while. It may indicate the cause of the SCN increase is heartbeat, but we don't know for sure.

Before you run it in 10g, change 115 to 134. Do not use current_scn column of v$database to do this test. Apparently, a simple query (on any column) of v$database in 10g will increase SCN. This doesn't happen in 9i.

viadeazhu said you can't get SQL trace to generate a trace file. Two things you need to watch. One is you need to use oradebug to set event on the SMON session. What method did you use? Secondly, wait longer. You may need to wait as long as 5 minutes (SMON has a few cycles to perform its different routine tasks, one of which is 5 minutes.) The SQL trace I got on SMON in Oracle 10.2.0.1 on Windows has these SQLs (excluding select's, shown below not in order):

delete from smon_scn_time...
insert into smon_scn_time...
update smon_scn_time...
insert into sys.col_usage$ values...
lock table sys.col_usage$ in exclusive mode nowait
insert into sys.mon_mods$ values...
lock table sys.mon_mods$ in exclusive mode nowait
update sys.col_usage$...
update sys.mon_mods$...

Yong Huang

viadeazhu

感谢huangyong的回答。
我是用的oradebug 10046 trace smon的，但是我发现我一直等在udump目录下，后来我发现trace文件产生在了bdump下面。
然而，我等待了许久，还是没从trace文件发现有update col_usage$的语句，于是我刚才logminer了过去几天的日志，才发现了少许update col_usage$的语句。我想我的第二个观点可以扩展为smon会自己跟新一些内部表，这可能跟你开启了什么参数有关，可能跟你版本不同有关，所以产生了redo。比如如果开启了awr，可能会有更多的redo。
对于第一个观点，我想我是无法确定一定是checkpoint heartbeat产生的，但现象是在没有任何redo产生的情况下每三秒增加一个scn。这是否可以理解为scn的一个heartbeat呢：）

Yong Huang

I randomly picked one update sys.col_usage$... SQL and checked the obj#. It's a table related to SQL*Plus. I didn't check others.

We can assume the SCN increase without redo is related to heartbeat. It's good to have a theory, even though it may be falsified later. If we can find an official paper giving us the details of this heartbeat, that'll solve the mystery.

Yong Huang