Re: The 5-minute interval in sys.smon_scn_time table

From: Anurag Varma <avoracle_at_gmail.com>
Date: 1 Dec 2006 12:17:14 -0800
Message-ID: <1165004234.088500.36080@80g2000cwy.googlegroups.com>

EscVector wrote:
> On Dec 1, 11:52 am, DA Morgan <damor..._at_psoug.org> wrote:
> > Challenge wrote:
> > > Oracle version is 9.2.0.7 on AIX 5.2
> >
> > > Any idea why the interval is 10 mins instead of 5 mins? Thanks.
> >
> > > DA Morgan wrote:
> > >> Challenge wrote:
> > >>> Hi,
> >
> > >>> According to the document, the smon_scn_time table will be updated
> > >>> automatically by SMON every 5 minutes. Why the interval of the
> > >>> smon_scn_time table is 10 minutes in my database?
> >
> > >>> THREAD TIME_MP TIME_DP SCN_WRP SCN_BAS
> > >>> ---------------- -----------------
> > >>> ------------------------------------- -------------------
> > >>> ----------------
> > >>> ......
> > >>> 1 1164280062 2006-11-23 06:07:43 0
> > >>> 404008
> > >>> 1 1164280662 2006-11-23 06:17:43 0
> > >>> 404209
> > >>> 1 1164281262 2006-11-23 06:27:43 0
> > >>> 404410
> > >>> 1 1164281862 2006-11-23 06:37:43 0
> > >>> 404611
> > >>> 1 1164282462 2006-11-23 06:47:43 0
> > >>> 404812
> > >>> 1 1164283062 2006-11-23 06:57:43 0
> > >>> 405013
> > >>> 1 1164283662 2006-11-23 07:07:43 0
> > >>> 405214
> > >>> 1 1164284262 2006-11-23 07:17:43 0
> > >>> 405415
> > >>> 1 1164284862 2006-11-23 07:27:43 0
> > >>> 405616
> > >>> ......
> >
> > >>> Thanks.
> >
> > >> And your version is?
> > >> --
> > >> Daniel A. Morgan
> > >> University of Washington
> > >> damor..._at_x.washington.edu
> > >> (replace x with u to respond)
> > >> Puget Sound Oracle Users Group
> > >>www.psoug.orgHopefully someone knows but I will check and see if I can find
> > out. My first thought: Documentation error. But it may be something
> > else.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org

>

> Quick sample 10.2.0.2 on AIX 5.3
> Total default config for used for testing.
> Looks like 5 min on this system...
> 12/01/2006 13:58:21
> 12/01/2006 14:03:52
> 12/01/2006 14:05:25
> 12/01/2006 14:10:37
> 12/01/2006 14:15:51
> 12/01/2006 14:17:27
> 12/01/2006 14:22:28
> 12/01/2006 14:27:53
> 12/01/2006 14:33:05

In 10.2 smon_scn_time table has changed quite drastically. Oracle seems to be trying to do a better job at mapping scn with time in 10.2.

Re: Op's question, in our databases I do not see the 5 min interval followed in any of the databases that rigorously (checked in 9.2.0.8 and 10.2).
I guess smon tries to update the table approx every 5 mins .. but might skip updates.
So the correct wording should probably be that in best case scenario, the table will be updated every 5 mins.

Approx 5 days data seems to be kept in this table. In 9i this table had 1440 rows however in 10.2 the 1440 number does not seem to be followed. Instead oracle seems to be following a max mapping number of 144000 (select sum(num_mappings) + count(*) from smon_scn_time;)

Anurag Received on Fri Dec 01 2006 - 14:17:14 CST