Re: ORA_ROWSCN and ROWDEPENDENCIES

From: joel garry <joel-garry_at_home.com>
Date: 18 Jan 2007 14:01:14 -0800
Message-ID: <1169157674.251274.101400@38g2000cwa.googlegroups.com>

DA Morgan wrote:
> Vladimir M. Zakharychev wrote:
> > DA Morgan wrote:
> >> hpuxrac wrote:
> >>> DA Morgan wrote:
> >>>> Yesterday, I can't find it now, someone wrote that ROWDEPENDENCIES
> >>>> has a granularity of 3 seconds.
> >>>>
> >>>> While that may have been true in the past ... it seems it another bit
> >>>> of advice that has outlived its usefulness.
> >>>>
> >>>> In 10gR2 the following produces a unique SCN for each row.
> >>>>
> >>>> CREATE TABLE t (
> >>>> testcol NUMBER(10))
> >>>> ROWDEPENDENCIES;
> >>>>
> >>>> BEGIN
> >>>> FOR i IN 1 .. 1000 LOOP
> >>>> INSERT INTO t VALUES (i);
> >>>> COMMIT;
> >>>> user_lock.sleep(0.1);
> >>>> END LOOP;
> >>>> END;
> >>>> /
> >>> You seem to be missing out on some pretty basic oracle concepts here.
> >>>
> >>> Do different rows in a table that are inserted/changed and committed on
> >>> different commits ever share the same SCN?
> >> I don't know why don't you tell me. ;-)
> >>
> >> SQL> CREATE TABLE t (
> >> 2 testcol VARCHAR2(3));
> >>
> >> Table created.
> >>
> >> SQL> BEGIN
> >> 2 FOR i IN 1..10000 LOOP
> >> 3 INSERT INTO t VALUES ('ABC');
> >> 4 COMMIT;
> >> 5 END LOOP;
> >> 6 END;
> >> 7 /
> >>
> >> PL/SQL procedure successfully completed.
> >>
> >> SQL> SELECT ora_rowscn, COUNT(*)
> >> 2 FROM t
> >> 3 GROUP BY ora_rowscn;
> >>
> >> ORA_ROWSCN COUNT(*)
> >> ---------- ----------
> >> 2387360 660
> >> 2383612 660
> >> 2389638 660
> >> 2392659 660
> >> 2392761 100
> >> 2386611 660
> >> 2388125 660
> >> 2390406 660
> >> 2382841 660
> >> 2384366 660
> >> 2388896 660
> >> 2391174 660
> >> 2385131 660
> >> 2385880 660
> >> 2382075 660
> >> 2391935 660
> >>
> >> 16 rows selected.
> >>
> >> SQL>
> >>
> >> Do you have a different explanation?
> >>
> >> Anyone interested should drop the above table, recreate it with the
> >> ROWDEPENDENCIES keyword and observe the difference.
> >>
> >> There may at one time have been a 3 second granularity, I see no
> >> evidence for it in 10gR2.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan_at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
> >
> > These are block SCNs in this case. I suppose the OP forgot to add "with
> > row dependencies turned on" after "a table" in his question. :)
> >
> > Anyway, as pointed out earlier, 3 second granularity manifests on
> > conversion from SCN to timestamp of that SCN. Definitely holds for
> > 10.2.0.2 (table created with ROWDEPENDENCIES, so your test query above
> > returns 10000 rows):
> >
> > SQL> begin
> > 2 for i in 1..1000 loop
> > 3 insert into t values('ABC');
> > 4 commit;
> > 5 dbms_lock.sleep(0.01);
> > 6 end loop;
> > 7 end;
> > 8 /
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL> select count(ora_rowscn) cnt, scn_to_timestamp(ora_rowscn) ts from
> > t
> > group by scn_to_timestamp(ora_rowscn) order by 2;
> >
> > CNT TS
> > ---------- -----------------------------------
> > 38 18-JAN-07 09.03.43.000000000 AM
> > 192 18-JAN-07 09.03.46.000000000 AM
> > 192 18-JAN-07 09.03.49.000000000 AM
> > 192 18-JAN-07 09.03.52.000000000 AM
> > 192 18-JAN-07 09.03.55.000000000 AM
> > 192 18-JAN-07 09.03.58.000000000 AM
> > 2 18-JAN-07 09.04.01.000000000 AM
> >
> > 7 rows selected.
> >
> > This was done on a perfectly idle system with my session being the only
> > one connected, on 10.2.0.2/Win32.
> >
> > Regards,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com

> Thanks ... that clears it up. It is not the SCNs that have the
> granularity ... but rather their conversion back to a timestamp.
>
> Thanks again.

That's an interesting distinction. Since I have no idea how the internals work and no way to test, I was wondering how things like checkpointing, slow redo and system suspend impact this. (I've been idly wondering about checkpointing and suspend since someone blogged about using it to clone without alter ts backup - does the suspend wait for the checkpoint to complete? What if you are getting checkpoint not complete errors? I can't imagine suspend waiting for checkpoint, so I can imagine SCN's not matching file headers... it's a strange imaginary world, I know.)

-- 
@home.com is bogus.
http://www.newscientist.com/article.ns?id=dn4899

Received on Thu Jan 18 2007 - 16:01:14 CST