Re: Migrating from 6.5 SQL Server to Oracle 8.1

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3e48e77c$0$14170$ed9e5944_at_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.
>

Hi Niall,

Correct.

Performing a 'select for update' causes changes to the lock bytes (which incidentally are in the row header) and to the block header and this as you've demonstrated generates undo, which all in turn may generate physical writes ....

So yes, a select for update does cause "writes" but I view this as very much consistent with the Oracle architecture and a special example of a select. You 'generally' use a select for update within a logical transaction and the locking involved must therefore generate these associated costs.

You shouldn't perform unnecessary select for updates because of both the locking implications and the background "costs" associated with the statement.

However, Pablo's suggestion that getting inittrans wrong requires the *instance* to be rebuilt has Oracle terminology confused somewhere along the way ????

Cheers

Richard Received on Tue Feb 11 2003 - 07:55:12 CST