Re: ORA_ROWSCN and ROWDEPENDENCIES

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 18 Jan 2007 09:08:55 -0800
Message-ID: <1169140132.842325@bubbleator.drizzle.com>

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.

-- 
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

Received on Thu Jan 18 2007 - 11:08:55 CST