Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Migrating from 6.5 SQL Server to Oracle 8.1

Re: Migrating from 6.5 SQL Server to Oracle 8.1

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 11 Feb 2003 12:07:15 -0000
Message-ID: <3e48e77c$0$14170$ed9e5944@reading.news.pipex.net>


"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E48B24D.FA60962E_at_exesolutions.com...
> > The issue with Oracle's row level locks is that _if_ you need them,
> > the lock is implemented by writing information to the Oracle data
> > block where the row resides. If you have 100 rows on 100 different
> > data pages and you need to "SELECT FOR UPDATE" those rows, then those
> > "reads" are now also "writes"!!!

<snip>

> Your misunderstanding is, how shall I say this kindly, considerable.
Locking of
> rows is in the header. But what does that have to writing? The informatin
is
> cached in memory.

I don't believe that it is in fact the case consider the following

SQL> select count(*) from large_tab;

  COUNT(*)


    232632

SQL> analyze table large_tab compute statistics;

Table analyzed.

SQL> select blocks from dba_segments where segment_name='LARGE_TAB';

    BLOCKS


      3200

*so it really is a large table, at least for a laptop in a second session issue SELECT * FROM LARGE_TAB FOR UPDATE; and then wait for the select to finish
back to the same session

SQL> SELECT USED_UBLK,USED_UREC
  2 FROM V$TRANSACTION;  USED_UBLK USED_UREC
---------- ----------

      1892 232632

so we see that the session has used 1892 undo blocks, with one record for each row affected. This is a write. You will also get redo records written. If you modify the block (even just to update the ITL) then you will get write activity. In many cases of course the select for update is unnecessary which I think is the point Jonathan was making in the thread Pablo refers to. Perhaps we should campaign to rename pessimistic locking costly locking.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Tue Feb 11 2003 - 06:07:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US