Re: ORA_ROWSCN and ROWDEPENDENCIES

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 17 Jan 2007 22:20:45 -0800
Message-ID: <1169101245.583897.126950@38g2000cwa.googlegroups.com>

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 Received on Thu Jan 18 2007 - 00:20:45 CST